Nested query taking long execution time!!!

From: <qazmlp1209_at_rediffmail.com>
Date: 13 Feb 2007 23:08:21 -0800
Message-ID: <1171436901.844753.94610@s48g2000cws.googlegroups.com>

select count(distinct t1.ColA)
from myTable1 t1
where
(

        select count( distinct t2.ColB )
        from myTable1 t2
        where SUBSTR(TO_CHAR(ID_TO_NUMBER(t1.ColA)),1,17) =

SUBSTR(TO_CHAR(ID_TO_NUMBER(t2.ColA)),1,17)
) > 1 ;

I need to find the count of the rows in the Table(myTable1) where the number of ColB values associated with the ColA value is more than 1. myTable contains ColA and ColB as the key columns and there are other columns also in it.

This query works fine when the myTable1 contains only lesser number of rows. But, when it contains 2-3 million rows, it takes hell a lot of time(not over even after 10 minutes). What could be the reason? Is there something wrong with the Table design? If not, how do I optimize the query? Received on Wed Feb 14 2007 - 01:08:21 CST