Home » RDBMS Server » Performance Tuning » Delete statement and performance
Delete statement and performance [message #224726] Thu, 15 March 2007 04:31 Go to next message
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 #224749 is a reply to message #224726] Thu, 15 March 2007 05:51 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
The best way is to acquire those privs or run the jobs through the DBA guy Smile.
All the rest methods would involve many unnessary work.
Re: Delete statement and performance [message #224751 is a reply to message #224749] Thu, 15 March 2007 05:53 Go to previous messageGo to next message
subratd
Messages: 12
Registered: March 2007
Junior Member
Thanks Mahesh.Acquiring the privs is my last resort. Could you please tell me any round about way to do this effectively, even if its unnecesary.Please.
Re: Delete statement and performance [message #224898 is a reply to message #224751] Thu, 15 March 2007 18:59 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
DELETE FROM table;

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 Go to previous messageGo to next message
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.
Re: Delete statement and performance [message #224937 is a reply to message #224933] Fri, 16 March 2007 01:21 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I believe the process is more elaborately explained on AskTom.oracle.com. Do a search on "Rowid Range"

Ross Leishman
Previous Topic: Benchmarking UPDATE Statements
Next Topic: Oracle 8.1.7 statistics problem
Goto Forum:
  


Current Time: Thu Dec 08 18:06:43 CST 2016

Total time taken to generate the page: 0.10397 seconds