Performance tuning in delete operation [message #659830] |
Tue, 31 January 2017 09:21 |
|
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
|
|
|
|
|
|
|
|