重建索引的考虑
最近想对系统中的索引进行调整,参阅pub上好多的有关重建索引的文章,总结如下(欢迎大家补上):
重建索引的考虑
一:考虑重建索引的场合
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 rebuil 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 | 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 | TABLE ACCESS FULL | TEST_INDEX2 | 999K| 4882K| 3219 |
(2) rebuild 会阻塞dml操作,rebuil online 不会阻塞dml操作;
(3) rebuild online时系统会产生一个SYS_JOURNAL_xxx的IOT类型的系统临时日志表,所有rebuild online时索引的变化都记录在这个表中,当新的索引创建完成后,把这个表的记录维护到新的索引中去,然后drop掉旧的索引,rebuild online就完成了。
注意点:
1, 执行rebuild操作时,需要检查表空间是否足够;
2, 虽然说rebuild online操作允许dml操作,但是还是建议在业务部繁忙时间段进行;
3, Rebuild操作会产生大量redo log ;
五:重建分区表上的分区索引
1:重建分区索引方法:
Alter index indexname rebuild partition paritionname tablespace tablespacename;
Alter index indexname rebuild subpartition partitioname tablespace tablespacename;
Partition name 可以从user_ind_partitions查找
Tablepace 参数允许alter index操作更改索引的存储空间;
六:其他
1:truncate 分区操作和truncate 普通表的区别
Truncate 分区操作会导致全局索引失效; truncate 普通表对索引没有影响;
Truncate 分区操作不会释放全局索引中的空间,而truncate 普通表会释放
索引所占空间;
2:rename 表名操作对索引没有影响,因为rename操作只是更改了数据字典,表中数据行的rowid并没有发生变化
重建索引的考虑
一:考虑重建索引的场合
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 rebuil 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 | 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 | TABLE ACCESS FULL | TEST_INDEX2 | 999K| 4882K| 3219 |
(2) rebuild 会阻塞dml操作,rebuil online 不会阻塞dml操作;
(3) rebuild online时系统会产生一个SYS_JOURNAL_xxx的IOT类型的系统临时日志表,所有rebuild online时索引的变化都记录在这个表中,当新的索引创建完成后,把这个表的记录维护到新的索引中去,然后drop掉旧的索引,rebuild online就完成了。
注意点:
1, 执行rebuild操作时,需要检查表空间是否足够;
2, 虽然说rebuild online操作允许dml操作,但是还是建议在业务部繁忙时间段进行;
3, Rebuild操作会产生大量redo log ;
五:重建分区表上的分区索引
1:重建分区索引方法:
Alter index indexname rebuild partition paritionname tablespace tablespacename;
Alter index indexname rebuild subpartition partitioname tablespace tablespacename;
Partition name 可以从user_ind_partitions查找
Tablepace 参数允许alter index操作更改索引的存储空间;
六:其他
1:truncate 分区操作和truncate 普通表的区别
Truncate 分区操作会导致全局索引失效; truncate 普通表对索引没有影响;
Truncate 分区操作不会释放全局索引中的空间,而truncate 普通表会释放
索引所占空间;
2:rename 表名操作对索引没有影响,因为rename操作只是更改了数据字典,表中数据行的rowid并没有发生变化