频道栏目
首页 > 数据库 > Oracle > 正文
Oracle数据库:全局索引的ONLINE重建要注意影响
2011-07-01 07:53:01           
收藏   我要投稿

前几天遇到一个问题,重建一个表的索引的时候,竟然用了8个多小时。后来仔细检查了一下创建慢的索引,发现基本都是全局索引而且都是 ONLINE方式重建,每个分区的重建时间基本相同,大约在23分钟左右。其实导致问题的原因很简单:由于采用ONLINE方式,而且全局索引的每一个分区的数据可能来自这个表的任何一个分区,所以ORACLE对于全局索引的任何一个分区的重建都要走全表扫描。
  SQL> SHOW USER
  USER is "TEST"
  SQL> CREATE TABLE T(ID INT,NAME VARCHAR2(30))
  2  PARTITION BY RANGE(ID)
  3  (
  4  PARTITION P1 VALUES LESS THAN(10000),
  5  PARTITION P2 VALUES LESS THAN(20000),
  6  PARTITION P3 VALUES LESS THAN(30000),
  7  PARTITION P4 VALUES LESS THAN(40000),
  8  PARTITION P5 VALUES LESS THAN(50000),
  9  PARTITION PMAX VALUES LESS THAN(MAXVALUE)
  10  )
  11  /
  Table created.
  SQL> CREATE INDEX T_ID_IDX ON T(ID) GLOBAL
  2  PARTITION BY HASH(ID)
  3  PARTITIONS 32
  4  /
  Index created.
  SQL> COL INDEX_NAME  FORMAT A20
  SQL> COL PARTITION_NAME FORMAT A20
  SQL> SELECT INDEX_NAME,PARTITION_NAME FROM USER_IND_PARTITIONS WHERE INDEX_NAME=T_ID_IDX;
  INDEX_NAME           PARTITION_NAME
  -------------------- --------------------
  T_ID_IDX             SYS_P225
  T_ID_IDX             SYS_P226
  T_ID_IDX             SYS_P227
  T_ID_IDX             SYS_P228
  T_ID_IDX             SYS_P229
  T_ID_IDX             SYS_P230
  T_ID_IDX             SYS_P231
  T_ID_IDX             SYS_P232
  T_ID_IDX             SYS_P233
  T_ID_IDX             SYS_P234
  T_ID_IDX             SYS_P235
  T_ID_IDX             SYS_P236
  T_ID_IDX             SYS_P237
  T_ID_IDX             SYS_P238
  T_ID_IDX             SYS_P239
  T_ID_IDX             SYS_P240
  T_ID_IDX             SYS_P241
  T_ID_IDX             SYS_P242
  T_ID_IDX             SYS_P243
  T_ID_IDX             SYS_P244
  T_ID_IDX             SYS_P245
  T_ID_IDX             SYS_P246
  T_ID_IDX             SYS_P247
  T_ID_IDX             SYS_P248
  T_ID_IDX             SYS_P249
  T_ID_IDX             SYS_P250
  T_ID_IDX             SYS_P251
  T_ID_IDX             SYS_P252
  T_ID_IDX             SYS_P253
  T_ID_IDX             SYS_P254
  T_ID_IDX             SYS_P255
  T_ID_IDX             SYS_P256
  32 rows selected.

SQL> INSERT INTO T SELECT OBJECT_ID,OBJECT_NAME FROM ALL_OBJECTS;
  50617 rows created.
  SQL> COMMIT;
  Commit complete.
  SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,T,CASCADE=>TRUE);
  PL/SQL procedure successfully completed.
  SQL> EXPLAIN PLAN FOR ALTER INDEX T_ID_IDX REBUILD PARTITION SYS_P225;
  Explained.
  SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
  PLAN_TABLE_OUTPUT
  Plan hash value: 2508449852
  ------------------------------------------------------------------------------------
  | Id  | Operation               | Name     | Rows  | Bytes | Cost  | Pstart| Pstop |
  ------------------------------------------------------------------------------------
  |   0 | ALTER INDEX STATEMENT   |          | 50617 |   247K|    56 |       |       |
  |   1 |  INDEX BUILD NON UNIQUE | T_ID_IDX |       |       |       |       |       |
  |   2 |   SORT CREATE INDEX     |          | 50617 |   247K|       |       |       |
  |   3 |    PARTITION HASH SINGLE|          |       |       |       |     1 |     1 |
  |   4 |     INDEX FAST FULL SCAN| T_ID_IDX |       |       |       |     1 |     1 |
  ------------------------------------------------------------------------------------
  Note
  -----
  - cpu costing is off (consider enabling it)
  15 rows selected.
  SQL> EXPLAIN PLAN FOR ALTER INDEX T_ID_IDX REBUILD PARTITION SYS_P225 ONLINE;
  Explained.
  SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
  PLAN_TABLE_OUTPUT
  ----------------------------------------------------------------------------------------------------
  Plan hash value: 78911014
  -----------------------------------------------------------------------------------
  | Id  | Operation              | Name     | Rows  | Bytes | Cost  | Pstart| Pstop |
  -----------------------------------------------------------------------------------
  |   0 | ALTER INDEX STATEMENT  |          | 50617 |   247K|    56 |       |       |
  |   1 |  INDEX BUILD NON UNIQUE| T_ID_IDX |       |       |       |       |       |
  |   2 |   SORT CREATE INDEX    |          | 50617 |   247K|       |       |       |
  |   3 |    PARTITION RANGE ALL |          | 50617 |   247K|    56 |     1 |     6 |
  |*  4 |     TABLE ACCESS FULL  | T        | 50617 |   247K|    56 |     1 |     6 |
  -----------------------------------------------------------------------------------
  Predicate Information (identified by operation id):
  ---------------------------------------------------
  4 - filter(TBL$OR$IDX$PART$NUM("TEST"."T",58596,0,1048576,"ID")=1)
  Note
  -----
  - cpu costing is off (consider enabling it)
  20 rows selected.
  SQL> EXPLAIN PLAN FOR ALTER INDEX T_ID_IDX REBUILD PARTITION SYS_P226;
  Explained.
  SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
  PLAN_TABLE_OUTPUT
  ----------------------------------------------------------------------------------------------------
  Plan hash value: 2508449852
  ------------------------------------------------------------------------------------
  | Id  | Operation               | Name     | Rows  | Bytes | Cost  | Pstart| Pstop |
  ------------------------------------------------------------------------------------
  |   0 | ALTER INDEX STATEMENT   |          | 50617 |   247K|    56 |       |       |
  |   1 |  INDEX BUILD NON UNIQUE | T_ID_IDX |       |       |       |       |       |
  |   2 |   SORT CREATE INDEX     |          | 50617 |   247K|       |       |       |
  |   3 |    PARTITION HASH SINGLE|          |       |       |       |     2 |     2 |
  |   4 |     INDEX FAST FULL SCAN| T_ID_IDX |       |       |       |     2 |     2 |
  ------------------------------------------------------------------------------------
  Note
  -----

- cpu costing is off (consider enabling it)
  15 rows selected.
  SQL> EXPLAIN PLAN FOR ALTER INDEX T_ID_IDX REBUILD PARTITION SYS_P226 ONLINE;
  Explained.
  SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
  PLAN_TABLE_OUTPUT
  ----------------------------------------------------------------------------------------------------
  Plan hash value: 78911014
  -----------------------------------------------------------------------------------
  | Id  | Operation              | Name     | Rows  | Bytes | Cost  | Pstart| Pstop |
  -----------------------------------------------------------------------------------
  |   0 | ALTER INDEX STATEMENT  |          | 50617 |   247K|    56 |       |       |
  |   1 |  INDEX BUILD NON UNIQUE| T_ID_IDX |       |       |       |       |       |
  |   2 |   SORT CREATE INDEX    |          | 50617 |   247K|       |       |       |
  |   3 |    PARTITION RANGE ALL |          | 50617 |   247K|    56 |     1 |     6 |
  |*  4 |     TABLE ACCESS FULL  | T        | 50617 |   247K|    56 |     1 |     6 |
  -----------------------------------------------------------------------------------
  Predicate Information (identified by operation id):
  ---------------------------------------------------
  4 - filter(TBL$OR$IDX$PART$NUM("TEST"."T",58596,0,1048576,"ID")=2)
  Note
  -----
  - cpu costing is off (consider enabling it)
  20 rows selected.
  可以看到,如果要ONLINE重建这个索引,将会对表T执行32全表扫描。如果要对比较大的表进行在线重建索引,全局索引的重建代价是比较高的。

点击复制链接 与好友分享!回本站首页
相关TAG标签 全局 索引 数据库
上一篇:如何解决Oracle数据库归档日志占满磁盘空间
下一篇:解析OracleOLAP使用MView刷新Cube
相关文章
图文推荐
文章
推荐
点击排行

关于我们 | 联系我们 | 广告服务 | 投资合作 | 版权申明 | 在线帮助 | 网站地图 | 作品发布 | Vip技术培训 | 举报中心

版权所有: 红黑联盟--致力于做实用的IT技术学习网站