熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

全局索引的ONLINE重建要注意影響

2013-11-13 22:14:31  來源: Oracle 

  前幾天遇到一個問題重建一個表的索引的時候竟然用了個多小時後來仔細檢查了一下創建慢的索引發現基本都是全局索引而且都是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執行全表掃描如果要對比較大的表進行在線重建索引全局索引的重建代價是比較高的


From:http://tw.wingwit.com/Article/program/Oracle/201311/18578.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.