Re: Long running sql ?

From: <kennethkoenraadt_at_no-spam.hotmail.com>
Date: Thu, 03 Oct 2002 15:56:19 GMT
Message-ID: <3d9c66c9.1703910@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 =-----
Received on Thu Oct 03 2002 - 10:56:19 CDT