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
Please Explain in detail why ?
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.
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 ) :)
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.