数据库索引创建及索引重建变更​

1、索引创建的检查过程

a)  检查表段大小：

select segment_name, bytes/1024/1024

from user segments

where segment_name='<表名>';

b)  检查表列不同值分布情况：

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 table_name='';

一般不同值分布占全表记录数 15%以上就可以建立索引提高效率

2、数据库索引 创建最佳实施过程

a)  创建单列索引：

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

alter index index_name noparallel ;

b)  创建复合索引：

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

alter index index_name noparallel ;

c)  创建唯一索引：

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

alter index index_name noparallel ;

d)  创建分区索引：

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);

3、数据库索引重建最佳实施过程

a)  重建普通索引：

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

alter index index_name noparallel ;

b)  重建分区索引：

alter  index  index_name  rebuild  partition  partition_name  tablespace  tbs_name

[online][parallel n][nologging];

alter index index_name noparallel ;

4、数据库索引创建及重建后检查

a)  检查索引状态：

普通索引检查

select index_name,table_name,status,tablespace_name from user_indexes;

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

分区索引检查

select index_name,partition_name,status,tablespace_name from user_ind_partitions;

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

b)  检查应用：检查相应的 SQL 效率是否提高。

5、回退过程

删掉创建的索引：drop index index_name;

6、废弃的索引

1）分别检查内存和历史视图

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’;

2)使索引不可见：alter index index_name invisible （11g 以上）

注意事项：使索引 invisible 可能会触发 bug

bug 描述：当索引 invisible 后，hint/*+ index( .. )*/ 不再生效，也不会评估其它任何索

引，硬解析后最佳的访问路径为全表扫描。

影响的版本：Versions >= 11.2 but BELOW 12.2

修复：12.0.1.2

Bug 文档：

Bug 17727676 - optimizer hint ignored when using invisible indexes (文档 ID 17727676.8)

3)如果是 11g 版本，为了避免因 invisible 索引后，在代码、SQL Profile 中的 hint 失效从

而导致全表扫描所引发的性能问题，建议直接删除索引：

drop index index_name ;

或者 先重命名索引名称:

alter index rename old_index_name to old_index_name_bak ;

alter index old_index_name_bak invisible ;

观察没问题后再删除: drop index old_index_name_bak;