All Forums Database
Jithin James 4 posts Joined 03/14
02 Jan 2015
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 .
Should I go with Alter DROP partion with delete mentioned on the link below index.html#page/SQL_Reference/B035_1184_111A/ Alter_Function-Details.008.092.html
Please Explain in detail why ? 

Glass 225 posts Joined 04/10
04 Jan 2015


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.


dnoeth 4628 posts Joined 11/04
04 Jan 2015

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.


Jithin James 4 posts Joined 03/14
04 Jan 2015

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 ) :) 
Jithin James

dnoeth 4628 posts Joined 11/04
05 Jan 2015

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.


You must sign in to leave a comment.