 |
 |
Archives of the TeradataForum
Message Posted: Wed, 21 Jan 2004 @ 14:14:29 GMT
| Subj: | | Statistics and explain plans |
| |
| From: | | Ferry, Craig |
I am running the following SQL. I have collected statistics on every column of every table that is referenced in the SQL. When I run an
explain plan (see after the SQL below) on this SQL, I still see numerous 'no confidence' messages. I thought that these type of messages always
came from not having enough statistics collected.
Any thoughts as to why this is occurring?
TIA
Craig
SELECT '&RDrill' AS RPT_TYPE,
b.group_id AS GROUP_ID,
group_name as GROUP_NAME,
b.region_id AS REGION_ID,
region_name as REGION_NAME,
d.branch_id AS BRANCH_ID,
branch_name as BRANCH_NAME,
h.supplier_no AS SUPPLIER_NO,
s.supplier_name AS SUPPLIER_NAME,
count(h.purchase_ord_no) AS TOTAL_POS,
count(*) AS TOTAL_LINES,
sum(d.extended_cost) AS COST
FROM dss_tables.po_detail d
LEFT OUTER JOIN dss_tables.po_header h
ON h.branch_id = d.branch_id
AND h.sequence_no = d.sequence_no
AND h.date_transaction = d.date_transaction
LEFT OUTER JOIN (SELECT branch_id, supplier_no, supplier_name
FROM dss_tables.supplier
GROUP BY 1,2,3) as s
ON h.branch_id = s.branch_id
AND h.supplier_no = s.supplier_no
LEFT OUTER JOIN dss_tables.branch b
ON d.branch_id = b.branch_id
LEFT OUTER JOIN dss_tables.region r
ON b.region_id = r.region_id
LEFT OUTER JOIN dss_tables.wgroup w
ON b.group_id = w.group_id
WHERE d.sim_mfr_no IN ('788888', '628888')
AND d.date_loaded > '2004/01/01'
AND d.date_loaded < '2004/01/30'
AND b.REGION_ID = 8110
GROUP BY 1,2,3,4,5,6,7,8,9;
Explanation
-------------------------------------------------- | |
| | 1) | First, we lock dss_tables.branch_tbl for access, we lock dss_tables.supplier for access, we lock dss_tables.a for access, we lock
dss_tables.po_header_tbl for access, we lock dss_tables.w for access, and we lock dss_tables.r for access.
| |
| | 2) | Next, we do an all-AMPs SUM step to aggregate from dss_tables.po_header_tbl by way of an all-rows scan with no residual conditions, and the
grouping identifier in field 1. Aggregate Intermediate Results are computed globally, then placed in Spool 7. The input table will not be cached
in memory. The size of Spool 7 is estimated with no confidence to be 2,350,721 rows. The estimated time for this step is 19.41 seconds.
| |
| | 3) | We do an all-AMPs RETRIEVE step from Spool 7 (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 no confidence to be 2,350,721 rows. The estimated time for this step is 1.82 seconds.
| |
| | 4) | We execute the following steps in parallel.
| |
| | |
| | 1) | We do an all-AMPs RETRIEVE step from Spool 1 (Last Use) by way of an all-rows scan into Spool 11 (all_amps), which is redistributed by
hash code to all AMPs. The size of Spool 11 is estimated with no confidence to be 2,350,721 rows. The estimated time for this step is 6.23
seconds.
| | |
| | |
| | 2) | We do an all-AMPs RETRIEVE step from dss_tables.a by way of an all-rows scan with no residual conditions locking for access into Spool 12
(all_amps), which is redistributed by hash code to all AMPs. The input table will not be cached in memory. The size of Spool 12 is estimated
with high confidence to be 3,725,177 rows. The estimated time for this step is 13.67 seconds.
| | |
| | 5) | We do an all-AMPs JOIN step from Spool 11 (Last Use) by way of an all-rows scan, which is joined to Spool 12 (Last Use). Spool 11 and
Spool 12 are joined using a single partition hash join, with a join condition of ("(branch_id = BRANCH_ID) AND ((purchase_ord_no =
PURCHASE_ORD_NO) AND (date_transaction = DATE_TRANSACTION =))"). The result goes into Spool 10 (all_amps), which is built locally on the AMPs.
The size of Spool 10 is estimated with no confidence to be 2,350,721 rows. The estimated time for this step is 2.70 seconds.
| |
| | 6) | We do an all-AMPs SUM step to aggregate from Spool 10 (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 13. The size of Spool 13 is estimated with no confidence to be
2,115,649 rows. The estimated time for this step is 9.62 seconds.
| |
| | 7) | We execute the following steps in parallel.
| |
| | |
| | 1) | We do an all-AMPs RETRIEVE step from Spool 13 (Last Use) 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 2,115,649 rows. The estimated time for this step is 1.63 seconds.
| | |
| | |
| | 2) | We do an all-AMPs SUM step to aggregate from dss_tables.supplier 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 25. The size of Spool 25 is estimated with no
confidence to be 370,462 rows. The estimated time for this step is 0.93 seconds.
| | |
| | 8) | We execute the following steps in parallel.
| |
| | |
| | 1) | We do an all-AMPs RETRIEVE step from Spool 25 (Last Use) by way of an all-rows scan into Spool 5 (all_amps), which is built locally on
the AMPs. The size of Spool 5 is estimated with no confidence to be 370,462 rows. The estimated time for this step is 0.37 seconds.
| | |
| | |
| | 2) | We do an all-AMPs RETRIEVE step from dss_tables.branch_tbl by way of an all-rows scan with a condition of
("dss_tables.branch_tbl.region_id = 8110") into Spool 30 (all_amps), which is redistributed by hash code to all AMPs. Then we do a SORT to order
Spool 30 by row hash. The size of Spool 30 is estimated with high confidence to be 45 rows. The estimated time for this step is 0.03 seconds.
| | |
| | 9) | We do an all-AMPs JOIN step from Spool 30 (Last Use) by way of a RowHash match scan, which is joined to dss_tables.r. Spool 30 and
dss_tables.r are left outer joined using a merge join, with a join condition of ("region_id = dss_tables.r.region_id"). The result goes into
Spool 31 (all_amps), which is redistributed by hash code to all AMPs. Then we do a SORT to order Spool 31 by row hash. The size of Spool 31 is
estimated with low confidence to be 45 rows. The estimated time for this step is 0.02 seconds.
| |
| | 10) | We execute the following steps in parallel.
| |
| | |
| | 1) | We do an all-AMPs JOIN step from dss_tables.w by way of a RowHash match scan with no residual conditions, which is joined to Spool 31
(Last Use). dss_tables.w and Spool 31 are right outer joined using a merge join, with a join condition of ("group_id = dss_tables.w.group_id").
The result goes into Spool 34 (all_amps), which is redistributed by hash code to all AMPs. The size of Spool 34 is estimated with low confidence
to be 45 rows. The estimated time for this step is 0.04 seconds.
| | |
| | |
| | 2) | We do an all-AMPs RETRIEVE step from dss_tables.a by way of an all-rows scan with a condition of ("(dss_tables.a.date_loaded < DATE
'2004-01-30') AND ((dss_tables.a.date_loaded > DATE '2004-01-01') AND (((dss_tables.a.sim_mfr_no = '788888') OR (dss_tables.a.sim_mfr_no =
'628888')) AND ((NOT (dss_tables.a.branch_id IS NULL )) AND ((NOT (dss_tables.a.sequence_no IS NULL )) AND (NOT (dss_tables.a.date_transaction IS
NULL ))))))") into Spool 37 (all_amps), which is redistributed by hash code to all AMPs. The input table will not be cached in memory. The size
of Spool 37 is estimated with high confidence to be 353,229 rows. The estimated time for this step is 11.02 seconds.
| | |
| | 11) | We do an all-AMPs JOIN step from Spool 34 (Last Use) by way of an all-rows scan, which is joined to Spool 37 (Last Use). Spool 34 and
Spool 37 are joined using a product join, with a join condition of ("branch_id = branch_id"). The result goes into Spool 38 (all_amps), which is
redistributed by hash code to all AMPs. The size of Spool 38 is estimated with low confidence to be 44,154 rows. The estimated time for this
step is 0.38 seconds.
| |
| | 12) | We do an all-AMPs JOIN step from Spool 38 (Last Use) by way of an all-rows scan, which is joined to dss_tables.a with a condition of
("(NOT (dss_tables.a.purchase_ord_no IS NULL )) AND ((NOT (dss_tables.a.sequence_no IS NULL )) AND (NOT (dss_tables.a.date_transaction IS NULL
)))") locking dss_tables.a for access. Spool 38 and dss_tables.a are joined using a single partition hash join, with a join condition of
("(branch_id = dss_tables.a.branch_id) AND ((sequence_no = dss_tables.a.sequence_no) AND (date_transaction = dss_tables.a.date_transaction
))"). The input table dss_tables.a will not be cached in memory. The result goes into Spool 39 (all_amps), which is redistributed by hash code
to all AMPs. The size of Spool 39 is estimated with low confidence to be 44,154 rows. The estimated time for this step is 4.16 seconds.
| |
| | 13) | We do an all-AMPs RETRIEVE step from Spool 2 by way of an all-rows scan with a condition of ("(NOT (BRANCH_ID IS NULL )) AND ((NOT
(PURCHASE_ORD_NO IS NULL )) AND ((NOT (SEQUENCE_NO IS NULL )) AND ((NOT (BRANCH_ID IS NULL )) AND (NOT (SEQUENCE_NO IS NULL )))))") into Spool 40
(all_amps), which is redistributed by hash code to all AMPs. The size of Spool 40 is estimated with no confidence to be 2,115,649 rows. The
estimated time for this step is 5.56 seconds.
| |
| | 14) | We execute the following steps in parallel.
| |
| | |
| | 1) | We do an all-AMPs RETRIEVE step from Spool 2 (Last Use) by way of an all-rows scan with a condition of ("(NOT (BRANCH_ID IS NULL )) AND
((NOT (PURCHASE_ORD_NO IS NULL )) AND (NOT (SEQUENCE_NO IS NULL )))") into Spool 41 (all_amps), which is redistributed by hash code to all AMPs.
The size of Spool 41 is estimated with no confidence to be 2,115,649 rows. The estimated time for this step is 5.56 seconds.
| | |
| | |
| | 2) | We do an all-AMPs RETRIEVE step from dss_tables.a by way of an all-rows scan with a condition of ("(NOT (dss_tables.a.branch_id IS NULL
)) AND ((NOT (dss_tables.a.purchase_ord_no IS NULL )) AND ((NOT (dss_tables.a.sequence_no IS NULL )) AND ((NOT (dss_tables.a.date_transaction IS
NULL )) AND ((NOT (dss_tables.a.sequence_no IS NULL )) AND (NOT (dss_tables.a.branch_id IS NULL ))))))") locking for access into Spool 42
(all_amps), which is redistributed by hash code to all AMPs. The input table will not be cached in memory. The size of Spool 42 is estimated
with high confidence to be 3,725,177 rows. The estimated time for this step is 14.31 seconds.
| | |
| | 15) | We do an all-AMPs JOIN step from Spool 39 (Last Use) by way of an all-rows scan, which is joined to Spool 40 (Last Use). Spool 39 and
Spool 40 are joined using a single partition hash join, with a join condition of ("(sequence_no = SEQUENCE_NO) AND ((branch_id = BRANCH_ID) AND
((sequence_no = SEQUENCE_NO) AND ((purchase_ord_no = PURCHASE_ORD_NO) AND (branch_id = BRANCH_ID ))))"). The result goes into Spool 43
(all_amps), which is redistributed by hash code to all AMPs. Then we do a SORT to order Spool 43 by row hash. The size of Spool 43 is estimated
with no confidence to be 2,115,649 rows. The estimated time for this step is 12.14 seconds.
| |
| | 16) | We do an all-AMPs JOIN step from Spool 41 (Last Use) by way of an all-rows scan, which is joined to Spool 42 (Last Use). Spool 41 and
Spool 42 are joined using a single partition hash join, with a join condition of ("(sequence_no = SEQUENCE_NO) AND ((purchase_ord_no =
PURCHASE_ORD_NO) AND (branch_id = BRANCH_ID))"). The result goes into Spool 44 (all_amps), which is redistributed by hash code to all AMPs.
Then we do a SORT to order Spool 44 by row hash. The size of Spool 44 is estimated with no confidence to be 2,115,649 rows. The estimated time
for this step is 7.05 seconds.
| |
| | 17) | We execute the following steps in parallel.
| |
| | |
| | 1) | We do an all-AMPs JOIN step from Spool 43 (Last Use) by way of a RowHash match scan, which is joined to Spool 44 (Last Use). Spool 43
and Spool 44 are left outer joined using a merge join, with a join condition of ("(date_transaction = date_transaction) AND ((sequence_no =
sequence_no) AND (branch_id = branch_id ))"). The result goes into Spool 45 (all_amps), which is redistributed by hash code to all AMPs. Then
we do a SORT to order Spool 45 by row hash. The size of Spool 45 is estimated with low confidence to be 2,115,649 rows. The estimated time for
this step is 14.14 seconds.
| | |
| | |
| | 2) | We do an all-AMPs RETRIEVE step from Spool 5 (Last Use) by way of an all-rows scan with a condition of ("(NOT (BRANCH_ID IS NULL )) AND
(NOT (SUPPLIER_NO IS NULL ))") into Spool 48 (all_amps), which is redistributed by hash code to all AMPs. Then we do a SORT to order Spool 48 by
row hash. The size of Spool 48 is estimated with no confidence to be 370,462 rows. The estimated time for this step is 0.99 seconds.
| | |
| | 18) | We do an all-AMPs JOIN step from Spool 45 (Last Use) by way of a RowHash match scan, which is joined to Spool 48 (Last Use). Spool 45 and
Spool 48 are left outer joined using a merge join, with a join condition of ("(supplier_no = SUPPLIER_NO) AND (branch_id = BRANCH_ID)"). The
result goes into Spool 29 (all_amps), which is built locally on the AMPs. The result spool file will not be cached in memory. The size of Spool
29 is estimated with no confidence to be 7,664,077 rows. The estimated time for this step is 14.39 seconds.
| |
| | 19) | We do an all-AMPs SUM step to aggregate from Spool 29 (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 51. The aggregate spool file will not be cached in memory. The size
of Spool 51 is estimated with low confidence to be 7,664,077 rows. The estimated time for this step is 3 minutes and 33 seconds.
| |
| | 20) | We do an all-AMPs RETRIEVE step from Spool 51 (Last Use) by way of an all-rows scan into Spool 27 (group_amps), which is built locally on
the AMPs. The result spool file will not be cached in memory. The size of Spool 27 is estimated with low confidence to be 7,664,077 rows. The
estimated time for this step is 18.08 seconds.
| |
| | 21) | Finally, we send out an END TRANSACTION step to all AMPs involved in processing the request.
| |
| | -> | The contents of Spool 27 are sent back to the user as the result of statement 1.
| |
| |
