?
Solved

Fast Oracle Table deletion without using TRUNCATE TABLE

Posted on 2010-03-24
13
Medium Priority
?
779 Views
Last Modified: 2013-12-18
Please suppose you have to delete 99,50 % of the records of an Oracle Table.

You cannot use TRUNCATE.

You have a particular where condition to use, you can't delete randomly of course!

Could you suggest me an efficient way to delete the records?

Please suppose you have a massive table, with million of records.
0
Comment
Question by:hc2342uhxx3vw36x96hq
  • 3
  • 3
  • 3
  • +3
13 Comments
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 28465958
Just move the 0.5% record to another temp table.

Drop your existing table, Recreate it and then copy records into your table.
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 28466084
What constraints do you have on the table?

for example, do you have foreign keys?  indexes?

quickest is create a new table with the records you want to keep, drop the old table, rename the temp table.

create table temp_tab as ( select * from oldtable where col1 = '1');
drop table oldtable;
rename temp_tab to oldtable;

This of course won't work if you have constraints and indexes.

You could always recreate the indexes and it still might be quicker.
0
 
LVL 35

Expert Comment

by:johnsone
ID: 28468303
In order to preserve grants, indexes, contrstraints, etc.  I would suggest creating a temporary table with the records to be kept, truncating the original table, then inserting the records from the temporary table back into the original table.  A slight variation on slightwv's solution:

create table temp_tab as ( select * from oldtable where col1 = '1');
truncate table oldtable;
insert into oldtable select * from temp_tab;

This should preserve everything on the old table.

Due to the amount of data that you are deleting, you would want to drop or truncate the original table to release the space in the table and the indexes.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
LVL 35

Expert Comment

by:johnsone
ID: 28468377
I just reread this and saw the "without using truncate".  Is that because you are trying to preserve some records and not delete all records, or there is some reason to not truncate?  If it is a permission issue, then some of these other methods may not work either.
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 28468445
I agree with johnsone.  

Sorry.  I took "You cannot use TRUNCATE" literally, not as I can't use it because it gets ALL data...
0
 
LVL 1

Author Comment

by:hc2342uhxx3vw36x96hq
ID: 28473597
In my system I am not allowed to create temporary tables.  Excuse me for having not specified it in the original question.  So I can't drop the table and substitute it with another one by renaming it.  There is also a FOREIGN KEY which refers to another table.
0
 
LVL 1

Author Comment

by:hc2342uhxx3vw36x96hq
ID: 28473634
Please suppose you can use only DELETE.  I would like to see your solutions (of course I have one!).
0
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 28473967
You might not have permissions to create a 'staging' table but can't you have the DBAs create one for you that you can use?


the only way to really speed up a straight delete is do it in small batches and commit at regular intervals.
0
 
LVL 35

Assisted Solution

by:johnsone
johnsone earned 500 total points
ID: 28477292
Agreed.  Without being able to create a temporary table, the best way I can think of is PL/SQL and commit in batches.  Given the restrictions, I cannot think of a better way.

Be aware that this is going to leave a lot of unused space out there.  I would have your DBAs do an ALTER TABLE MOVE to reclaim the space in the table and then rebuild the indexes once your deletes are complete.
0
 
LVL 15

Expert Comment

by:Devinder Singh Virdi
ID: 28479933
Adding to rrjegan17: comments

1. Find all constraints and indexes on this table.
2. Find all references using
select constraint_name from user_constraints where r_constraint_name='ORIGINAL_TABLE_PK';
3 Drop references
4 Rename original table to somethink like Table_name_ORG
5. create new table with required rows in same/diff tablespace with same/diff storage parameters etc.
6. create PK / indexes and all constraints
7. Create all from step 2.
0
 
LVL 15

Assisted Solution

by:Devinder Singh Virdi
Devinder Singh Virdi earned 500 total points
ID: 28480290
Sorry
here is my another view.

You can fast delete operation by doing it parallelly. Make small chunks ie 50K records delete parallely in one thread and run multiple threads. Here is the problem if your coding is not perfect, you may see blocking locks also.
0
 
LVL 28

Assisted Solution

by:Naveen Kumar
Naveen Kumar earned 500 total points
ID: 28525131
it should be a simple pl/sql block of code to delete records in batches ( batch size should be decided by you something like 5K or 10K or 50 K depending on how many fields are there in the table ).

To ensure deletes run faster, you can put the where clause accordingly which can use an index. But i doubt that an index usage for deleting 90% + records is a good idea as you will be going thru all most all the records.

Depending on the size of the table which holds this large data ( example, if you table size is more than few gigs then i would say you should get it truncated once in a while if frequent DML's happen to this table on a daily basis ), you should propose to your client/supervisor that a truncate is a must for this kind of table to reset the high water mark otherwise it is a highly fragmented object which could cause performance issues for inserts/updates/deletes.

Thanks
0
 
LVL 1

Author Closing Comment

by:hc2342uhxx3vw36x96hq
ID: 31706683
Thank you very much for your interesting remarks!
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

601 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question