Oracle PARALLEL_INDEX与索引并发（待测试）
使用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、分区索引：
1 SQL> select index_name, index_type, status, partitioned, degree 2 2 from dba_indexes 3 3 where table_name = 'T1' 4 4 and owner = 'SYS'; 5 6 INDEX_NAME INDEX_TYPE STATUS PARTIT DEGREE 7 --------------- --------------- ---------- ------ ------- 8 IND_T1_ID NORMAL N/A YES 1 9 IND_T1_NAME NORMAL VALID NO 1 10 11 SQL> SELECT /*+ PARALLEL_INDEX(T1, ind_t1_id, 3) */ id from T1 where id between 1 and 3000; 12 13 Execution Plan 14 ---------------------------------------------------------- 15 Plan hash value: 2134138182 16 17 ------------------------------------------------------------------------------------------- 18 | Id | Operation | Name | Rows | Pstart| Pstop | TQ |IN-OUT| PQ Di 19 ------------------------------------------------------------------------------------------- 20 | 0 | SELECT STATEMENT | | 2500 | | | | | 21 | 1 | PX COORDINATOR | | | | | | | 22 | 2 | PX SEND QC (RANDOM) | :TQ10000 | 2500 | | | Q1,00 | P->S | QC (R 23 | 3 | PX PARTITION RANGE ALL| | 2500 | 1 | 4 | Q1,00 | PCWC | 24 |* 4 | INDEX RANGE SCAN | IND_T1_ID | 2500 | 1 | 4 | Q1,00 | PCWP | 25 ------------------------------------------------------------------------------------------- 26 27 SQL> SELECT /*+ PARALLEL_INDEX(T1, ind_t1_id, 3) */ id from T1; 28 29 Execution Plan 30 ---------------------------------------------------------- 31 Plan hash value: 2841388588 32 33 -------------------------------------------------------------------------------------------- 34 | Id | Operation | Name | Rows | Pstart| Pstop | TQ |IN-OUT| PQ Dis 35 -------------------------------------------------------------------------------------------- 36 | 0 | SELECT STATEMENT | | 936K| | | | | 37 | 1 | PX COORDINATOR | | | | | | | 38 | 2 | PX SEND QC (RANDOM) | :TQ10000 | 936K| | | Q1,00 | P->S | QC (RA 39 | 3 | PX BLOCK ITERATOR | | 936K| 1 | 4 | Q1,00 | PCWC | 40 | 4 | INDEX FAST FULL SCAN| IND_T1_ID | 936K| 1 | 4 | Q1,00 | PCWP | 41 --------------------------------------------------------------------------------------------
2、非分区索引，需要走index fast full scan才能开并发:
1 SQL> SELECT /*+ INDEX_FFS(T1,IND_T1_NAME) PARALLEL_INDEX(T1, ind_t1_name, 2) */ 2 2 count(name) from T1; 3 4 Execution Plan 5 ---------------------------------------------------------- 6 Plan hash value: 1205896683 7 8 -------------------------------------------------------------------------------------- 9 | Id | Operation | Name | Rows | TQ |IN-OUT| PQ Distrib | 10 -------------------------------------------------------------------------------------- 11 | 0 | SELECT STATEMENT | | 1 | | | | 12 | 1 | SORT AGGREGATE | | 1 | | | | 13 | 2 | PX COORDINATOR | | | | | | 14 | 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | Q1,00 | P->S | QC (RAND) | 15 | 4 | SORT AGGREGATE | | 1 | Q1,00 | PCWP | | 16 | 5 | PX BLOCK ITERATOR | | 936K| Q1,00 | PCWC | | 17 | 6 | INDEX FAST FULL SCAN| IND_T1_NAME | 936K| Q1,00 | PCWP | | 18 --------------------------------------------------------------------------------------
以下T2表为非分区表：
1 SQL> select /*+ PARALLEL_INDEX(T2, ind_t2_id, 2) */count(id) from T2; 2 3 Execution Plan 4 ---------------------------------------------------------- 5 Plan hash value: 155381254 6 7 ------------------------------------------------------------------------------------ 8 | Id | Operation | Name | Rows | TQ |IN-OUT| PQ Distrib | 9 ------------------------------------------------------------------------------------ 10 | 0 | SELECT STATEMENT | | 1 | | | | 11 | 1 | SORT AGGREGATE | | 1 | | | | 12 | 2 | PX COORDINATOR | | | | | | 13 | 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | Q1,00 | P->S | QC (RAND) | 14 | 4 | SORT AGGREGATE | | 1 | Q1,00 | PCWP | | 15 | 5 | PX BLOCK ITERATOR | | 949K| Q1,00 | PCWC | | 16 | 6 | INDEX FAST FULL SCAN| IND_T2_ID | 949K| Q1,00 | PCWP | | 17 ------------------------------------------------------------------------------------
另外还找到一个与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;
