Oracle Convert Select Union All to Parallel Select

Question

I don't know if it's possible or not in terms of oracle execution architecture so I just wanted to ask.
I have a table that stores some schemas names (eg. sch1, sch2, sch3 ) and I am creating a union all query dynamically within a cursor using this table.
so eg. my query looks like this :

select col1, col2 from sch1.tab1
union all 
select col1, col2 from sch2.tab1
union all 
select col1, col2 from sch3.tab1

eg. If I Select a table with parallel hint like

select /*+ parallel(tab,4)*/ * from tab

if I am not mistaken it will opens 4 sessions and process it in one execution.
so is it possible to query all tables in this approach?

`+` must be the very first character in comment if it shall be treated as hint. Do `/*+ parallel(tab,4) */ ` — Wernfried Domscheit, Nov 23 '16 at 13:05
@WernfriedDomscheit It looks like OP is searching for `PQ_CONCURRENT_UNION` — Lukasz Szozda, Sep 23 '18 at 10:05

score 3 · Answer 1 · edited Jun 20 '20 at 09:12

You are searching for Concurrent Execution of Union All:

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.

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.

SELECT /*+ PQ_CONCURRENT_UNION(@"SET$1") */ * 
FROM (
  select col1, col2 from sch1.tab1
  union all 
  select col1, col2 from sch2.tab1
  union all 
  select col1, col2 from sch3.tab1
) a;

You don't need to do anything if you OPTIMIZER_FEATURE_ENABLED is 12.1+ and at least one branch is considered being processed in parallel.

Oracle Convert Select Union All to Parallel Select

1 Answers1