index fast full scan不能使用並行的實驗
今天同事問我怎麼提高一個查詢的速度,非常著急要查詢結果。查詢語句執行計劃如下:
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 52563 (2)| 00:10:31 |
| 1 | SORT GROUP BY | | 1 | 6 | | |
| 2 | INDEX FAST FULL SCAN| PK_IC40 | 540M| 310M| 52563 (2)| 00:10:31 |
---------------------------------------------------------------------------------
透過HINT並行的做法讓查詢時間僅用了10秒。查詢執行計劃:
SQL> SELECT DEGREE FROM user_indexes WHERE index_Name='PK_IC40';
DEGREE
----------------------------------------
1
已用時間: 00: 00: 00.05
SQL> explain plan for
2 SELECT /*+ parallel_index(ic40 pk_ic40 8)*/COUNT(DISTINCT aac001) FROM ic40;
已解釋。
已用時間: 00: 00: 00.05
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------
--------
Plan hash value: 3375479740
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 52563 (2)| 00:10:31 | | | |
| 1 | SORT GROUP BY | | 1 | 6 | | | | | |
| 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10001 | 1 | 6 | | | Q1,01 | P->S | QC (RAND) |
| 4 | SORT GROUP BY | | 1 | 6 | | | Q1,01 | PCWP | |
| 5 | PX RECEIVE | | 1 | 6 | | | Q1,01 | PCWP | |
| 6 | PX SEND HASH | :TQ10000 | 1 | 6 | | | Q1,00 | P->P | HASH |
| 7 | SORT GROUP BY | | 1 | 6 | | | Q1,00 | PCWP | |
| 8 | PX BLOCK ITERATOR | | 540M| 310M| 52563 (2)| 00:10:31 | Q1,00 | PCWC | |
| 9 | INDEX FAST FULL SCAN| PK_IC40 | 540M| 310M| 52563 (2)| 00:10:31 | Q1,00 | PCWP | |
----------------------------------------------------------------------------------------------------------------------
已選擇16行。
可是突然想起了之前看過一篇文章,說是對於index fast full scan操作,除非在索引上顯式的設定並行度,否則採用HINT新增並行的做法,是不能達到並行效果的。文章連結如下:
我的ORACLE版本,10.2.0.4.
莫非原文的意思是不能夠使用parallel()提示來並行index fast full sacn。而不是說parallel_index()不可以讓index fast full scan並行?
[ 本帖最後由 wei-xh 於 2010-7-10 13:43 編輯 ]
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 52563 (2)| 00:10:31 |
| 1 | SORT GROUP BY | | 1 | 6 | | |
| 2 | INDEX FAST FULL SCAN| PK_IC40 | 540M| 310M| 52563 (2)| 00:10:31 |
---------------------------------------------------------------------------------
透過HINT並行的做法讓查詢時間僅用了10秒。查詢執行計劃:
SQL> SELECT DEGREE FROM user_indexes WHERE index_Name='PK_IC40';
DEGREE
----------------------------------------
1
已用時間: 00: 00: 00.05
SQL> explain plan for
2 SELECT /*+ parallel_index(ic40 pk_ic40 8)*/COUNT(DISTINCT aac001) FROM ic40;
已解釋。
已用時間: 00: 00: 00.05
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------
--------
Plan hash value: 3375479740
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 52563 (2)| 00:10:31 | | | |
| 1 | SORT GROUP BY | | 1 | 6 | | | | | |
| 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10001 | 1 | 6 | | | Q1,01 | P->S | QC (RAND) |
| 4 | SORT GROUP BY | | 1 | 6 | | | Q1,01 | PCWP | |
| 5 | PX RECEIVE | | 1 | 6 | | | Q1,01 | PCWP | |
| 6 | PX SEND HASH | :TQ10000 | 1 | 6 | | | Q1,00 | P->P | HASH |
| 7 | SORT GROUP BY | | 1 | 6 | | | Q1,00 | PCWP | |
| 8 | PX BLOCK ITERATOR | | 540M| 310M| 52563 (2)| 00:10:31 | Q1,00 | PCWC | |
| 9 | INDEX FAST FULL SCAN| PK_IC40 | 540M| 310M| 52563 (2)| 00:10:31 | Q1,00 | PCWP | |
----------------------------------------------------------------------------------------------------------------------
已選擇16行。
可是突然想起了之前看過一篇文章,說是對於index fast full scan操作,除非在索引上顯式的設定並行度,否則採用HINT新增並行的做法,是不能達到並行效果的。文章連結如下:
我的ORACLE版本,10.2.0.4.
莫非原文的意思是不能夠使用parallel()提示來並行index fast full sacn。而不是說parallel_index()不可以讓index fast full scan並行?
[ 本帖最後由 wei-xh 於 2010-7-10 13:43 編輯 ]
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/22034023/viewspace-667704/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Index Full Scan vs Index Fast Full ScanIndexAST
- Index Full Scan 與 Index Fast Full ScanIndexAST
- INDEX UNIQUE SCAN,INDEX FULL SCAN和INDEX FAST FULL SCANIndexAST
- rowid,index,INDEX FULL SCAN,INDEX FAST FULL SCAN|IndexAST
- Index的掃描方式:index full scan/index fast full scanIndexAST
- INDEX FULL SCAN和INDEX FAST FULL SCAN的區別IndexAST
- INDEX FULL SCAN和INDEX FAST FULL SCAN區別IndexAST
- index full scan 和 index FAST full scan 區別IndexAST
- Index Full Scan 與 Index Fast Full Scan (Final)IndexAST
- index full scan 和 index fast full scan (IFS,FFS)的不同IndexAST
- Index Full Scan和Index Fast Full Scan行為差異分析(上)IndexAST
- Index Full Scan和Index Fast Full Scan行為差異分析(下)IndexAST
- index fast full scan 和 nullIndexASTNull
- Fast full index scan 淺析ASTIndex
- oracle實驗記錄(INDEX fast full scan 的成本計算)OracleIndexAST
- index range scan,index fast full scan,index skip scan發生的條件IndexAST
- [總結]關於index range scans & INDEX (FAST FULL SCAN)IndexAST
- SELECT COUNT(*) 索引會走 index fast full scan索引IndexAST
- Index Full Scans和Index Fast Full ScansIndexAST
- Fast Full Index Scans的特點!ASTIndex
- 收集full table / index scan sqlIndexSQL
- FBI? MAX? INDEX FULL SCAN (MIN/MAX)?Index
- index skip scan的一些實驗。Index
- (轉)索引掃描還是全表掃描(Index Scan Or Full Table Scan)索引Index
- 轉)索引掃描還是全表掃描(Index Scan Or Full Table Scan)索引Index
- 【優化】INDEX FULL SCAN (MIN/MAX)訪問路徑優化Index
- 20180316不使用INDEX FULL SCAN (MIN/MAX)Index
- 【TUNE_ORACLE】列出走了INDEX FULL SCAN的SQL參考OracleIndexSQL
- 【最佳化】INDEX FULL SCAN (MIN/MAX)訪問路徑Index
- Oracle學習系列—資料庫最佳化—Full Scans和Fast Full Index ScansOracle資料庫ASTIndex
- mysql loose index scan的實現MySqlIndex
- 高效的SQL(index skip scan使用條件)SQLIndex
- INDEX SKIP SCANIndex
- Clustered Index Scan and Clustered Index SeekIndex
- 理解index skip scanIndex
- Index Unique Scan (213)Index
- PostgreSQL DBA(119) - pgAdmin(LIMIT:Index Scan vs Bitmap Index Scan)SQLMITIndex
- [轉貼]Skip Scan IndexIndex