dstinct val

[gsprince]

What is the best way to achieve the following?.


I want to, select the value of COL1 from TAB1, if there is duplicate value for COL1, COL2, COL3 combined.


I have the following qry, but believe there would be some better ones.

Select COL1 from (
Select COL1, COL2, COL3, COUNT(*)
From TAB1
Group by COL1, COL2, COL3
Having COUNT(*) > 1 )

[jmodic]

Your query requires a single full table scan and a sort on group by. If the data distribution is such that sorting requires a lot of resources then you should try the following one (and a composite index on COL1, COL2 and COL3 will be very helpful):

SELECT col1 FROM tab1 x WHERE EXISTS
(SELECT NULL FROM tab1
WHERE col1=x.col1 AND col2=x.col2 AND col3=x.col3 AND rowid != x.rowid);

Again, this might or might not be a better one, try it yourself on your actual data.

HTH,