概述
作为DBA,针对数据库索引创建及重建需要了解一下索引创建的原则及标准,判断是否需要停止应用以及检查创建索引的表大小,选择合适的创建方式。
一、索引创建前的检查
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%以上就可以建立索引提高效率
二、数据库索引创建实施过程
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、创建分区索引
4.1、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=> '',)
4.2、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、检查索引状态
1.1、普通索引检查
status 为 valid 表示索引状态正常。
select index_name,table_name,status,tablespace_name from user_indexes;
1.2、分区索引检查
status 为 usable 表示索引状态正常。
select index_name,partition_name,status,tablespace_name from user_ind_partitions;
2、检查应用
检查相应的 SQL 效率是否提高,重点分析执行计划是否改善。
3、检查内存和历史视图
select object_name from v$sql_plan where object_name = 'index_name' ;
select object_name from dba_hist_sql_plan where object_name = 'index_name';
总结
最后,在建索引后一定要注意观察数据库 SQL 执行计划是否 OK,执行效率是否提高,然后监控下应用是否正常。