 |
 |
Archives of the TeradataForum
Message Posted: Tue, 03 Feb 2004 @ 12:20:57 GMT
| Subj: | | Query Performance |
| |
| From: | | Ferry, Craig |
I am running the following query and am running out of spool space (my user has 50 GB). I have statistics collected on every column of each
table in the query (see below). When I look at the explain, I see things such as low confidence and all-rows scan. Are there any resources that
are available which will help me to better understand the explain plans? I don't know if there is a better way that I could have run this
query.
Thanks so much for the help.
Craig
select sd.branch_id, b.branch_name, sd.date_loaded,
sum(sales_amount) as sales, sum(cost_amount) as cost
from sales_detail sd, mw_panduit_id p, branch b
where sd.date_loaded between '2003/03/01' and '2003/06/01'
and sd.sim_mfr_no = p.sim_mfr_no
and sd.sim_item_no = p.sim_item_no
and sd.branch_id = b.branch_id
and ((je_prime_no = '007' AND je_sub_no IN ('01', '02'))
OR (je_prime_no = '006' AND je_sub_no = '01' AND acct_prime_no = '')
OR (je_prime_no = '006' AND je_sub_no = '01' AND acct_prime_no > '0' AND sales_amount = 0.0)
OR (je_prime_no = '006' AND je_sub_no = '01' AND acct_prime_no = '222'
and acct_sub_no in ('05','11','12') AND adder_code = '1')
OR (sd.branch_id in (7302,7304,7305) and je_prime_no = '006' AND je_sub_no = '01'
AND acct_prime_no = '201' and acct_sub_no = '02'))
group by 1,2,3
order by 1,2,3
Explanation
-------------------------------------------------- | |
| | 1) | First, we lock dss_tables.branch_tbl for access, we lock DSS_TABLES.sd for access, and we lock DSS_TABLES.p for access.
| |
| | 2) | Next, we do an all-AMPs RETRIEVE step from DSS_TABLES.p by way of an all-rows scan with no residual conditions into Spool 4 (all_amps),
which is duplicated on all AMPs. Then we do a SORT to order Spool 4 by row hash. The size of Spool 4 is estimated with high confidence to be
92,232 rows. The estimated time for this step is 0.15 seconds.
| |
| | 3) | We do an all-AMPs JOIN step from Spool 4 (Last Use) by way of an all-rows scan, which is joined to DSS_TABLES.sd by way of a traversal of
index # 16 without accessing the base table extracting row ids only. Spool 4 and DSS_TABLES.sd are joined using a nested join, with a join
condition of ("DSS_TABLES.sd.sim_mfr_no = sim_mfr_no"). The input table DSS_TABLES.sd will not be cached in memory. The result goes into Spool 5
(all_amps), which is built locally on the AMPs. Then we do a SORT to order Spool 5 by field Id 1. The size of Spool 5 is estimated with low
confidence to be 996 rows. The estimated time for this step is 3 minutes and 23 seconds.
| |
| | 4) | We do an all-AMPs JOIN step from Spool 5 (Last Use) by way of an all-rows scan, which is joined to DSS_TABLES.sd with a condition of
("(((DSS_TABLES.sd.je_prime_no = '007') AND ((DSS_TABLES.sd.je_sub_no = '01') OR (DSS_TABLES.sd.je_sub_no = '02'))) OR
(((DSS_TABLES.sd.je_prime_no = '006') AND ((DSS_TABLES.sd.je_sub_no = '01') AND (DSS_TABLES.sd.acct_prime_no = ' '))) OR
(((DSS_TABLES.sd.je_prime_no = '006') AND ((DSS_TABLES.sd.je_sub_no = '01') AND ((DSS_TABLES.sd.acct_prime_no > '0 ') AND
(DSS_TABLES.sd.sales_amount = 0.00 )))) OR (((DSS_TABLES.sd.je_prime_no = '006') AND ((DSS_TABLES.sd.je_sub_no = '01')AND
((DSS_TABLES.sd.acct_prime_no = '222') AND (((DSS_TABLES.sd.acct_sub_no = '05') OR ((DSS_TABLES.sd.acct_sub_no = '11') OR
(DSS_TABLES.sd.acct_sub_no = '12'))) AND (DSS_TABLES.sd.adder_code = '1'))))) OR (((DSS_TABLES.sd.branch_id =7302) OR ((DSS_TABLES.sd.branch_id =
7304) OR (DSS_TABLES.sd.branch_id = 7305 ))) AND ((DSS_TABLES.sd.je_prime_no = '006') AND ((DSS_TABLES.sd.je_sub_no = '01') AND
((DSS_TABLES.sd.acct_prime_no = '201') AND (DSS_TABLES.sd.acct_sub_no = '02'))))))))) AND ((DSS_TABLES.sd.date_loaded <= DATE '2003-06-01') AND
(DSS_TABLES.sd.date_loaded >= DATE '2003-03-01'))"). Spool 5 and DSS_TABLES.sd are joined using a row id join, with a join condition of
("DSS_TABLES.sd.sim_item_no = sim_item_no"). The input table DSS_TABLES.sd will not be cached in memory. The result goes into Spool 6
(all_amps), which is redistributed by hash code to all AMPs. The size of Spool 6 is estimated with low confidence to be 996 rows. The estimated
time for this step is 3 minutes and 23 seconds.
| |
| | 5) | We do an all-AMPs JOIN step from Spool 6 (Last Use) by way of an all-rows scan, which is joined to dss_tables.branch_tbl. Spool 6 and
dss_tables.branch_tbl are joined using a single partition hash join, with a join condition of ("branch_id = dss_tables.branch_tbl.branch_id").
The result goes into Spool 3 (all_amps), which is built locally on the AMPs. The size of Spool 3 is estimated with low confidence to be 996 rows.
The estimated time for this step is 0.04 seconds.
| |
| | 6) | 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 locally, then placed in Spool 7. The size of Spool 7 is estimated with low confidence to be 996 rows.
The estimated time for this step is 0.03 seconds.
| |
| | 7) | We do an all-AMPs RETRIEVE step from Spool 7 (Last Use) by way of an all-rows scan into Spool 1 (group_amps), which is built locally on the
AMPs. Then we do a SORT to order Spool 1 by the sort key in spool field1. The size of Spool 1 is estimated with low confidence to be 996 rows.
The estimated time for this step is 0.03 seconds.
| |
| | 8) | Finally, we send out an END TRANSACTION step to all AMPs involved in processing the request.
| |
| | -> | The contents of Spool 1 are sent back to the user as the result of statement 1.
| |
Branch_tbl Statistics
Date Time Unique Values Column Names
04/02/02 20:33:48 879 branch_id
04/02/02 20:33:49 854 branch_name
04/02/02 20:33:49 455 mail_address_1
04/02/02 20:33:50 1 mail_address_2
04/02/02 20:33:50 358 mail_city
04/02/02 20:33:51 62 mail_state
04/02/02 20:33:51 421 mail_zip_code
04/02/02 20:33:52 1 street_address_1
04/02/02 20:33:52 1 street_address_2
04/02/02 20:33:53 1 street_city
04/02/02 20:33:54 1 street_state
04/02/02 20:33:54 1 street_zip_code
04/02/02 20:33:55 2 country_code
04/02/02 20:33:55 50 phone_no
04/02/02 20:33:56 1 fax_no
04/02/02 20:33:56 77 area_branch_id
04/02/02 20:33:57 53 region_id
04/02/02 20:33:57 28 group_id
04/02/02 20:33:58 44 treasury_id
04/02/02 20:33:58 8 zone_treasury_id
04/02/02 20:33:59 3 ncr_switch
04/02/02 20:34:00 6 wdc_id
04/02/02 20:34:00 362 naed_zone
04/02/02 20:34:01 66 district_id
mw_panduit_id statistics
Date Time Unique Values Column Names
04/02/02 16:08:35 1 sim_mfr_no
04/02/02 16:08:35 3,294 sim_item_no
04/02/02 16:08:36 3,294 sim_mfr_no,sim_item_no
sales_detail statistics
Date Time Unique Values Column Names
04/01/25 17:04:10 55,606,879 date_loaded,join_no,branch_id
04/01/25 17:00:17 5,209,957 branch_id,sim_mfr_no,sim_item_no,country_code
04/01/25 16:59:34 20,467 date_loaded,branch_id
04/01/25 17:02:24 55,606,879 date_loaded,join_no
04/02/02 16:24:37 444 branch_id
04/02/02 16:33:31 1,814 date_invoice
04/02/02 16:33:34 21,173 sim_mfr_no
04/02/02 16:40:30 99,239 sim_item_no
04/02/02 16:49:01 7,118,750 assembled_sim_no
04/02/02 17:14:14 3,090,702 sim_description
04/02/02 17:20:11 2,970 product_code
04/02/02 17:29:34 7 transaction_code
04/02/02 17:43:21 24,565 quantity
04/02/02 17:47:54 706,430 sales_amount
04/02/02 17:52:49 755,675 cost_amount
04/02/02 17:52:52 109 acct_prime_no
04/02/02 17:56:46 102 acct_sub_no
04/02/02 17:56:49 4 je_prime_no
04/02/02 17:56:52 3 je_sub_no
04/02/02 18:00:33 4 distr_center_code
04/02/02 18:04:06 4 prior_inven_code
04/02/02 18:07:34 4 interbr_supplier
04/02/02 18:11:11 90 gst_item_type
04/02/02 18:14:53 124 outside_comm_code
04/02/02 18:18:43 226 inside_comm_code
04/02/02 18:21:58 62 outside_comm_pct
04/02/02 18:24:19 2 inside_comm_pct
04/02/02 18:27:23 165 gross_profit_pct
04/02/02 18:31:44 613,830 gpo_cost_adj
04/02/02 18:34:52 1 discount_amount
04/02/02 18:38:13 4 adder_code
04/02/02 18:38:18 4 shipment_type
04/02/02 18:41:27 2 country_code
04/02/02 18:44:46 4 sales_transfer
04/02/02 18:48:07 4 cmpy_shipment_type
04/02/02 18:51:11 8 flag_a
04/02/02 18:53:50 3 flag_b
04/02/02 18:56:22 3 flag_c
04/02/02 18:59:01 516,581 orig_price
04/02/02 19:00:35 12 price_chg_reason
04/02/02 19:01:56 925 orig_line_no
04/02/02 19:03:08 714 order_date
04/02/02 19:04:58 9,422 wdc_absorb
04/02/02 19:06:29 996,616 wesnet_order_no
04/02/02 19:08:19 3,116,654 customer_po_no
04/02/02 19:10:07 2 customer_part_no
04/02/02 19:11:33 11 order_type
04/02/02 19:13:14 28 cm_code
04/02/02 19:14:34 1,438 contract_no
04/02/02 19:15:46 5,122 cust_manifest_no
04/02/02 19:16:59 3,236 qty_on_backorder
04/02/02 19:18:41 474,601 list_price
04/02/02 19:20:00 8 unit_of_measure_id
04/02/02 19:21:23 1,864 trade_discount_pct
04/02/02 19:22:32 193 cash_discount_pct
04/02/02 16:33:23 1,663,441 invoice_no
04/02/02 16:08:45 60 date_loaded
04/02/02 16:24:35 616 line_no
04/02/02 16:19:19 1,223,264 join_no
| |
