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?

Hi.

Do you really need all this type convertions (i.e.
SUBSTR(TO_CHAR(ID_TO_NUMBER...) ? What about "CoIA" field type? Why
not to compare "t1.CoIA = t2.CoIA" ?
Is the "CoIA" column a primary key of myTable1 ?

W
On 14 ÆÅ×, 09:08, qazmlp1...@rediffmail.com wrote:
> 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?