Re: Long running sql ?

This is very nifty, I'm still trying to figure out how it's counting dups

bgt

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:anhtib$fai$1$830fa7b3_at_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
> =-----
>
>

-----------== 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:34:10 CDT