Avoid skewing during delete Teradata

Database
Enthusiast

Avoid skewing during delete Teradata

HI,

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.

Regards,

Chirag

3 REPLIES
Teradata Employee

Re: Avoid skewing during delete Teradata

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

Enthusiast

Re: Avoid skewing during delete Teradata

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.

Regards,

Chirag

Teradata Employee

Re: Avoid skewing during delete Teradata

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.