Set operators like UNION or UNION ALL consist of multiple queries (branches) combined to a single SQL statement. Traditionally, set operators are processed in a sequential manner. Individual branches can be processed in serial or parallel, but only one branch at a time, one branch after another. While this approach satisfies many use cases, there are situations where the processing of multiple branches of a UNION or UNION ALL statement should occur concurrently. The most typical situation is when several or all branches are remote SQL statements. In this situation, concurrent processing on all participating remote systems is desired to speed up the overall processing time without increasing the workload of any participating system.
The default behavior of concurrent execution for UNION or UNION ALL statements is controlled by the setting of the OPTIMIZER_FEATURES_ENABLE initialization parameter. When set to 12.1, concurrent execution is enabled by default. Any statement where at least one branch of the statement is local and is considered being processed in parallel, the entire UNION or UNION ALL statement is also processed concurrently. The system calculates the DOP for every individual local branch of the statement and chooses the highest DOP for the execution of the entire UNION or UNION ALL statement. The system then works concurrently on as many branches as possible, using the chosen DOP both for parallelization of the branches that are processed in parallel, and as concurrent workers on serial and remote statements.
When the OPTIMIZER_FEATURES_ENABLE initialization parameter is set to a value less than 12.1, concurrent execution of UNION or UNION ALL statements must be enabled explicitly by using the PQ_CONCURRENT_UNION hint.
However, unlike the sequential processing of one branch after another, the concurrent processing does not guarantee an ordered return of the results of the individual branches. If an ordered return of one branch after another is required, then you either must disable concurrent processing using the NO_PQ_CONCURRENT_UNION hint or you must augment the SQL statement to uniquely identify individual branches of the statement and to sort on this specified identifier.
UNION or UNION ALL statements that only consist of serial or remote branches are not processed concurrently unless specifically using the PQ_CONCURRENT_UNION hint. The DOP of this SQL statement is at most the number of serial and remote inputs.
Whether or not concurrent processing of a UNION or UNION ALL statement occurs can be easily identified with the execution plan of the SQL statements. When executed in parallel, the execution of serial and remote branches is managed with a row source identifiable as PX SELECTOR. Statements that are not processed concurrently show the query coordinator (QC) as coordinator of serial and remote branches.
In Example 8-7, the SQL statement consists of local and remote branches. The SQL statement loads information about gold and platinum customers from the local database, and the information about customers from three major cities from remote databases. Because the local select statements occur in parallel, this processing is automatically performed in parallel. Each serial branch is executed by only one parallel execution server process. Because each parallel execution server can execute one serial branch, they are executed concurrently.
Example 8-7 Explain Plan for UNION ALL
SQL> EXPLAIN PLAN FOR INSERT INTO all_customer SELECT * FROM GOLD_customer UNION ALL SELECT * FROM PLATINUM_customer UNION ALL SELECT * FROM SF_customer@san_francisco UNION ALL SELECT * FROM LA_customer@los_angeles UNION ALL SELECT * FROM LV_customer@las_vegas; ------------------------------------------------------------------------------- | Id | Operation | Name | TQ/Ins |IN-OUT | PQ Distrib| ------------------------------------------------------------------------------- | 0 | INSERT STATEMENT | | | | | | 1 | LOAD TABLE CONVENTIONAL | ALL_CUSTOMER | | | | | 2 | PX COORDINATOR | | | | | | 3 | PX SEND QC (RANDOM) | :TQ10003 | | P->S | QC (RAND) | | 4 | UNION-ALL | | | PCWP | | | 5 | PX BLOCK ITERATOR | | | PCWC | | | 6 | TABLE ACCESS FULL | GOLD_CUSTOMER| | PCWP | | | 7 | PX BLOCK ITERATOR | | | PCWC | | | 8 | TABLE ACCESS FULL | PLATINUM_CUST| | PCWP | | | 9 | PX SELECTOR | | | PCWP | | |10 | REMOTE | SF_CUSTOMER | | PCWP | | |11 | PX SELECTOR | | | PCWP | | |12 | REMOTE | LA_CUSTOMER | | PCWP | | |13 | PX SELECTOR | | | PCWP | | |14 | REMOTE | LV_CUSTOMER | | PCWP | | -------------------------------------------------------------------------------