Home » SQL & PL/SQL » SQL & PL/SQL » Effective delete Syntax (oracle 10g)
Effective delete Syntax [message #393336] Sat, 21 March 2009 14:50 Go to next message
Messages: 58
Registered: March 2009
Hi Friends,
I want to delete one table , when I write the following syntax
its taking lots of time. Because the table contains 100000 records.
delete from tab_nm;

I cant truncate table because foreing key is there.

So what is the best way to tune it.

Thanks in advance !!
Re: Effective delete Syntax [message #393338 is a reply to message #393336] Sat, 21 March 2009 15:18 Go to previous messageGo to next message
Messages: 20847
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Disable the constraint first, truncate it next and - finally - enable the constraint?
Re: Effective delete Syntax [message #393355 is a reply to message #393336] Sat, 21 March 2009 21:50 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
If you can afford truncate, you can also afford a drop.
You can DROP the TABLE with CASCADE CONSTRAINTS clause and even PURGE them instantly.

I would take code backup ( just the DDL of the table,its constraints and associated indexes) before doing this. Could
be handy to recreate the table/indexes/constraints.
Main drawbacks are the need to re-grant the privs on table and recompile the objects(not so good)

[Updated on: Sat, 21 March 2009 22:15]

Report message to a moderator

Re: Effective delete Syntax [message #393557 is a reply to message #393336] Mon, 23 March 2009 06:27 Go to previous message
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Check that the foreign key constraints have indexes - otherwise the delete will need to perform a FTS of the foreign table for each row deleted.
Previous Topic: ora-01001 invalid cursor
Next Topic: dbms_job.submit is not taking variable in INTERVAL column
Goto Forum:

Current Time: Tue Oct 25 09:24:38 CDT 2016

Total time taken to generate the page: 0.12681 seconds