Re: Long running sql ?

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Thu, 3 Oct 2002 18:08:30 +0100
Message-ID: <anhtib$fai$1$830fa7b3@news.demon.co.uk>

How about doing something like:

SELECT
labno , batchid , testcode ,testseq, COUNT(*) FROM
result_master a
group by

labno , batchid , testcode ,testseq, having

count(*) > 1
;

There are alternatives depending on what you want to do with the result set, how many columns determine uniqueness, and so on - but this will tell you exactly which rows have how many duplicates.

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____USA__________November 7/9   (Detroit)
____USA__________November 19/21 (Dallas)
____England______November 12/14

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html





bgt0990 wrote in message <3d9c6091_1_at_corp-goliath.newsgroups.com>...


>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 - 12:08:30 CDT