UNION or UNION ALL consists of many queries(branches) that in pre 12C releases were executed one by one.
<BRANCH1> UNION <BRANCH2> <BRANCH1> UNION ALL <BRANCH2>
So in pre 12C first is processed BRANCH1 then BRANCH2. Of course each individual query(branch) can be processed in serial or in parallel but only one branch at time.
Oracle 12C allows to run branches(statement) of UNION or UNION ALL concurrently. It means that BRANCH2 can be processed together with BRANCH1 :). It can even return data faster than BRANCH1.
This feature is turned on automatically and entire UNION or UNION ALL is processed in parallel if:
- OPTIMIZER_FEATURE_ENABLED set to 12.1 or higher
- one of a branch is considered being processed in parallel
Let’s prepare some test data
create table test_tbl ( id number ); create table test_tbl1 ( id number ); create table test_tbl2 ( id number ); create table test_tbl3 ( id number ); begin for i in 1..10000 loop insert into test_tbl values(i); insert into test_tbl1 values(i); insert into test_tbl2 values(i); insert into test_tbl3 values(i); end loop; commit; end loop; / begin dbms_stats.gather_table_stats(user, 'TEST_TBL'); dbms_stats.gather_table_stats(user, 'TEST_TBL1'); dbms_stats.gather_table_stats(user, 'TEST_TBL2'); dbms_stats.gather_table_stats(user, 'TEST_TBL3'); end; /
If no parallelism is recognized then explain plan shows that each BRANCH is processed one by one
explain plan for
select * from test_tbl a
union
select * from test_tbl1 b
union
select * from test_tbl2 c
union
select * from test_tbl3 d;
select *
from table(dbms_xplan.display('PLAN_TABLE', null, 'BASIC'));
-----------------------------------------
| Id | Operation | Name |
-----------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT UNIQUE | |
| 2 | UNION-ALL | |
| 3 | TABLE ACCESS FULL| TEST_TBL |
| 4 | TABLE ACCESS FULL| TEST_TBL1 |
| 5 | TABLE ACCESS FULL| TEST_TBL2 |
| 6 | TABLE ACCESS FULL| TEST_TBL3 |
-----------------------------------------
Let’s turn on parallel for one table so Oracle recognize parallel for one branch.
alter table test_tbl parallel 2;
Now explain plan shows that branches of UNION will be processed concurrently. To recognize it you need to search for PX SELECTOR.
explain plan for
create table test_tbl4
as
select * from test_tbl a -- branch 1
union
select * from test_tbl1 b -- branch 2
union
select * from test_tbl2 c -- branch 3
union
select * from test_tbl3 d; -- branch 4
select * from
table(dbms_xplan.display('PLAN_TABLE', null, 'BASIC'));
--------------------------------------------------------
| Id | Operation | Name |
--------------------------------------------------------
| 0 | CREATE TABLE STATEMENT | |
| 1 | LOAD AS SELECT | TEST_TBL4 |
| 2 | PX COORDINATOR | |
| 3 | PX SEND QC (RANDOM) | :TQ10001 |
| 4 | OPTIMIZER STATISTICS GATHERING | |
| 5 | SORT UNIQUE | |
| 6 | PX RECEIVE | |
| 7 | PX SEND HASH | :TQ10000 |
| 8 | UNION-ALL | |
| 9 | PX BLOCK ITERATOR | |
| 10 | TABLE ACCESS FULL | TEST_TBL |
| 11 | PX SELECTOR | |
| 12 | TABLE ACCESS FULL | TEST_TBL1 |
| 13 | PX SELECTOR | |
| 14 | TABLE ACCESS FULL | TEST_TBL2 |
| 15 | PX SELECTOR | |
| 16 | TABLE ACCESS FULL | TEST_TBL3 |
--------------------------------------------------------
Hint NO_PQ_CONCURRENT_UNION
To block concurrency for branches you can use NO_PQ_CONCURRENT_UNION hint. In below example each branch is executed one by one. Of course each individual statement is executed in parallel – PX RECEIVE.
select /*+ NO_PQ_CONCURRENT_UNION(@"SET$1") */ a.* from (
select * from test_tbl a
union
select * from test_tbl1 b
union
select * from test_tbl2 c
union
select * from test_tbl3 d
) a;
select * from
table(dbms_xplan.display('PLAN_TABLE', null, 'BASIC'));
Plan hash value: 4121875802
--------------------------------------------------
| Id | Operation | Name |
--------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | PX COORDINATOR | |
| 2 | PX SEND QC (RANDOM) | :TQ10004 |
| 3 | VIEW | |
| 4 | SORT UNIQUE | |
| 5 | PX RECEIVE | |
| 6 | PX SEND HASH | :TQ10003 |
| 7 | BUFFER SORT | |
| 8 | UNION-ALL | |
| 9 | PX BLOCK ITERATOR | |
| 10 | TABLE ACCESS FULL | TEST_TBL |
| 11 | PX RECEIVE | |
| 12 | PX SEND ROUND-ROBIN| :TQ10000 |
| 13 | PX SELECTOR | |
| 14 | TABLE ACCESS FULL| TEST_TBL1 |
| 15 | PX RECEIVE | |
| 16 | PX SEND ROUND-ROBIN| :TQ10001 |
| 17 | PX SELECTOR | |
| 18 | TABLE ACCESS FULL| TEST_TBL2 |
| 19 | PX RECEIVE | |
| 20 | PX SEND ROUND-ROBIN| :TQ10002 |
| 21 | PX SELECTOR | |
| 22 | TABLE ACCESS FULL| TEST_TBL3 |
Hint PQ_CONCURRENT_UNION
In case you are using 12C but your optimizer is still 11G you can turn on this feature on sql level using new hint PQ_CONCURRENT_UNION.
alter session set optimizer_features_enable='11.2.0.3';
explain plan for
select /*+ PQ_CONCURRENT_UNION(@"SET$1") */ a.* from (
select * from test_tbl a
union
select * from test_tbl1 b
union
select * from test_tbl2 c
union
select * from test_tbl3 d
) a;
select * from table(dbms_xplan.display('PLAN_TABLE', null, 'BASIC'));
-----------------------------------------------
| Id | Operation | Name |
-----------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | PX COORDINATOR | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 |
| 3 | VIEW | |
| 4 | SORT UNIQUE | |
| 5 | PX RECEIVE | |
| 6 | PX SEND HASH | :TQ10000 |
| 7 | UNION-ALL | |
| 8 | PX BLOCK ITERATOR | |
| 9 | TABLE ACCESS FULL| TEST_TBL |
| 10 | PX SELECTOR | |
| 11 | TABLE ACCESS FULL| TEST_TBL1 |
| 12 | PX SELECTOR | |
| 13 | TABLE ACCESS FULL| TEST_TBL2 |
| 14 | PX SELECTOR | |
| 15 | TABLE ACCESS FULL| TEST_TBL3 |
-----------------------------------------------
db link and UNION and UNION ALL
Concurrent processing for UNION and UNION ALL for remote queries works as well.
On separate database version 11G I created the same list of tables then I connected from 12C and selected tables via db link. Explain plan shows that branches will be processed concurrently.
alter session set optimizer_features_enable='12.1.0.1';
create database link ora11g
connect to tomasz identified by ****** using 'ora11g';
explain plan for
select count(*) from test_tbl@ora11g a
union
select count(*) from test_tbl1@ora11g b
union
select count(*) from test_tbl2@ora11g c
union
select count(*) from test_tbl3@ora11g d;
select *
from table(dbms_xplan.display('PLAN_TABLE', null, 'BASIC'));
----------------------------------------------
| Id | Operation | Name |
----------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | PX COORDINATOR | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 |
| 3 | SORT UNIQUE | |
| 4 | PX RECEIVE | |
| 5 | PX SEND HASH | :TQ10000 |
| 6 | UNION-ALL | |
| 7 | PX BLOCK ITERATOR | |
| 8 | TABLE ACCESS FULL| TEST_TBL |
| 9 | PX SELECTOR | |
| 10 | REMOTE | TEST_TBL1 |
| 11 | PX SELECTOR | |
| 12 | REMOTE | TEST_TBL2 |
| 13 | PX SELECTOR | |
| 14 | REMOTE | TEST_TBL3 |
----------------------------------------------
Have a fun 🙂
Tomasz