A SELECT statement can be parallelized only if the following
conditions are satisfied:
The query includes a parallel hint specification (PARALLEL or
PARALLEL_INDEX) or the schema objects referred to in the query have a
PARALLEL declaration associated with them.At least one of the tables specified in the query requires one of the
following:
A full table scan
An index range scan spanning multiple partitions
No scalar subqueries are in the SELECT list.
解决方法
(至少对于Oracle 11gR2,也可能是10g.对于某些过时版本的Oracle,该列表可能是准确的.)
我建议尽可能使用官方的Oracle文档,但并行执行章节不是很准确.
即使手册没有错,也常常会产生误导,因为并行执行非常复杂.如果您查看所有文档,您会发现大约有30个不同的变量决定了并行度.如果您看到一个简短的项目清单,您应该非常怀疑.这些清单通常只是在非常具体的背景下要考虑的最相关的项目.
例：
sql> --Create a table without any parallel settings sql> create table parallel_test(a number primary key,b number); Table created. sql> --Create some test data sql> insert into parallel_test 2 select level,level from dual connect by level <= 100000; 100000 rows created. sql> commit; Commit complete. sql> --Force the session to run the query in parallel sql> alter session force parallel query; Session altered. sql> --Generate explain plan sql> explain plan for 2 select a 3,( 4 select a 5 from parallel_test parallel_test2 6 where parallel_test2.a = parallel_test.a 7 ) 8 from parallel_test; Explained. sql> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------ Plan hash value: 3823224058 --------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%cpu)| Time | TQ |IN-OUT| PQ Distrib | --------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 116K| 1477K| 9 (0)| 00:00:01 | | | | |* 1 | INDEX UNIQUE SCAN | SYS_C0028894 | 1 | 13 | 1 (0)| 00:00:01 | | | | | 2 | PX COORDINATOR | | | | | | | | | | 3 | PX SEND QC (RANDOM) | :TQ10000 | 116K| 1477K| 9 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) | | 4 | PX BLOCK ITERATOR | | 116K| 1477K| 9 (0)| 00:00:01 | Q1,00 | PCWC | | | 5 | INDEX FAST FULL SCAN| SYS_C0028894 | 116K| 1477K| 9 (0)| 00:00:01 | Q1,00 | PCWP | | --------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("PARALLEL_TEST2"."A"=:B1) Note ----- - dynamic sampling used for this statement (level=2) 21 rows selected. sql>
没有并行提示,没有并行对象,没有全表扫描,没有跨越多个分区的索引范围扫描,以及标量子查询.
没有遇到任何一个条件,但查询仍然使用并行性. (我还验证了v $px_process以确保查询确实使用并行性,并且它不仅仅是解释计划失败.)
这意味着您的other question的答案是错误的.
我不确定在这种情况下到底发生了什么,但我认为它与FAST DUAL优化有关.在某些情况下,DUAL不用作表,因此无需并行化.这可能是一个“错误”,但如果你使用DUAL,那么你真的不想要并行性. (虽然我假设您使用DUAL进行演示,但您的真实查询更复杂.如果是这样,您可能需要使用更实际的示例更新查询.)
