Re: Nested query taking long execution time!!!

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..._at_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?
Received on Wed Feb 14 2007 - 01:54:16 CST