Fast Oracle Table deletion without using TRUNCATE TABLE

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.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Just move the 0.5% record to another temp table.

Drop your existing table, Recreate it and then copy records into your table.
slightwv (䄆 Netminder) Commented:
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.
johnsoneSenior Oracle DBACommented:
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.
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

johnsoneSenior Oracle DBACommented:
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.
slightwv (䄆 Netminder) Commented:
I agree with johnsone.  

Sorry.  I took "You cannot use TRUNCATE" literally, not as I can't use it because it gets ALL data...
hc2342uhxx3vw36x96hqAuthor Commented:
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.
hc2342uhxx3vw36x96hqAuthor Commented:
Please suppose you can use only DELETE.  I would like to see your solutions (of course I have one!).
slightwv (䄆 Netminder) Commented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
johnsoneSenior Oracle DBACommented:
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.
Devinder Singh VirdiLead Oracle DBA TeamCommented:
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.
Devinder Singh VirdiLead Oracle DBA TeamCommented:
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.
Naveen KumarProduction Manager / Application Support ManagerCommented:
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.

hc2342uhxx3vw36x96hqAuthor Commented:
Thank you very much for your interesting remarks!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.