Re: Correlated subquery and join in the query

From: Steve Howard <stevedhoward_at_gmail.com>
Date: 18 Oct 2006 08:59:51 -0700
Message-ID: <1161187191.748731.151220@h48g2000cwc.googlegroups.com>

Sandesh wrote:
> sorry guys...am attaching the required info - (Oracle 9i)
>
> There is an index OPE_IE1_ENRICHED_TRIAL_BAL on all the GROUP BY
> columns.
>

One thing I can see is that the most of your work is here...

  18 17 VIEW OF 'VW_SQ_1' (Cost=10287 Card=366158 Bytes=12449372)
  19 18 SORT (GROUP BY) (Cost=10287 Card=366158 Bytes=8421634)

  20   19           PARTITION RANGE (ALL)
  21   20             TABLE ACCESS (FULL) OF 'A' (Cost=5199 Card=517825
Bytes=11909975)
  22   17       PARTITION RANGE (ALL)
  23   22         TABLE ACCESS (FULL) OF 'A' (Cost=5199 Card=517825

Bytes=239235150)

Have you tried adding the...

   AND OETB1.BUSINESS_DATE='1-JAN-06' ...to that subquery? Also, have you tried (just for testing) breaking it apart, and running it with and without certain subquery combinations? Lastly, a 10046 trace would be best so you could see exactly where it was spending it time.

HTH, Steve Received on Wed Oct 18 2006 - 10:59:51 CDT