Re: query taking too much time to execute

From: Mark C. Stock <mcstockX_at_Xenquery>
Date: Wed, 26 Apr 2006 11:58:03 -0400
Message-ID: <gOGdnQ7qy88WBdLZRVn-sQ@comcast.com>

"gazzag" <gareth_at_jamms.org> wrote in message news:1146065350.738969.301760_at_y43g2000cwc.googlegroups.com...
: Kuldeep wrote:
: > delete from ASSORTMENT
: > where ASSORTM not in ( select min(ASSORTM)
: > from ASSORTMENT
: > group by ASSORTM2 )
: >
: > This query is teking too musc time to execute
: >
: > In ASSORTMENT there are 1,50,000 records
: >
: > somebdy has solution over it?
:
: Ed's quite right about knowing one's data. However, if I were to
: hazard a guess, why not the following?
:
: delete from ASSORTMENT
: where ASSORTM != ( select min(ASSORTM)
: from ASSORTMENT
: );
:
: MIN() will only return the one value...
:

totally different logic

OP's deletes all but one regord for each ASSORTM2

this suggestion selects all but one record

either way, lots of undo is generated -- depending on how many ASSORTM2 groups there are.

bottom line, the execution plan needs to be reviewed

++ mcs Received on Wed Apr 26 2006 - 10:58:03 CDT