Re: Nested query taking long execution time!!!

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Wed, 14 Feb 2007 09:22:58 -0000
Message-ID: <T_KdncjIH5hySU_YnZ2dneKdnZypnZ2d@bt.com>

<qazmlp1209_at_rediffmail.com> wrote in message news:1171436873.013822.62050_at_m58g2000cwm.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?
>

Your first step should be to check the
execution plan for this query, and decide if it looks sensible - based on your understanding of the data,

It will probably be something like:

|   0 | SELECT STATEMENT     |      |     1 |   220 | 40485 |
|   1 |  SORT GROUP BY       |      |     1 |   220 |       |
|*  2 |   FILTER             |      |       |       |       |
|   3 |    TABLE ACCESS FULL | T1   |  3000 |   644K|    15 |
|   4 |    SORT AGGREGATE    |      |     1 |   216 |       |
|*  5 |     TABLE ACCESS FULL| T2   |    30 |  6480 |    15 |
-------------------------------------------------------------

Predicate Information (identified by operation id):

2 - filter( (SELECT COUNT(*) FROM "T2" "T2" WHERE

SUBSTR("T2"."V1",1,10)=SUBSTR(:B1,1,10))>1) 5 - filter(SUBSTR("T2"."V1",1,10)=SUBSTR(:B1,1,10))

If this is the case, you are probably running a lot of tablescans on T2 to get your result.

You will then need to decide whether is makes sense to create a function-based index on T2 to avoid the tablescans and find the related row efficiently; or whether to unnest the subquery and turn it into an inline view so that you can do a hash join, (or nested loop - aided by a function-based index on t1) into t1.

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Received on Wed Feb 14 2007 - 03:22:58 CST