Recursive SELECT Example
The following example demonstrates a recursive query:
EXPLAIN WITH RECURSIVE temp_table (employee_number, depth) AS (SELECT root.employee_number, 0 as depth FROM Employee root WHERE root.manager_employee_number = 801 UNION ALL SELECT indirect.employee_number, seed.depth+1 as depth FROM temp_table seed, Employee indirect WHERE seed.employee_number = indirect.manager_employee_number AND depth <= 20) SELECT employee_number, depth FROM temp_table;
EXPLAIN generates the following report for this request:
...
2) Next, we lock PERSONNEL.root for read.
3) We do an all-AMPs RETRIEVE step from PERSONNEL.root by
way of an all-rows scan with a condition of (
"PERSONNEL.root.manager_employee_number = 801") into
Spool 3 (all_amps), which is built locally on the AMPs. The size
of Spool 3 is estimated with no confidence to be 1 row. The
estimated time for this step is 0.06 seconds.
4) We do an all-AMPs RETRIEVE step from Spool 3 by way of an all-rows
scan into Spool 2 (all_amps), which is built locally on the AMPs.
The size of Spool 2 is estimated with no confidence to be 1 row.
The estimated time for this step is 0.07 seconds.
5) 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 with a condition of ("(DEPTH <= 20)
AND (NOT (EMPLOYEE_NUMBER IS NULL ))") into Spool 4
(all_amps), which is duplicated on all AMPs. The size of
Spool 4 is estimated with no confidence to be 8 rows. The
estimated time for this step is 0.03 seconds.
2) We do an all-AMPs RETRIEVE step from
PERSONNEL.indirect by way of an all-rows scan with a
condition of
("NOT (PERSONNEL.indirect.manager_employee_number IS NULL)")
into Spool 5 (all_amps), which is built locally on the AMPs.
The size of Spool 5 is estimated with no confidence to be 8
rows. The estimated time for this step is 0.01 seconds.
6) We do an all-AMPs JOIN step from Spool 4 (Last Use) by way of an
all-rows scan, which is joined to Spool 5 (Last Use) by way of an
all-rows scan. Spool 4 and Spool 5 are joined using a single
partition hash join, with a join condition of ("EMPLOYEE_NUMBER =
manager_employee_number"). The result goes into Spool 6
(all_amps), which is built locally on the AMPs. The size of Spool
6 is estimated with no confidence to be 3 rows. The estimated
time for this step is 0.08 seconds.
7) We do an all-AMPs RETRIEVE step from Spool 6 (Last Use) by way of
an all-rows scan into Spool 3 (all_amps), which is built locally
on the AMPs. The size of Spool 3 is estimated with no confidence
to be 4 rows. The estimated time for this step is 0.07 seconds.
If one or more rows are inserted into spool 3, then go to step 4.
8) We do an all-AMPs RETRIEVE step from Spool 2 (Last Use) by way of
an all-rows scan into Spool 7 (all_amps), which is built locally
on the AMPs. The size of Spool 7 is estimated with no confidence
to be 142 rows. The estimated time for this step is 0.07 seconds.
...
Step 3 indicates the processing of the seed statement inside the recursive query and produces the initial temporary result set.
Steps 4 through 7 correspond to the processing of the recursive statement inside the recursive query and repeat until no new rows are inserted into the temporary result set. Although steps 4 through 7 indicate a static plan, each iteration can produce spools with varying cardinalities; thus, the level of confidence for the spool size in these steps is set to no confidence.
Step 8 indicates the processing of the final result that is sent back to the user.