|
前几天遇到一个问题重建一个表的索引的时候竟然用了个多小时后来仔细检查了一下创建慢的索引发现基本都是全局索引而且都是ONLINE方式重建每个分区的重建时间基本相同大约在分钟左右其实导致问题的原因很简单由于采用ONLINE方式而且全局索引的每一个分区的数据可能来自这个表的任何一个分区所以ORACLE对于全局索引的任何一个分区的重建都要走全表扫描 SQL> SHOW USER USER is TEST SQL> CREATE TABLE T(ID INTNAME VARCHAR()) PARTITION BY RANGE(ID) ( PARTITION P VALUES LESS THAN() PARTITION P VALUES LESS THAN() PARTITION P VALUES LESS THAN() PARTITION P VALUES LESS THAN() PARTITION P VALUES LESS THAN() PARTITION PMAX VALUES LESS THAN(MAXVALUE) ) / Table created SQL> CREATE INDEX T_ID_IDX ON T(ID) GLOBAL PARTITION BY HASH(ID) PARTITIONS / Index created SQL> COL INDEX_NAME FORMAT A SQL> COL PARTITION_NAME FORMAT A SQL> SELECT INDEX_NAMEPARTITION_NAME FROM USER_IND_PARTITIONS WHERE INDEX_NAME=T_ID_IDX; INDEX_NAME PARTITION_NAME T_ID_IDX SYS_P T_ID_IDX SYS_P T_ID_IDX SYS_P T_ID_IDX SYS_P T_ID_IDX SYS_P T_ID_IDX SYS_P T_ID_IDX SYS_P T_ID_IDX SYS_P T_ID_IDX SYS_P T_ID_IDX SYS_P T_ID_IDX SYS_P T_ID_IDX SYS_P T_ID_IDX SYS_P T_ID_IDX SYS_P T_ID_IDX SYS_P T_ID_IDX SYS_P T_ID_IDX SYS_P T_ID_IDX SYS_P T_ID_IDX SYS_P T_ID_IDX SYS_P T_ID_IDX SYS_P T_ID_IDX SYS_P T_ID_IDX SYS_P T_ID_IDX SYS_P T_ID_IDX SYS_P T_ID_IDX SYS_P T_ID_IDX SYS_P T_ID_IDX SYS_P T_ID_IDX SYS_P T_ID_IDX SYS_P T_ID_IDX SYS_P T_ID_IDX SYS_P rows selected SQL> INSERT INTO T SELECT OBJECT_IDOBJECT_NAME FROM ALL_OBJECTS; rows created SQL> COMMIT; Commit complete SQL> EXEC DBMS_STATSGATHER_TABLE_STATS(USERTCASCADE=>TRUE); PL/SQL procedure successfully completed SQL> EXPLAIN PLAN FOR ALTER INDEX T_ID_IDX REBUILD PARTITION SYS_P; Explained SQL> SELECT * FROM TABLE(DBMS_XPLANDISPLAY); PLAN_TABLE_OUTPUT
Plan hash value:
| Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop |
| | ALTER INDEX STATEMENT | | | K| | | | | | INDEX BUILD NON UNIQUE | T_ID_IDX | | | | | | | | SORT CREATE INDEX | | | K| | | | | | PARTITION HASH SINGLE| | | | | | | | | INDEX FAST FULL SCAN| T_ID_IDX | | | | | |
Note
cpu costing is off (consider enabling it) rows selected SQL> EXPLAIN PLAN FOR ALTER INDEX T_ID_IDX REBUILD PARTITION SYS_P ONLINE; Explained SQL> SELECT * FROM TABLE(DBMS_XPLANDISPLAY); PLAN_TABLE_OUTPUT
Plan hash value:
| Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop |
| | ALTER INDEX STATEMENT | | | K| | | | | | INDEX BUILD NON UNIQUE| T_ID_IDX | | | | | | | | SORT CREATE INDEX | | | K| | | | | | PARTITION RANGE ALL | | | K| | | | |* | TABLE ACCESS FULL | T | | K| | | |
Predicate Information (identified by operation id):
filter(TBL$OR$IDX$PART$NUM(TESTTID)=) Note
cpu costing is off (consider enabling it) rows selected SQL> EXPLAIN PLAN FOR ALTER INDEX T_ID_IDX REBUILD PARTITION SYS_P; Explained SQL> SELECT * FROM TABLE(DBMS_XPLANDISPLAY); PLAN_TABLE_OUTPUT
Plan hash value:
| Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop |
| | ALTER INDEX STATEMENT | | | K| | | | | | INDEX BUILD NON UNIQUE | T_ID_IDX | | | | | | | | SORT CREATE INDEX | | | K| | | | | | PARTITION HASH SINGLE| | | | | | | | | INDEX FAST FULL SCAN| T_ID_IDX | | | | | |
Note
cpu costing is off (consider enabling it) rows selected SQL> EXPLAIN PLAN FOR ALTER INDEX T_ID_IDX REBUILD PARTITION SYS_P ONLINE; Explained SQL> SELECT * FROM TABLE(DBMS_XPLANDISPLAY); PLAN_TABLE_OUTPUT
Plan hash value:
| Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop |
| | ALTER INDEX STATEMENT | | | K| | | | | | INDEX BUILD NON UNIQUE| T_ID_IDX | | | | | | | | SORT CREATE INDEX | | | K| | | | | | PARTITION RANGE ALL | | | K| | | | |* | TABLE ACCESS FULL | T | | K| | | |
Predicate Information (identified by operation id):
filter(TBL$OR$IDX$PART$NUM(TESTTID)=) Note
cpu costing is off (consider enabling it) rows selected 可以看到如果要ONLINE重建这个索引将会对表T执行全表扫描如果要对比较大的表进行在线重建索引全局索引的重建代价是比较高的 |
