Long running sql ?

From: bgt0990 <btighe_at_neometrics.com>
Date: Thu, 3 Oct 2002 11:39:34 -0400
Message-ID: <3d9c6091_1@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 - 10:39:34 CDT