Oracle create/rebuild index開並行時要記得noparallel哦~
連結:http://blog.itpub.net/28602568/viewspace-2076195/
註釋:
今天出現一個SQL中不包含parallel關鍵字而執行計劃中去出現並行;
1、檢查表的degree並行度是否等於1
SQL> SELECT table_name,degree FROM DBA_TABLES WHERE TABLE_NAME ='T1';
TABLE_NAME DEGREE
------------------------------ ------------
T1 1
SQL>
2、再檢查該表T1的索引的degree是否等於1
SQL> SELECT table_name,index_name,degree FROM user_indexes where table_name='T1' and index_name='M1' ;
TABLE_NAME INDEX_NAME DEGREE
------------------------------ --------------- ---------
T1 M1 4
SQL>
***出現索引M1 degree=4 是因為在建立/重建索引時指定並行=4,CREATE INDEX M1 ON BILL_OM_DIVIDE_DTL ITEM_NO online parallel 4;而導致sql中只要用到M1的索引就開4個並行去查詢...
3、取消並行 noparallel: ---解決方案
SQL> alter index M1 noparallel;
Index altered.
SQL> SELECT table_name,index_name,degree FROM user_indexes where table_name='T1' and index_name='M1';
TABLE_NAME INDEX_NAME DEGREE
------------------------------ -------------- ----------
T1 M1 1
SQL>
提醒:
create/rebuild index並行處理完後 要記得alter index M1 noparallel 避免影響到其他業務;
【源於本人筆記】 若有書寫錯誤,表達錯誤,請指正...
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/28602568/viewspace-2076195/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- create index , rebuild index troubleshooting 索引故障解決IndexRebuild索引
- create index online 與rebuild index onlineIndexRebuild
- create index online 與rebuild index onlineIndexRebuild
- 加快create / rebuild index的3個點(zt)RebuildIndex
- Default behavior of create & rebuild index in 10G (zt)RebuildIndex
- 加快建立索引(create / rebuild index) 的幾點索引RebuildIndex
- ORACLE中index的rebuildOracleIndexRebuild
- Oracle alter index rebuild 說明OracleIndexRebuild
- rebuild indexRebuildIndex
- index rebuildIndexRebuild
- 索引rebuild和rebuild online時要慎重索引Rebuild
- rebuild index 排序RebuildIndex排序
- sybase rebuild indexRebuildIndex
- 【江楓 】Oracle 9i和10g在create index和rebuild index的統計資訊的區別OracleIndexRebuild
- 索引rebuild和rebuild online時要慎重(轉載)索引Rebuild
- Oracle效能優化之“少做事”(rebuild index)Oracle優化RebuildIndex
- Oracle什麼情況下需要rebuild indexOracleRebuildIndex
- Index rebuild --case 1IndexRebuild
- alter index rebuild 與 rebuild onlineIndexRebuild
- alter index rebuild與index_statsIndexRebuild
- rebuild index online和create index online及沒有online的區別RebuildIndex
- create index/create index online區別Index
- 測試index online rebuild故障記錄IndexRebuild
- Index Online RebuildIndexRebuild
- 【羅玄】從鎖的角度看rebuild index online和rebuild indexRebuildIndex
- 聊聊索引Index Rebuild和Rebuild Online(下)索引IndexRebuild
- 聊聊索引Index Rebuild和Rebuild Online(上)索引IndexRebuild
- CREATE BITMAP INDEXIndex
- alter index ind1 rebuild 和alter index ind1 rebuild onlineIndexRebuild
- alter index rebuild和rebuild online的區別IndexRebuild
- rebuild index 和 recreate index (重新建立index) 的區別RebuildIndex
- create index online 和create index 不同及注意點Index
- create和recreate index時縮短時間的選項Index
- 關於move table和rebuild index批量操作的記錄RebuildIndex
- 為什麼要選擇Python進行Web開發?記得收藏!PythonWeb
- Oracle並行操作——淺議使用並行的時機Oracle並行
- hive orc表'orc.create.index'='true'與'orc.create.index'='false'HiveIndexFalse
- create index .. onlineIndex