parallel_index并发

PARALLEL_INDEX与索引并发

                    作者 ： OoNiceDream【转载时请务必以超链接形式标明文章原始出处和作者信息】
                    链接： http://www.dbaroad.me/archives/2011/12/parallel_index.html

使用PARALLEL_INDEX可以在索引上开并发，先来看看文档中的描述：
The PARALLEL_INDEX hint instructs the optimizer to use the specified number of concurrent servers to parallelize index range scans for partitioned indexes.

这里说的是PARALLEL_INDEX可以用在分区索引上开并发，其实还有一种就情况是，当查询走index fast full scan时，也可以开并发。

1、分区索引：

SQL> select index_name, index_type, status, partitioned, degree 2 from dba_indexes 3 where table_name = 'T1' 4 and owner = 'SYS';   INDEX_NAME INDEX_TYPE STATUS PARTIT DEGREE --------------- --------------- ---------- ------ ------- IND_T1_ID NORMAL N/A YES 1 IND_T1_NAME NORMAL VALID NO 1   SQL> SELECT /*+ PARALLEL_INDEX(T1, ind_t1_id, 3) */ id from T1 where id between 1 and 3000;   Execution Plan ---------------------------------------------------------- Plan hash value: 2134138182   ------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Pstart| Pstop | TQ |IN-OUT| PQ Di ------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2500 | | | | | | 1 | PX COORDINATOR | | | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10000 | 2500 | | | Q1,00 | P->S | QC (R | 3 | PX PARTITION RANGE ALL| | 2500 | 1 | 4 | Q1,00 | PCWC | |* 4 | INDEX RANGE SCAN | IND_T1_ID | 2500 | 1 | 4 | Q1,00 | PCWP | -------------------------------------------------------------------------------------------   SQL> SELECT /*+ PARALLEL_INDEX(T1, ind_t1_id, 3) */ id from T1;   Execution Plan ---------------------------------------------------------- Plan hash value: 2841388588   -------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Pstart| Pstop | TQ |IN-OUT| PQ Dis -------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 936K| | | | | | 1 | PX COORDINATOR | | | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10000 | 936K| | | Q1,00 | P->S | QC (RA | 3 | PX BLOCK ITERATOR | | 936K| 1 | 4 | Q1,00 | PCWC | | 4 | INDEX FAST FULL SCAN| IND_T1_ID | 936K| 1 | 4 | Q1,00 | PCWP | --------------------------------------------------------------------------------------------

2、非分区索引，需要走index fast full scan才能开并发:

SQL> SELECT /*+ INDEX_FFS(T1,IND_T1_NAME) PARALLEL_INDEX(T1, ind_t1_name, 2) */ 2 count(name) from T1;   Execution Plan ---------------------------------------------------------- Plan hash value: 1205896683   -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | TQ |IN-OUT| PQ Distrib | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | | | 1 | SORT AGGREGATE | | 1 | | | | | 2 | PX COORDINATOR | | | | | | | 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | Q1,00 | P->S | QC (RAND) | | 4 | SORT AGGREGATE | | 1 | Q1,00 | PCWP | | | 5 | PX BLOCK ITERATOR | | 936K| Q1,00 | PCWC | | | 6 | INDEX FAST FULL SCAN| IND_T1_NAME | 936K| Q1,00 | PCWP | | --------------------------------------------------------------------------------------   以下T2表为非分区表： SQL> select /*+ PARALLEL_INDEX(T2, ind_t2_id, 2) */count(id) from T2;   Execution Plan ---------------------------------------------------------- Plan hash value: 155381254   ------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | TQ |IN-OUT| PQ Distrib | ------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | | | | 1 | SORT AGGREGATE | | 1 | | | | | 2 | PX COORDINATOR | | | | | | | 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | Q1,00 | P->S | QC (RAND) | | 4 | SORT AGGREGATE | | 1 | Q1,00 | PCWP | | | 5 | PX BLOCK ITERATOR | | 949K| Q1,00 | PCWC | | | 6 | INDEX FAST FULL SCAN| IND_T2_ID | 949K| Q1,00 | PCWP | | ------------------------------------------------------------------------------------

另外还找到一个与PARALLEL_INDEX相关的BUG，使HINT无法在DBLINK中使用，该BUG在10.2.0.5中修复：
Bug 6621937 – [NO]PARALLEL_INDEX hint not sent to remote site for SQL over database link [ID 6621937.8]:

A SQL statement containing [NO_]PARALLEL_INDEX hints which is sent to a remote site (over a database link) does not send those hints with the remote SQL. This can affect query performance.   eg: select /*+ driving_site(a) parallel_index(a,4) */ a.*,b.* from test_aaa@test_link a,test_bbb b where a.col1 = b.col1 and a.col3=5;