All Forums Database
Chiraggorsia 7 posts Joined 06/15
12 Apr 2016
Avoid skewing during delete Teradata

I am trying to delete the non PI column from the pi column of the same table.
Query as below.

delete From A tbl1  

Where  Exists(

                Select 1 

                From A tbl2

                Where tbl1.non_pi = tbl2.PI

and tbl2.col1 = 'Email_TEC' and  tbl2.col2 between  '2015-01-01' and  '2015-12-31'

and tbl1.PI is not null;


                and   tbl1.col2 between  '2015-01-01' and  '2015-12-31' ;


But this results in distribution of the non-pi column and the non pi column has a lot of null values with is resulting in the spool out of the query.


explain plan as below.


We execute the following steps in parallel.

       1) We do an all-AMPs RETRIEVE step from 365 partitions of

          L2_OfferPerformancedev_S.tbl1 with a condition of (

          "(tbl1.col2<= DATE

          '2015-12-31') AND (tbl1.col2

          >= DATE '2015-01-01')") into Spool 2 (all_amps) fanned out

          into 21 hash join partitions, which is redistributed by the

          hash code of (tbl1.non_pi)

          to all AMPs.  The size of Spool 2 is estimated with no

          confidence to be 172,847,118 rows (127,906,867,320 bytes).

          The estimated time for this step is 1 minute and 19 seconds.



Even thought i have added the condition of only not null values it does not considers it.


can you please helo me optimize this query.




ToddAWalter 316 posts Joined 10/11
12 Apr 2016

the condition above says "and tbl1.PI is not null" rather than "tbl1.non-PI is not null"

Chiraggorsia 7 posts Joined 06/15
02 May 2016

Thanks for the Responce Todd.
Its a TYPO here the condition is tbl1.non-PI is not null.
Can you give me solution to solve the issue.

ToddAWalter 316 posts Joined 10/11
02 May 2016

It would help if the whole Explain plan was available. The step above is spooling the tbl1 copy of the table and qualifying on the date range. There is no qualification on "tbl1.non-PI is not null" in the outer query, only inside the subquery.

You must sign in to leave a comment.