Home » SQL & PL/SQL » SQL & PL/SQL » Deleting large number of rows Quickly in Oracle Db (Oracle 10g)
Deleting large number of rows Quickly in Oracle Db [message #346952] Wed, 10 September 2008 03:36 Go to next message
sudheer0886
Messages: 7
Registered: September 2008
Junior Member
Hi,
I have one issue in the project like I need to delete a large number of (2 million) tuples from a table of 5 million based on some criteria.The criteria(condition) is fetched from one base table,based on the fetched data tuple is deleted.similarly I have to delete from 30 similar tables.

Like "Delete from employees where id IN(select id from empinfo where empinid=100)"

I tried some options
-Writing all the queries in a file and running the script
-Using a cursor (creating a index table) and deleting particular tuple from the table


Both giving me a large response time.
Can I use NOLOGGING option with DELETE to achieve my response time..??
Main concern here is time. I need to delete in very quick time.
So I need a optimised and efficient solution for the issue.

Thanks In Anticipation.
Re: Deleting large number of rows Quickly in Oracle Db [message #346960 is a reply to message #346952] Wed, 10 September 2008 04:18 Go to previous messageGo to next message
dwarak.k
Messages: 61
Registered: June 2008
Location: Hyderabad
Member
Ask Tom site has explanation for problems like this

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2345591157689

Regards,
Dwarak
Re: Deleting large number of rows Quickly in Oracle Db [message #346961 is a reply to message #346952] Wed, 10 September 2008 04:18 Go to previous messageGo to next message
Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome
Senior Member
During this large DML the index management may heavily degrade your performances.

See what happens if you alter index to unusable state before deleting and then re-alter the index to rebuild it.

Bye Alessandro
Re: Deleting large number of rows Quickly in Oracle Db [message #346986 is a reply to message #346961] Wed, 10 September 2008 05:47 Go to previous messageGo to next message
sudheer0886
Messages: 7
Registered: September 2008
Junior Member
Could you please elaborate on it.
How should I alter my session to enable faster deletes?
Will there be any threat if I alter session and delete the tuples?
Re: Deleting large number of rows Quickly in Oracle Db [message #346989 is a reply to message #346986] Wed, 10 September 2008 06:03 Go to previous message
Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome
Senior Member
It's an alter index and it's not an alter session.

You should try with.
alter index <index_name> unusable;
<your delete>
alter index <index_name> rebuild;


But see some documentation about it.

Bye Alessandro
Previous Topic: ORA-22905 Error in a PIPELINED function
Next Topic: Script to categorize the DJ aging
Goto Forum:
  


Current Time: Thu Feb 13 11:10:03 CST 2025