问题描述
Oracle是否有逻辑上的理由不对SELECT列表中的标量子查询使用并行执行?为什么不应该使用它们?
该列表错误.
(至少对于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以确保查询确实使用了并行性，而不仅仅是解释计划失败.)
这意味着您的其他问题的答案是错误的. >
我不确定在这种情况下到底发生了什么，但是我认为这与FAST DUAL优化有关.在某些情况下，DUAL不用作表，因此没有什么可以并行化.这可能是一个错误"，但是如果您使用的是DUAL，那么您实际上根本就不希望并行化. (尽管我假设您使用DUAL进行演示，并且实际查询更为复杂.如果是这样，则可能需要使用更实际的示例来更新查询.)
Is there any logical reason for Oracle not to use parallel execution with scalar subqueries in the SELECT list? Why it shouldn't use them?
Every item in that list is wrong.
(At least for Oracle 11gR2, and probably10g as well. The list may be accurate for some obsolete versions of Oracle.)
I recommend using the official Oracle documentation whenever possible, but the parallel execution chapter is not very accurate.
And even when the manual isn't wrong, it is often misleading, because parallel execution is very complicated. If you go through all the documentation you'll find there are about 30 different variables that determine the degree of parallelism. If you ever see a short checklist of items, you should be very skeptical. Those checklists are usually just the most relevant items to consider in a very specific context.
Example:
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>
No parallel hint, no parallel objects, no full table scans, no index range scans spanning multiple partitions, and a scalar subquery.
Not a single condition met, yet the query still uses parallelism. (I also verified v$px_process to make sure that the query really does use parallelism, and it's not just an explain plan failure.)
This means the answer to your other question is wrong.
I'm not sure exactly what's going on in that case, but I think it has to do with the FAST DUAL optimization. In some contexts, DUAL isn't used as a table, so there's nothing to parallelize. This is probably a "bug", but if you're using DUAL then you really don't want parallelism anyway. (Although I assume you used DUAL for demonstration purposes, and your real query is more complicated. If so, you may need to update the query with a more realistic example.)
这篇关于Oracle:是否有逻辑上的理由不对SELECT列表中的子查询使用并行执行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！