Delete Query in PPI vs Alter Statement Drop Range faster and better .

Database

Delete Query in PPI vs Alter Statement Drop Range faster and better .

Hi ,

Table A has partition defined on for each month say  from 2012 to 2016 .

Now I have to perform a Delete on Table A for any date between 2012 to 2016 say 12-12-2014 

So which option should I choose while deleting from above mentioned table (which would get the job done faster in a better way ).

Query : Delete from Table A where dateColumn(on which partion has been made )  < 12-12-2014 .

Or 

Should I go with Alter DROP partion with delete mentioned on the link below 

http://www.info.teradata.com/htmlpubs/DB_TTU_14_00/index.html#page/SQL_Reference/B035_1184_111A/Alte...

Please Explain in detail why ? 

4 REPLIES
Enthusiast

Re: Delete Query in PPI vs Alter Statement Drop Range faster and better .

Jithin,

You don't have the option to drop partitions from a non empty table that are not either at the beginning or the end of the partitioning range so your example would fail unless the table is empty.

Deleting a data range will use the Transient journal, altering to drop partitions with delete will not and should be faster.

RGlass
Senior Apprentice

Re: Delete Query in PPI vs Alter Statement Drop Range faster and better .

Hi Jithin,

in your example can't use DROP because you can only drop full partitions an not 12 days out if a months.

Otherwise I prefer DELETE over DROP PARTITION: an empty partition has no overhead and if there's a NO RANGE you must DELETE before DROP anyway.

DELETE will not use the TJ if it's a full partition (similar to a FastPath Delete ALL), only partial partition deletes are journaled.

Re: Delete Query in PPI vs Alter Statement Drop Range faster and better .

Thanks RGlass and Dieter . 

Just to clarify more .

1. The tables are populated with Count more than 50 k or in few cases 10 times more .

2. I can also  Identify which ever partition is less than the Date eg : 12-12-2014   and drop them. or just delete the data which is less than the date .

But I need to choose a solution that is the best (in terms of speed and performance ) :) 

Thanks 

Jithin James

Senior Apprentice

Re: Delete Query in PPI vs Alter Statement Drop Range faster and better .

Hi Jithin,

as I wrote, I prefer DELETE over DROP.

But for a table with only 500k rows I would hardly consider adding partitioning, any way to delete should be fast enough :-) 

You might simply run some tests and check DBQL for actual resource usage.