Hi All, Everytime I ask this question I get a different opinion. I have a table of about 4 million rows. The date column is part of a PK. I need to delete approx. 300K rows based on an input date parameter every month. What is the most efficient method to delete from my table where date <= parm date? Some say use an anonymous block with execute immediate, some say plain ole SQL command. I want it to be simple but efficent and be run from a Unix shell script. What's your suggestion? Thank you!
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
[b]Best Way To Delete By Date?[/b]
- Thread starter Msolar
- Start date
Consider bulk collect:
Do it in pure sql (feel free to wrap it in pl/sql but the task will be most performant (all things being equal)
The Bulk collect would still require a select into to populate the collection that you would need for the forall to succeed and so would be less performant than a simple, single sql delete.
The Bulk collect would still require a select into to populate the collection that you would need for the forall to succeed and so would be less performant than a simple, single sql delete.
- Thread starter
- #4
-- Or --
Partition the table by date (month?) and then every month drop the oldest partition.
![[3eyes]](/data/assets/smilies/3eyes.gif "[3eyes] [3eyes]")
----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb
Not too sure what you are trying to say by posting a question mark and then a link to documention dbtoo? Are you trying to say that a solution that can be achieved by SQL will actually be faster when executed by using collections and bulk processing in PL/SQL? Do you have any evidence to back this up or are you just going to post links to documentation that does not do anything to compare the SQL approach with the PL/SQL approach? At best it alludes to a comparison between row by row (slow by slow) cursor loop processing and bulk processing.
In order to perform your proscribed FORALL solution you require a collection to -(I would say to loop through but Forall is not a loop, so best to say) -process. That collection must of course be populated, so we require a bulk collect select into (I would point to the documentation on this but you seem familiar with it) we then must have PL/SQL process that collection, gather all of the delete statements together and pass all of them to the SQL engine in a single context switch. Alternatively, we can have SQL delete the rows. SQL will beat a PL/SQL solution pretty much every time performance wise (exceptions not included)
Finally, have a look at one of Tom Kyte's most popular mantras here
In order to perform your proscribed FORALL solution you require a collection to -(I would say to loop through but Forall is not a loop, so best to say) -process. That collection must of course be populated, so we require a bulk collect select into (I would point to the documentation on this but you seem familiar with it) we then must have PL/SQL process that collection, gather all of the delete statements together and pass all of them to the SQL engine in a single context switch. Alternatively, we can have SQL delete the rows. SQL will beat a PL/SQL solution pretty much every time performance wise (exceptions not included)
Finally, have a look at one of Tom Kyte's most popular mantras here
Of course I'm familiar with Tom's montra. He also offers great advice on many things, including bulk collect:
(sorry, don't have access to tiny url from work.)
It wasn't 'my' proscribed solution, as it was a link to oracle propaganda. Just offering up ideas. I guess I probably made the inappropriate assumption that the poster had already tried slow-by-slow and was not familiar with alternatives.
But thanks for smacking me with the newspaper.
(sorry, don't have access to tiny url from work.)
It wasn't 'my' proscribed solution, as it was a link to oracle propaganda. Just offering up ideas. I guess I probably made the inappropriate assumption that the poster had already tried slow-by-slow and was not familiar with alternatives.
But thanks for smacking me with the newspaper.
By far the largest overhead in any massive delete operation tends to be updating the table's indexes. You will buy a lot more performance for your efforts if you can drop the indexes before the delete and recreate them afterward. Unfortunately that's not always possible.
The most efficient way to do it, in terms of speed, is probably the simple SQL statement. Wrapping it in a PL/SQL EXECUTE IMMEDIATE won't make much difference one way or the other, but may be more convenient (for example, you could use DBMS_JOB to schedule the running of a PL/SQL procedure).
However, the downside of a single statement is that it's an all-or-nothing affair. If the delete fails, due to lack af rollback space for example, the whole transaction rolls back and you're back to square one. You also have no way to monitor progress - the SQL runs for however long, then (hopefully!) it's done.
An iterative approach, fetching the rows that need to be deleted and then deleting them one-by-one in a loop will be a little bit slower, but does offer you more control.
-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd
However, the downside of a single statement is that it's an all-or-nothing affair. If the delete fails, due to lack af rollback space for example, the whole transaction rolls back and you're back to square one. You also have no way to monitor progress - the SQL runs for however long, then (hopefully!) it's done.
An iterative approach, fetching the rows that need to be deleted and then deleting them one-by-one in a loop will be a little bit slower, but does offer you more control.
-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd
- Status
Similar threads
