Not sure from which context you are looking. However, I ran an explain test:
2) Next, we execute the following steps in parallel.
Is this what you want to confirm?
It is a decision made by the optimizer based on several factors. Optimizer decides whether to execute statements sequentailly or parallel.
the statements independent of each other are mostly executed in parallel, and the statements which use common objects will be executed in serial.
For example if your union statements are selecting from different tables, then optimizer can go for parallel, but if the same table is being used in multiple slects then there is chance for sequential execution.
I understood the explanation.Thanks for that.
Just an analogy explained below.
Suppose I want to extract the rows based on 1 to 10000 id's.If a write a single sql statement then it's going to take a long time.So Ihave written multiple SQL stmts using UNION like
select * from table where id between 1 and 1000
select * from table where id between 1001 and 2000
Here My question is all the SQL statements submit to the server in parallel and get the results set in parallel.
Will it save time better than the first SQL stmt?
a UNION needs to insert both results into the same spool -> the individual SELECT might run in parallel (if they need multiple steps due to joins, etc), but the final inserts are always processed serially.
Splitting this query returning 10000 ids into multiple queries plus UNION (or better UNION ALL to avoid the DISTINCT processing) will never be faster.
How many rows are in this table, what's the PI and partitioning and what's your actual query and why do you think it's slow?
Here's a variation where it would be great all if the select's of a union all executed in parallel and the resulting spools were combined sequentially at the end...
select count(*) as Table1_Count from Table 1 where key = 123 union all
select count(*) as Table2_Count from Table 2 where key = 123 union all
select count(*) as Table3_Count from Table 3 where key = 123
Instead we (only) see parallel behavior for the pairs of: the spool from the previous select getting combined to a common spool while the current select runs. Is there a way to indicate to the query optimizer that union all select's should be run in parallel?
3) We execute the following steps in parallel.
1) We do an all-AMPs RETRIEVE step from Spool 3 (Last Use) by
way of an all-rows scan into Spool 1 (all_amps), which is
built locally on the AMPs. The size of Spool 1 is estimated
with high confidence to be 1 row (83 bytes). The estimated
time for this step is 0.00 seconds.
2) We do an all-AMPs SUM step to aggregate from a single
partition of db.table with a condition
of ("db.table2.key = 6140") with a
residual condition of ("db.table2.key = 6140") , grouping by field1 (
Intermediate Results are computed globally, then placed in
Spool 6. The size of Spool 6 is estimated with high
confidence to be 1 row (29 bytes). The estimated time for
this step is 0.02 seconds.
There is not such an option. It would not work for three reasons under current optimizer/execution engine rules.
- Currently the engine is not able to write to the same spool file from multiple concurrent steps. This means that the write to the union spool steps cannot be executed in parallel.
- Currently we do not dispatch more than 2 all-AMP steps concurrently regardless of how many concurrent steps are shown in the explain (more single/few AMP steps can be run in parallel). Even if we grouped all the select steps together we would only execute two of them at a time, and then we would have all the steps for writing into the final spool to do sequentially. Running more steps sequentially would allow a single query to significantly over use resources relative to other queries on the system.
- The algorithm for parallel steps currently does not allow for reordering of steps in the plan. And as soon as there is a dependency like the use of the spool produced from the previous step, then there has to be a parallel step break. Without reordering, the step for writing into the union spool is going to be a parallel step break from the previous steep tha makes the spool.