Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Tue, 03 Feb 2004 @ 12:20:57 GMT


     
  <Prev Next>  
<<First
<Prev
Next> Last>>  


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


     
  <Prev Next>  
<<First
<Prev
Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023