Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

Deleting large number of rows in Oracle quickly

Oracle Database Tips by Donald BurlesonSeptember 19, 2015

Question: I need to delete a large number of (2 million) tuples from a table of 5 million based on specified criteria.  The criteria (condition) is fetched from one base table.  I'll have to carry out this process on roughly 30 tables.  Is there a quick way I can accomplish this?

Answer: You quite simply have too much data for it to be deleted quickly. No matter what method you use, it will take a significant amount of time. If you are confident of your code, you can also disable constraints while pruning your data, which will save some time.

Also see: The fastest way to delete from large tables

It might be easiest to create a new table with the rows you want to keep - then drop the old one but whatever you do will take a large amount of time

Another option you can try would be to create a new table using ctas and the select statement is based on the join condition between the two base tables. When done, index it as needed, then do a rename of the original followed by a rename of the second (this part takes < 1 second, but no one can have a lock on the table).

Tuning any DML statement can be difficult, but there are some things that you can do to speed-up Oracle delete operations.  For complete details, see the book Advanced Oracle SQL Tuning: The Definitive Reference.

Some helpful hints include:

  • Use partitioning:  The fastest way to do a mass delete is to drop an Oracle partition.
  • Tune the delete subquery:  Many Oracle deletes use a where clause subquery and optimizing the subquery will improve the SQL delete speed.
  • Use bulk deletes:  Oracle PL/SQL has a bulk delete operator that often is faster than a standard SQL delete.
  • Drop indexes & constraints:  If you are tuning a delete in a nighttime batch job, consider dropping the indexes and rebuilding them after the delete job as completed.
  • Small pctused:  For tuning mass deletes you can reduce freelist overhead by setting Oracle to only re-add a block to the freelists when the block is dead empty by setting a low value for pctused.
  • Parallelize the delete job:  You can run massive delete in parallel with the parallel hint.  If you have 36 processors, the full-scan can run 35 times faster (cpu_count-1)
  • Consider NOARCHIVELOG:  Take a full backup first and bounce the database into NOLOGGING mode for the delete and bounce it again after, into ARCHIVELOG mode.
  • Use CTAS:  Another option you can try would be to create a new table using CTAS where the select statement filters out the rows that you want to delete. Then do a rename of the original followed by a rename of the new table and transfer constraints and indexes.

Lastly, resist the temptation to do "soft" deletes, a brain-dead approach that can be fatal.


 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2016

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.