Home » SQL & PL/SQL » SQL & PL/SQL » Fast delete
Fast delete [message #167222] Tue, 11 April 2006 22:54 Go to next message
birenp
Messages: 2
Registered: April 2006
Junior Member
How to do conditional delete from huge table having an index in shortest possible time ? Nobody will be using a table.
Re: Fast delete [message #167249 is a reply to message #167222] Wed, 12 April 2006 02:12 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Option one:
- Drop the indexes
- Delete
- Recreate the indexes

Option two:
- Create table2 as select <the records you want from your table>(CTAS method)
- Drop your table
- rename table2 to your table
- create constraints/indexes on your table

MHE

Re: Fast delete [message #167250 is a reply to message #167222] Wed, 12 April 2006 02:14 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
What proportion of the table will be deleted?
Is the table partitioned?
Are your rollback segments big enough to perform the DELETE in a single statement?

If you want to delete <10% of the table, a simple DELETE is going to be the fastest. After a big delete, it is often worthwhile rebuilding indexes, so best to drop/disable them first.
If deleting in one statement will blow your rollback segments, do the delete one partition at a time (if its a partitioned table), or use ROWID RANGE otherwise.

If you want to delete >10% of the table, it will be quicker to
CREATE TABLE new_table AS SELECT .... FROM old_table...
then drop the old table, rename, and build indexes.

Ross Leishman
Re: Fast delete [message #167422 is a reply to message #167250] Wed, 12 April 2006 23:30 Go to previous messageGo to next message
birenp
Messages: 2
Registered: April 2006
Junior Member
- out of 40 million rows, Almost 80-85% of rows will be deleted(purged). Delete is conditional like older than some date. Index is present on the requried columns in where clause.
- Table is not partitioned.
- yes, we have enough rollback segments to handle the large deletes
- Considering this, whehter ROWID Range will be faste or create table as select from.. will be faster ?
Re: Fast delete [message #167436 is a reply to message #167222] Thu, 13 April 2006 00:42 Go to previous messageGo to next message
chetwyn
Messages: 73
Registered: December 2005
Member
Since the data is quite large, I suggest you take Maaher method.

- Drop the indexes
- Delete
- Recreate the indexes

If your using CBO, you will need to re-sequence the index anyways(well you should)
i.e.
If the clustering_factor is closer to the row_nums than the block size is to the clustering_factor, this means the table sequence isn't inline with the indexes which is a performance killer, and this will happen if you delete such as mass of data.
Re: Fast delete [message #167453 is a reply to message #167436] Thu, 13 April 2006 02:33 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
For 80-85%, DEFINITELY do it with CTAS (CREATE TABLE AS SELECT)

Ross Leishman

P.S. Apols to Maarten for reiterating hir reply - we were posting at the same time. Just too slow.
Re: Fast delete [message #186148 is a reply to message #167222] Sun, 06 August 2006 07:51 Go to previous messageGo to next message
nmacdannald
Messages: 460
Registered: July 2005
Location: Stockton, California - US...
Senior Member
Have your tried using 'truncate table'?
Re: Fast delete [message #186159 is a reply to message #186148] Sun, 06 August 2006 11:08 Go to previous messageGo to next message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
OP said: 80-85% of rows will be deleted.

How could TRUNCATE TABLE help here? It would remove ALL records from the table.
Re: Fast delete [message #186164 is a reply to message #167222] Sun, 06 August 2006 11:40 Go to previous messageGo to next message
nmacdannald
Messages: 460
Registered: July 2005
Location: Stockton, California - US...
Senior Member
You are correct, I just did not read the post well, Sorry.
I still have to say I have had good luck trusting Oracle's query optimizer whent the statistics are fresh.

Re: Fast delete [message #186183 is a reply to message #186164] Sun, 06 August 2006 20:37 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
It's been 4 months; I think the DELETE would have finished by now.
Re: Fast delete [message #186195 is a reply to message #186183] Sun, 06 August 2006 23:11 Go to previous message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Laughing
Previous Topic: Package Problems
Next Topic: Problems with Oracle9i...!
Goto Forum:
  


Current Time: Tue Dec 06 14:10:05 CST 2016

Total time taken to generate the page: 0.09061 seconds