在索引列上指定hint parallel是否生效的问题测试
在sql中当表的某列存在索引时，及时在hint中指定parallel参数，其执行计划都不会采用parallel的方式，仍采用索引访问数据的方式，推测是由于访问索引的过程只能由单进程串行的方式来做。实验过程如下：
[oracle@yhdb Script]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Tue Jul 17 13:27:36 2018 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> @GatherTableStat.sql TO_CHAR(SYSDATE,' ----------------- 20180717 13:27:50 "begin Gather Table's Statstics,Please waiting......" Enter value for owner: dayu Enter value for table_name: dayu20180717 Enter value for parallel_count: 4 PL/SQL procedure successfully completed. TO_CHAR(SYSDATE,' ----------------- 20180717 13:28:07 SQL> @TableAllInfo "-------------------------" "segment_size" "-------------------------" Enter value for segment_name: dayu20180717 old 2: from dba_segments where segment_name =upper( '&segment_name') group by owner, segment_name new 2: from dba_segments where segment_name =upper( 'dayu20180717') group by owner, segment_name OWNER SIZE_M -------- ---------- DAYU 160 "-------------------------" "table_stats" "-------------------------" old 4: from dba_tables where table_name = upper('&table_name') new 4: from dba_tables where table_name = upper('DAYU20180717') OWNER TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN PAR ANALYZED EST_M -------- ------------------------ ---------- ---------- ----------- --- ---------------- ---------- DAYU DAYU20180717 1385392 20325 98 NO 20180717 13:28:0 143.86539 6 "----------------" "indexes of table" "----------------" old 4: from dba_indexes where table_name = upper('&table_name') order by 1,2 new 4: from dba_indexes where table_name = upper('DAYU20180717') order by 1,2 no rows selected "----------------" "index columns" "----------------" old 3: where table_name =upper('&table_name') order by 1,2,4 new 3: where table_name =upper('DAYU20180717') order by 1,2,4 no rows selected "------------------" "Index partition analyzed" "------------------" old 9: where index_name in (select index_name from dba_part_indexes where table_name = upper('&table_name')) new 9: where index_name in (select index_name from dba_part_indexes where table_name = upper('DAYU20180717')) no rows selected "----------------" "index uniqueness" "----------------" old 5: where table_name = upper('&table_name') new 5: where table_name = upper('DAYU20180717') OWNER COLUMN_NAME NUM_DISTINCT HISTOGRAM NUM_DISTINCT NUM_NULLS ANALYZED -------- ------------------------ ------------ --------------- ------------ ---------- ------------------ DAYU CREATED 1570 NONE 1570 0 20180717 13:28 DAYU DATA_OBJECT_ID 8836 NONE 8836 1243376 20180717 13:28 DAYU EDITION_NAME 0 NONE 0 1385392 20180717 13:28 DAYU GENERATED 2 NONE 2 0 20180717 13:28 DAYU LAST_DDL_TIME 1671 NONE 1671 0 20180717 13:28 DAYU NAMESPACE 20 NONE 20 0 20180717 13:28 DAYU OBJECT_ID 87736 NONE 87736 0 20180717 13:28 DAYU OBJECT_NAME 51944 NONE 51944 0 20180717 13:28 DAYU OBJECT_TYPE 44 NONE 44 0 20180717 13:28 DAYU OWNER 28 NONE 28 0 20180717 13:28 DAYU SECONDARY 2 NONE 2 0 20180717 13:28 DAYU STATUS 2 NONE 2 0 20180717 13:28 DAYU SUBOBJECT_NAME 195 NONE 195 1378656 20180717 13:28 DAYU TEMPORARY 2 NONE 2 0 20180717 13:28 DAYU TIMESTAMP 1723 NONE 1723 0 20180717 13:28 15 rows selected. "----------------" old 1: select * from (select owner,subobject_name,object_type,to_char(created,'yyyymmdd hh24:mi') as created from dba_objects where object_name= upper('&table_name') order by 2) where rownum<101 new 1: select * from (select owner,subobject_name,object_type,to_char(created,'yyyymmdd hh24:mi') as created from dba_objects where object_name= upper('DAYU20180717') order by 2) where rownum<101 OWNER SUBOBJECT_NAME OBJECT_TYPE CREATED -------- ------------------------------ ------------------- -------------- DAYU TABLE 20180717 13:26 "-------------------------" "partition_type" "-------------------------" old 1: select owner,table_name,PARTITIONING_TYPE,SUBPARTITIONING_TYPE from dba_part_tables where table_name =upper('&table_name') new 1: select owner,table_name,PARTITIONING_TYPE,SUBPARTITIONING_TYPE from dba_part_tables where table_name =upper('DAYU20180717') no rows selected "-------------------------" "partition_column" "-------------------------" old 1: select 'part' type,a.* from dba_part_key_columns a where name= upper('&table_name') new 1: select 'part' type,a.* from dba_part_key_columns a where name= upper('DAYU20180717') old 3: select 'subpart' type,a.* from dba_subpart_key_columns a where name=upper('&table_name') new 3: select 'subpart' type,a.* from dba_subpart_key_columns a where name=upper('DAYU20180717') no rows selected "-------------------------" "partition_stats" "-------------------------" old 3: from dba_tab_partitions where table_name = upper('&table_name') new 3: from dba_tab_partitions where table_name = upper('DAYU20180717') no rows selected "-------------------------" "sub partition_stats" "-------------------------" old 4: where table_name = upper('&table_name') new 4: where table_name = upper('DAYU20180717') no rows selected "-------------------------" "Table analyze history" "-------------------------" old 8: and a.object_name = upper('&table_name') new 8: and a.object_name = upper('DAYU20180717') OWNER OBJECT_NAME ANALYZED ROWCNT BLKCNT ---------------- ------------------------ ------------------ ----------- --------- DAYU DAYU20180717 "-------------------------" old 5: where table_name =upper('&table_name') new 5: where table_name =upper('DAYU20180717') OWNER TABLE_NAME PARTITION_NAME OBJECT_TYPE LAST_ANALYZED STATT ---------------- ------------------------ ------------------ ------------ -------------------- ----- DAYU DAYU20180717 TABLE 20180717 13:28:06 SQL> SQL> SQL> create index dayu20180717_ind on dayu20180717(object_id); create index dayu20180717_ind on dayu20180717(object_id) * ERROR at line 1: ORA-00942: table or view does not exist SQL> create index dayu20180717_ind on dayu.dayu20180717(object_id); Index created. SQL> explain plan for select count(*) from table(dbms_xplan.display); Explained. SQL> explain plan for select count(*) from dayu20180717; explain plan for select count(*) from dayu20180717 * ERROR at line 1: ORA-00942: table or view does not exist SQL> conn dayu/dayu Connected. SQL> explain plan for select count(*) from dayu20180717; Explained. SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Plan hash value: 3704767521 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 5528 (1)| 00:01:07 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL| DAYU20180717 | 1385K| 5528 (1)| 00:01:07 | --------------------------------------------------------------------------- 9 rows selected. SQL> explain plan for select object_name,object_id from dayu20180717 where object_id=200; Explained. SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Plan hash value: 2529025618 ------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 16 | 480 | 19 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| DAYU20180717 | 16 | 480 | 19 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | DAYU20180717_IND | 16 | | 3 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_ID"=200) 14 rows selected. SQL> explain plan for select /*+ parallel(t 4) */ count(*) from dayu20180717 t; Explained. SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Plan hash value: 1771578260 ------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib | ------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 1533 (1)| 00:00:19 | | | | | 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 | | 1385K| 1533 (1)| 00:00:19 | Q1,00 | PCWC | | | 6 | TABLE ACCESS FULL| DAYU20180717 | 1385K| 1533 (1)| 00:00:19 | Q1,00 | PCWP | | ------------------------------------------------------------------------------------------------------------ 13 rows selected. SQL> explain plan for select /*+ parallel(t 4) */ object_name,object_id from dayu20180717 where object_id=200; Explained. SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Plan hash value: 2529025618 ------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 16 | 480 | 19 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| DAYU20180717 | 16 | 480 | 19 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | DAYU20180717_IND | 16 | | 3 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_ID"=200) 14 rows selected. SQL> SQL> @GatherTableStat.sql TO_CHAR(SYSDATE,' ----------------- 20180717 13:27:50 "begin Gather Table's Statstics,Please waiting......" Enter value for owner: dayu Enter value for table_name: dayu20180717 Enter value for parallel_count: 4 PL/SQL procedure successfully completed. TO_CHAR(SYSDATE,' ----------------- 20180717 13:28:07
