超详细的Oracle数据库索引创建及索引重建变更规范

很多时候我们都需要对某些大表去建索引或者重建，如果不正当操作就很有可能会影响到生产环境，针对这个方面我写了一些关于数据库索引创建及索引重建变更的规范，仅供参考。

一、索引创建前检查

1. 检查表段大小：

select segment_name, bytes/1024/1024 MB from user_segments where segment_name='<表名>'; 

2. 检查表列不同值分布情况：

select a.table_name, 
 a.column_name, 
 a.num_distinct, 
 round(a.num_distinct * 100 / b.num_rows) "distinct percent%" 
 from user_tab_columns a, user_tables b 
 where a.table_name = b.table_name 
 and a.table_name = 'ORDER_RELEASE_STATUS'; 

这里可以看到一般不同值分布占全表记录数，如果percent%达到15%以上就可以建立索引提高效率

二、索引创建

因为之前数据库规范没建立，居然有一张表建立了255个字段，且索引建了50多个...

1. 创建单列索引：

create index index_name on table(col1) tablespace tbs_name [nologging] [online] [parallel n]; 
alter index index_name noparallel ; 

2. 创建复合索引：

create index index_name on table(col1，col2,…) tablespace tbs_name [nologging] [online][parallel n]; 
alter index index_name noparallel ; 

3. 创建索引：

create unique index index_name on table(col1,col2,…) tablespace tbs_name [nologging][online][parallel n]; 
alter index index_name noparallel ; 

4. 创建分区索引：

Local 索引：

小表： 
create index index_name on table(col1) local; 
大表： 
1)create index index_name on table(col1) local unusable; 
2)alter index index_name rebuild partition p_name [parallel n]; 
alter index index_name noparallel ; 
3)execute dbms_stats.gather_index_stats(ownname=> '',indname=> '',) 

Global 索引：

create [global] index index_name on table(col); 

5. 删掉创建的索引

drop index index_name; 

三、索引重建

1. 重建普通索引：

alter index index_name rebuild tablespace w_data [online][ parallel n][ nologging];  
alter index index_name noparallel ; 

2. 重建分区索引：

alter index index_name rebuild partition partition_name tablespace tbs_name[online][parallel n][nologging]; 
alter index index_name noparallel ; 

四、数据库索引检查

1. 普通索引检查

select index_name,table_name,status,tablespace_name from user_indexes;  

status 为 valid 表示索引状态正常。

2. 分区索引检查

select index_name,partition_name,status,tablespace_name from user_ind_partitions;  

status 为 usable 表示索引状态正常。

最后提一点，大家在建索引后一定要注意观察数据库 SQL 执行计划是否 OK，执行效率是否提高，然后监控下应用是否正常，不能创建完就拍拍屁股走人，后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注下。