Re: Long running sql ?

That does work better, however it gives exactly the same Explain Plan ?

<kennethkoenraadt_at_no-spam.hotmail.com> wrote in message news:3d9c66c9.1703910_at_news.mobilixnet.dk...
> Hi,
>
> SELECT COUNT(1)
> FROM result_master a
> WHERE ROWID <> (SELECT MAX(ROWID)
> FROM result_master
> WHERE
> labno = a.labno
> AND testcode = a.testcode
> AND batchid =a.batchid
> AND testseq = a.testseq)
>
> would surely be much more efficient.
>
> Contatenation should only be necessary if i.e. there is one row with
> labno= 'abc' and testcode = 'def' AND another row has
> labno ='ab' and testcode = 'cdef' and these 2 rows are then to be
> considered having the same key. Is that really the case ?
>
>
> - Kenneth Koenraadt
>
> On Thu, 3 Oct 2002 11:39:34 -0400, "bgt0990" <btighe_at_neometrics.com>
> wrote:
>
> >I want to find duplicate rows in this table and have used variations of
this
> >sql statement often with great success. However when I run it against
this
> >table of 190,000 records it maxes out my CPU and DBA_Studio tells me it
will
> >take 23 hours to run. ANY ideas on how to improve this would be
> >appreciated. I think it maybe the concatinations but that is the only
way
> >to make a unique key for this table.
> >
> >Oracle 8.1.7.4.1 WIN 2k
> >
> >SELECT COUNT(1)
> > FROM result_master a
> > WHERE ROWID <> (SELECT MAX(ROWID)
> > FROM result_master
> > WHERE labno || batchid || testcode || testseq =
> >a.labno
> > || a.batchid || a.testcode || a.testseq)
> >
> >
> >Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
> >
> >SELECT STATEMENT Hint=CHOOSE 1 261
> > SORT AGGREGATE 1 32
> > FILTER
> > INDEX FAST FULL SCAN RESULT_M01 9 K 284 K 261
> > SORT AGGREGATE 1 32
> > INDEX FAST FULL SCAN RESULT_M01 1 K 56 K 261
> >
> >
> >
> >
> >
> >-----------== Posted via Newsfeed.Com - Uncensored Usenet News
==----------
> > http://www.newsfeed.com The #1 Newsgroup Service in the World!
> >-----= Over 100,000 Newsgroups - Unlimited Fast Downloads - 19 Servers
=-----
>

-----------== Posted via Newsfeed.Com - Uncensored Usenet News ==----------

   http://www.newsfeed.com The #1 Newsgroup Service in the World! -----= Over 100,000 Newsgroups - Unlimited Fast Downloads - 19 Servers =----- Received on Thu Oct 03 2002 - 11:58:59 CDT