Home » SQL & PL/SQL » SQL & PL/SQL » Performance tuning in delete operation (Oracle 11g)
Performance tuning in delete operation [message #659830] Tue, 31 January 2017 09:21 Go to next message
raj85844
Messages: 26
Registered: November 2016
Location: chennai
Junior Member
Hi Team,
Here I would like too know which approach is better when compared to performance

Approach 1:
Currently for deleting 5lak records from a single table holding 90 million records I am using plsql procedure with cursor and deleting the records by commiting 10k rows in loop takes 7 min to complete

Approach 2:
Without using the cursor, deleting the records directly in the delete statement with condition using (in operator) select statement

Out of the two approaches which is better in performance

Note:
In production environment
It is a single table
Conditioned column in delete statement is indexed
Table is online phasing for dml operation
Continuously the table is been loaded(data been inserted) from other system

Can u help me to go with the right approach

Thanks and regards
Raj

Re: Performance tuning in delete operation [message #659831 is a reply to message #659830] Tue, 31 January 2017 09:24 Go to previous messageGo to next message
John Watson
Messages: 7220
Registered: January 2010
Location: Global Village
Senior Member
I don't know which performs better. You do - you can run the code, and time it!

By the way, please user SI units, not "lak". Also note that tables store rows, not records.
Re: Performance tuning in delete operation [message #659832 is a reply to message #659830] Tue, 31 January 2017 09:24 Go to previous messageGo to next message
BlackSwan
Messages: 25797
Registered: January 2009
Location: SoCal
Senior Member
Approach 2 is better.
Re: Performance tuning in delete operation [message #659866 is a reply to message #659832] Wed, 01 February 2017 08:18 Go to previous messageGo to next message
Bill B
Messages: 1709
Registered: December 2004
Senior Member
Because of context switching a single delete statement will be much faster assuming that you have the redo space to store the deleted rows.
Re: Performance tuning in delete operation [message #659907 is a reply to message #659866] Fri, 03 February 2017 04:01 Go to previous messageGo to next message
saipradyumn
Messages: 318
Registered: October 2011
Location: Hyderabad
Senior Member

If we are going for second approach, its better to check all the data which is to be deleted is eligible for delete with out any constraint violation Other wise because of this constraint violation entire delete statement will not work fine.

But in the first approach we can skip those records and continue for the remaining

Re: Performance tuning in delete operation [message #659908 is a reply to message #659907] Fri, 03 February 2017 04:03 Go to previous message
John Watson
Messages: 7220
Registered: January 2010
Location: Global Village
Senior Member
Add a LOG ERRORS clause to your DELETE statement.

[Updated on: Fri, 03 February 2017 04:04]

Report message to a moderator

Previous Topic: order of operations in the below statements
Next Topic: Writing multiple Files using UTL_FILE
Goto Forum:
  


Current Time: Sat Jan 20 05:59:58 CST 2018

Total time taken to generate the page: 0.10415 seconds