All Forums Database
beautyofwisdom 2 posts Joined 02/13
02 Feb 2014
Optimizing Teradata SQL query

I have two tables , table A size 10GB (base table) and table B size 4GB, joined using left outer join, stats are collected on the joining columns, query is getting a spool space of 1.79 TB on Teradata system, any suggestions or points which need to be checked or done for optimizing the query to run in shorter time and taking less spool. Explain plan is duplicating data on all amps.

Hilal
dnoeth 4628 posts Joined 11/04
02 Feb 2014

Hi Hilal,
could you share the query? Might be a bad join condition.
 

Dieter

beautyofwisdom 2 posts Joined 02/13
02 Feb 2014

  SELECT B.ACCT_KEY
            ,B.SBSCRP_AGE_WITH_TP
            ,B.MSISDN
            ,B.SBSCRP_KEY
            ,B.ACCT_ID
            ,B.SBSCRP_UNBILLED_AMT

                FROM DB_TMP.TABLE_A A

                LEFT JOIN DB_TMP.TABLE_B B
                ON A.SBSCRP_KEY = B.SBSCRP_KEY;
Table B has PRIMARY INDEX ( SBSCRP_KEY ) while Table A has PRIMARY INDEX ( SBSCRP_KEY )
Partition by ( RANGE_N(SBSCRP_DATE  BETWEEN DATE '2000-01-01' AND DATE '2015-12-31' EACH INTERVAL '1' DAY ));
Table A has below data
    SBSCRP_DATE    total
1    1/25/2014    76831239
2    1/26/2014    76821482
3    1/27/2014    76291611
4    1/28/2014    76846372
5    1/29/2014    70884575
6    1/30/2014    76400378
7    1/31/2014    71271684
 
 

 

Hilal

dnoeth 4628 posts Joined 11/04
02 Feb 2014

Hi Hilal,
525 mio rows can't be stored in 10GB :-)
When both tables share the same PI there should be no duplication. I would expect the big table's spool is "built locally" and then sorted to prepare for the join (worst case), or no table is spooled and there's a direct "sliding window join".
The query you posted is probably not the actual query, you're not selecting any column from the outer table...
Can you show the stats for both tables and the actual explain, too?
 

Dieter

Adeel Chaudhry 773 posts Joined 04/08
02 Feb 2014

As Dieter suggested .... orignal query with explain is required to further comment on the issue.

-- If you are stuck at something .... consider it an opportunity to think anew.

You must sign in to leave a comment.