| Delete statement and performance [message #224726] |
Thu, 15 March 2007 04:31  |
subratd
Messages: 12
Registered: March 2007
|
Junior Member |
|
|
Hi,
I have a table which has indexes on few columns.I want to delete all the records from that table. How can i improve the performance of the delete statement. What is the best way to do this.
Further, i dont have the truncate and drop privilege with me.
[Updated on: Thu, 15 March 2007 04:57] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Delete statement and performance [message #224898 is a reply to message #224751] |
Thu, 15 March 2007 18:59  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
There are other ways to do this without DDL access (truncate, disabling indexes, triggers, RI) - all are slower still.
If your rollback segments (UNDO) are insufficient, you can use the Rowid Range technique; described along with the other DDL-based techniques here.
Ross Leishman
|
|
|
|
| Re: Delete statement and performance [message #224933 is a reply to message #224898] |
Fri, 16 March 2007 00:16 |
subratd
Messages: 12
Registered: March 2007
|
Junior Member |
|
|
Thanx relieshman.I didnt get one statemnet written in the link.
"Looking at the columns of dba_extents, we can reconstruct the rowids of the first and last row in every extent. Then it is simply a matter of:
UPDATE my_table
SET ....
WHERE rowid BETWEEN :low_rowid AND :high_rowid;"
Could you please explain me this.And your thought on how to implement this method of deleting all records from a table.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
.