Home » SQL & PL/SQL » SQL & PL/SQL » Optimize Delete statement (9.0.4.0)
Optimize Delete statement [message #322440] Fri, 23 May 2008 09:42 Go to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Hi All,
I have written following delete statement.But It's taking 10 mintutes.So please give me idea to decrease the execution time.
delete from
    duplicate
 WHERE ROWID  IN (
          SELECT ROWID
            FROM (SELECT ROWID,
                         ROW_NUMBER () OVER (PARTITION BY item ORDER BY item)
                                                                         stk,
                         cycle_count cycle_cnt
                    FROM duplicate
                   WHERE cycle_count =4080)
           WHERE stk > 1)
   AND cycle_count =1250;


Thank you.

Re: Optimize Delete statement [message #322448 is a reply to message #322440] Fri, 23 May 2008 10:19 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
I don't understand this.

Quote:

PARTITION BY item ORDER BY item
...
WHERE cycle_count =4080
...
AND cycle_count =1250



Regards

Raj

[EDIT] : I added another very interesting condition paritioning and ordering on the same column. I have not seen this before.

[Updated on: Fri, 23 May 2008 10:26]

Report message to a moderator

Re: Optimize Delete statement [message #322451 is a reply to message #322448] Fri, 23 May 2008 10:25 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Hi Raj,

By mistake I have posted Both are 1250's...
Re: Optimize Delete statement [message #322452 is a reply to message #322440] Fri, 23 May 2008 10:29 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>My mistake I have posted Both are 1250's...

It appears you would benefit from remedial training on CUT & PASTE
Re: Optimize Delete statement [message #322453 is a reply to message #322440] Fri, 23 May 2008 10:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
For all performances questions there are a minimum information to give and as you posted now almost 250 times I think you know them, so post them.

Regards
Michel
Re: Optimize Delete statement [message #322495 is a reply to message #322440] Fri, 23 May 2008 13:53 Go to previous message
drewsmith70
Messages: 22
Registered: April 2008
Location: New Hampshire
Junior Member
Does this work? The outer "cycle_count" looks like it's out of scope.
Previous Topic: Update Nested Table
Next Topic: Oracle query
Goto Forum:
  


Current Time: Thu Dec 08 08:14:52 CST 2016

Total time taken to generate the page: 0.13496 seconds