OraFAQ Forum: Performance Tuning » For Faster Delete statement

Home » RDBMS Server » Performance Tuning » For Faster Delete statement

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

For Faster Delete statement [message #171878]

Fri, 12 May 2006 00:37

bhadresh4u
Messages: 41
Registered: May 2005
Location: Japan

Member

Hi..,

I wants to tune my Delete statement. it's take too much time..
see the below statement and tell me how to tune it by using the optimizr Hint or any else suggesion..!


DELETE FROM tab1 t
WHERE (t.input_dt < v_start_dt OR t.input_dt > v_end_dt )
  AND no_col1 = 0 ;

Indexes : ( input_dt + No_col1 )

I used the /*+ NOLOGGING */ Hint to optimise it ... but it was not tuned..! so what to do to tune it...?

Thanks in advnce

Bhadresh

Report message to a moderator

Re: For Faster Delete statement [message #171893 is a reply to message #171878]

Fri, 12 May 2006 02:40

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

Post the EXPLAIN PLAN, the number of rows in the table, and the number of rows you expect to delete.

Ross Leishman

Report message to a moderator

Re: For Faster Delete statement [message #171981 is a reply to message #171893]

Fri, 12 May 2006 14:05

jrich
Messages: 35
Registered: February 2006

Member

Without more info it is difficult to give advice, but if you are deleting a large % of the table, a CTAS filtering on the rows you want to keep and then replacing the table is usually better.

JR

Report message to a moderator

Previous Topic:	Help interpreting a trace
Next Topic:	performance hit from 9i to 10g

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Nov 23 15:48:25 CST 2024