一:考虑重建索引的场合
1:表上频繁发生update,delete操作
2:表上发生了alter table ..move操作(move操作导致了rowid变化)
二:判断重建索引的标准
索引重建是否有必要,一般看索引是否倾斜的严重,是否浪费了空间;
那应该如何才可以判断索引是否倾斜的严重,是否浪费了空间,如下:
1, 对索引进行结构分析
Analyze index indexname validate structure;
2, 在执行步骤1的session中查询index_stats表,不要到别的session去查询
select height,DEL_LF_ROWS/LF_ROWS from index_stats;
3, 在步骤2查询出来的height>=4或者DEL_LF_ROWS/LF_ROWS>0.2的场合,该索引考虑重建;
Example:
SQL> select count(*) from test_index;
COUNT(*)
----------
2072327
SQL> analyze index pk_t_test validate structure;
Index analyzed
SQL> select height,DEL_LF_ROWS/LF_ROWS from index_stats;
HEIGHT DEL_LF_ROWS/LF_ROWS
---------- -------------------
3 0
SQL> delete from test_index where rownum<250000;
249999 rows deleted
SQL> select height,DEL_LF_ROWS/LF_ROWS from index_stats;
HEIGHT DEL_LF_ROWS/LF_ROWS
---------- -------------------
3 0
SQL> analyze index pk_t_test validate structure;
Index analyzed
SQL> select height,DEL_LF_ROWS/LF_ROWS from index_stats;
HEIGHT DEL_LF_ROWS/LF_ROWS
---------- -------------------
3 0.0777430939338362
三:重建索引的方式
1:drop 原来的索引,然后再创建索引;
2:alter index indexname rebuild (online);
方式一:耗时间,无法在24*7环境中实现
方式二:比较快,可以在24*7环境中实现
建议使用方式二
四:alter index rebuid内部过程和注意点
1:alter index rebuild 和alter index rebuild online的区别
(1) 扫描方式不同
Rebuild以index fast full scan(or table full scan)方式读取原索引中的数据来构建一个新的索引,有排序的操作; rebuild online 执行表扫描获取数据,有排序的操作;
Rebuild 方式 (index fast full scan or table full scan 取决于统计信息的cost)
Eg1:
SQL> explain plan for alter index idx_policy_id2 rebuild;
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------
| 0 | ALTER INDEX STATEMENT | | 999K| 4882K| 3219 |
| 1 | INDEX BUILD NON UNIQUE| IDX_POLICY_ID2 | | | |
| 2 | SORT CREATE INDEX | | 999K| 4882K| |
| 3 | INDEX FAST FULL SCAN | IDX_POLICY_ID2 | 999K| 4882K| |
---------------------------------------------------------------------
Eg2:
SQL> explain plan for alter index idx_policy_id rebuild;
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------
| 0 | ALTER INDEX STATEMENT | | 2072K| 9M| 461 |
| 1 | INDEX BUILD NON UNIQUE| IDX_POLICY_ID | | | |
| 2 | SORT CREATE INDEX | | 2072K| 9M| |
| 3 | TABLE ACCESS FULL | TEST_INDEX | 2072K| 9M| 461 |
Eg3: (注意和Eg1比较)
Rebuil online 方式:
SQL> explain plan for alter index idx_policy_id2 rebuild online;
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------
| Id | Operation