Re: Correlated subquery and join in the query

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.
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=21938 Card=1 Bytes=4
> 96)
>
> 1 0 PARTITION RANGE (SINGLE)
> 2 1 TABLE ACCESS (BY LOCAL INDEX ROWID) OF
> 'OPE_TB_POSTING_KEY' (Cost=1 Card=1 Bytes=103)
>
> 3 2 INDEX (RANGE SCAN) OF 'B' (UNIQUE) (Cost=1
> Card=1)
>
> 4 0 PARTITION RANGE (SINGLE)
> 5 4 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'OPE_TB_POSTING_K
> EY' (Cost=1 Card=1 Bytes=103)
>
> 6 5 INDEX (RANGE SCAN) OF 'B' (UNIQUE) (Cost=1
> Card=1)
>
> 7 0 PARTITION RANGE (SINGLE)
> 8 7 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'OPE_TB_POSTING_K
> EY' (Cost=1 Card=1 Bytes=103)
>
> 9 8 INDEX (RANGE SCAN) OF 'B' (UNIQUE) (Cost=1
> Card=1)
>
> 10 0 PARTITION RANGE (SINGLE)
> 11 10 TABLE ACCESS (BY LOCAL INDEX ROWID) OF
> 'OPE_TB_POSTING_KEY' (Cost=1 Card=1 Bytes=38)
>
> 12 11 INDEX (RANGE SCAN) OF 'B' (UNIQUE) (Cost=1
> Card=1)
>
> 13 0 PARTITION RANGE (SINGLE)
> 14 13 TABLE ACCESS (BY LOCAL INDEX ROWID) OF
> 'OPE_TB_POSTING_KEY' (Cost=1 Card=1 Bytes=44)
>
> 15 14 INDEX (RANGE SCAN) OF 'B' (UNIQUE) (Cost=1
> Card=1)
>
> 16 0 FILTER
> 17 16 HASH JOIN (Cost=21926 Card=1 Bytes=496)
> 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)
>
> 24 16 SORT (GROUP BY) (Cost=12 Card=1 Bytes=125)
> 25 24 PARTITION RANGE (SINGLE)
> 26 25 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'A' (Cost=10
> Card=1 Bytes=125)
>
> 27 26 INDEX (RANGE SCAN) OF
> 'OPE_IE1_ENRICHED_TRIAL_BAL'(NON-UNIQUE) (Cost=9 Card=1)

I am assuming this was generated with some neolithic script loaded with RPAD and such. Could you please rerun a proper EXPLAIN PLAN using the DBMS_XPLAN built-in package. Run EXPLAIN PLAN <your statement> followed by the script $ORACLE_HOME/rdbms/admin/utlxplp.sql. It may help see what is going on.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org