Explanation
1) First, we lock a distinct plato."pseudo table" for read on a
RowHash to prevent global deadlock for plato.objres.
2) Next, we lock a distinct plato."pseudo table" for read on a
RowHash to prevent global deadlock for plato.object.
3) We lock a distinct plato."pseudo table" for read on a RowHash to
prevent global deadlock for plato.land.
4) We lock a distinct plato."pseudo table" for read on a RowHash to
prevent global deadlock for plato.huisvesting.
5) We lock a distinct plato."pseudo table" for read on a RowHash to
prevent global deadlock for plato.garantie.
6) We lock a distinct SYS_CALENDAR."pseudo table" for read on a
RowHash to prevent global deadlock for SYS_CALENDAR.CALDATES.
7) We lock plato.objres for read, we lock plato.object for read, we
lock plato.land for read, we lock plato.huisvesting for read, we
lock plato.garantie for read, and we lock SYS_CALENDAR.CALDATES
for read.
8) We do an all-AMPs SUM step to aggregate from plato.garantie by way
of an all-rows scan with no residual conditions, and the grouping
identifier in field 1. Aggregate Intermediate Results are
computed locally, then placed in Spool 6. The size of Spool 6 is
estimated with low confidence to be 78 rows. The estimated time
for this step is 0.61 seconds.
9) We do an all-AMPs RETRIEVE step from Spool 6 (Last Use) by way of
an all-rows scan into Spool 8 (all_amps), which is redistributed
by hash code to all AMPs. Then we do a SORT to order Spool 8 by
row hash. The size of Spool 8 is estimated with low confidence to
be 78 rows. The estimated time for this step is 0.17 seconds.
10) We execute the following steps in parallel.
1) We do an all-AMPs JOIN step from plato.garantie by way of an
all-rows scan with a condition of ("(NOT
(plato.garantie.percentage_werkelijk IS NULL )) AND
(plato.garantie.seizoen > '03Z')"), which is joined to Spool 8
(Last Use). plato.garantie and Spool 8 are joined using an
inclusion merge join, with a join condition of (
"(plato.garantie.objectnummer = objectnummer) AND
((plato.garantie.seizoen = seizoen) AND
(plato.garantie.volgnummer = Field_4 ))"). The result goes
into Spool 9 (all_amps), which is duplicated on all AMPs. The
size of Spool 9 is estimated with index join confidence to be
5,516 rows. The estimated time for this step is 0.69 seconds.
2) We do an all-AMPs RETRIEVE step from plato.objres by way of an
all-rows scan with a condition of (
"(plato.objres.reserveringsvolgnummer IS NULL) AND
(plato.objres.dubbel_indicator IS NULL)") into Spool 10
(all_amps), which is redistributed by hash code to all AMPs.
Then we do a SORT to order Spool 10 by row hash. The size of
Spool 10 is estimated with high confidence to be 838,801 rows.
The estimated time for this step is 2 minutes and 17 seconds.
11) We do an all-AMPs JOIN step from Spool 9 (Last Use) by way of an
all-rows scan, which is joined to SYS_CALENDAR.CALDATES. Spool 9
and SYS_CALENDAR.CALDATES are joined using a product join, with a
join condition of ("(SYS_CALENDAR.CALDATES.cdate <= tot_met_datum)
AND (SYS_CALENDAR.CALDATES.cdate >= van_datum)"). The result goes
into Spool 11 (all_amps), which is redistributed by hash code to
all AMPs. Then we do a SORT to order Spool 11 by row hash. The
result spool file will not be cached in memory. The size of Spool
11 is estimated with index join confidence to be 25,245,230 rows.
The estimated time for this step is 2 hours and 41 minutes.
12) We do an all-AMPs JOIN step from Spool 10 (Last Use) by way of a
RowHash match scan, which is joined to Spool 11 (Last Use). Spool
10 and Spool 11 are right outer joined using a merge join, with a
join condition of ("(cdate <= datum_tot) AND ((cdate >= datum_van)
AND ((seizoen = seizoen) AND ((kamertype = kamertype) AND
((objectnummer = objectnummer) AND (Bedrijfsnummer =
Bedrijfsnummer )))))"). The result goes into Spool 3 (all_amps),
which is built locally on the AMPs. The result spool file will
not be cached in memory. The size of Spool 3 is estimated with
low confidence to be 418,865,081 rows. The estimated time for
this step is 9 hours and 34 minutes.
13) We do an all-AMPs SUM step to aggregate from Spool 3 (Last Use) by
way of an all-rows scan, and the grouping identifier in field 1.
Aggregate Intermediate Results are computed globally, then placed
in Spool 14. The aggregate spool file will not be cached in
memory. The size of Spool 14 is estimated with low confidence to
be 418,865,081 rows. The estimated time for this step is 299
hours and 35 minutes.
14) We execute the following steps in parallel.
1) We do an all-AMPs RETRIEVE step from Spool 14 (Last Use) by
way of an all-rows scan into Spool 1 (all_amps), which is
built locally on the AMPs. The result spool file will not be
cached in memory. The size of Spool 1 is estimated with low
confidence to be 418,865,081 rows. The estimated time for
this step is 11 hours and 5 minutes.
2) We do an all-AMPs RETRIEVE step from plato.land by way of an
all-rows scan with no residual conditions into Spool 17
(all_amps), which is duplicated on all AMPs. The size of
Spool 17 is estimated with high confidence to be 1,656 rows.
The estimated time for this step is 0.56 seconds.
3) We do an all-AMPs RETRIEVE step from plato.huisvesting by way
of an all-rows scan with a condition of (
"plato.huisvesting.DatumTot IS NULL") into Spool 18 (all_amps),
which is duplicated on all AMPs. Then we do a SORT to order
Spool 18 by row hash. The size of Spool 18 is estimated with
high confidence to be 31,484 rows. The estimated time for
this step is 1.80 seconds.
15) We execute the following steps in parallel.
1) We do an all-AMPs JOIN step from Spool 17 (Last Use) by way of
an all-rows scan, which is joined to Spool 1 (Last Use) with a
condition of ("SEIZOEN > '03Z'"). Spool 17 and Spool 1 are
joined using a product join, with a join condition of (
"BEDRIJFSNUMMER = Bedrijfsnummer"). The result goes into
Spool 19 (all_amps), which is built locally on the AMPs. Then
we do a SORT to order Spool 19 by row hash. The result spool
file will not be cached in memory. The size of Spool 19 is
estimated with no confidence to be 8,473,004 rows. The
estimated time for this step is 30 hours and 32 minutes.
2) We do an all-AMPs RETRIEVE step from plato.object by way of an
all-rows scan with a condition of ("plato.object.DatumTot IS
NULL") into Spool 20 (all_amps), which is duplicated on all
AMPs. Then we do a SORT to order Spool 20 by row hash. The
size of Spool 20 is estimated with high confidence to be
111,460 rows. The estimated time for this step is 7.02
seconds.
16) We do an all-AMPs JOIN step from Spool 18 (Last Use) by way of a
RowHash match scan, which is joined to Spool 19 (Last Use). Spool
18 and Spool 19 are joined using a merge join, with a join
condition of ("(Bedrijfsnummer = Bedrijfsnummer) AND ((KAMERTYPE =
huisvestingscode) AND (BEDRIJFSNUMMER = Bedrijfsnummer ))"). The
result goes into Spool 21 (all_amps), which is built locally on
the AMPs. Then we do a SORT to order Spool 21 by row hash. The
result spool file will not be cached in memory. The size of Spool
21 is estimated with no confidence to be 8,059,338 rows. The
estimated time for this step is 1 hour and 31 minutes.
17) We do an all-AMPs JOIN step from Spool 20 (Last Use) by way of a
RowHash match scan, which is joined to Spool 21 (Last Use). Spool
20 and Spool 21 are joined using a merge join, with a join
condition of ("(Bedrijfsnummer = Bedrijfsnummer) AND
((Bedrijfsnummer = Bedrijfsnummer) AND ((Landcode = landcode) AND
((OBJECTNUMMER = Objectnummer) AND (BEDRIJFSNUMMER =
Bedrijfsnummer ))))"). The result goes into Spool 16 (group_amps),
which is built locally on the AMPs. Then we do a SORT to order
Spool 16 by the sort key in spool field1. The result spool file
will not be cached in memory. The size of Spool 16 is estimated
with no confidence to be 7,756,751 rows. The estimated time for
this step is 26 minutes and 40 seconds.
18) Finally, we send out an END TRANSACTION step to all AMPs involved
in processing the request.
-> The contents of Spool 16 are sent back to the user as the result
of statement 1.
