使用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 1IND_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 hintswhich is sent to a remote site (over a database link)does not send those hints with the remote SQL. This canaffect 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;
