Re: subselect results in poor performance

Benny wrote:
> Hi all,
> Running query with partition pruning information in subselect but
> optimizer does not choose for partition pruning making query slow.
> If subselect is replaced with literal/string (which is result of
> subselect), query is fast.
> Have tried many hints...without success. Anyone else an idea ?
> note : query below is part of big sql.
>
> 1. no partition pruning on MV_RRP00010 => SLOW
> makes use of subselect (string in comment)
>
> select VW_RRP00010.*,RRP0.TB_RRP00115.*
> FROM RRP0.MV_RRP00010 VW_RRP00010,rrp0.TB_RRP00115
> where VW_RRP00010.LOAD_DT = TB_RRP00115.LOAD_DT
> AND VW_RRP00010.LOAD_TYPE_CD = TB_RRP00115.LOAD_TYPE_CD
> AND VW_RRP00010.FUSE_CTY_ID = TB_RRP00115.FUSE_CTY_ID
> AND (VW_RRP00010.ISO2_CTY_CD = TB_RRP00115.ISO2_CTY_CD OR ' ' =
> TB_RRP00115.ISO2_CTY_CD)
> and tb_rrp00115.load_dt = (SELECT TB_RRP00104.LOAD_DT COL1 FROM
> RRP0.TB_RRP00104 TB_RRP00104 WHERE '1' = TB_RRP00104.VSN_CD AND 'D' =
> TB_RRP00104.LOAD_TYPE_CD)
> /* and tb_rrp00115.load_dt = '14/01/2007' */
>
> Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
>
> SELECT STATEMENT Optimizer Mode=CHOOSE 64 K 2891
> HASH JOIN 64 K 10 M 2891
> PARTITION RANGE SINGLE KEY KEY
> TABLE ACCESS FULL RRP0.TB_RRP00115 48 K 6 M 1290 KEY KEY
> PARTITION RANGE ALL 1 35
> TABLE ACCESS FULL RRP0.TB_RRP00104 1 12 4 1 35
> PARTITION RANGE ALL 1 35
> TABLE ACCESS FULL RRP0.MV_RRP00010 1 M 33 M 754 1 35
>
>
> 2. partition pruning on MV_RRP00010 => FAST
> subselect in comment, makes use of string
>
> select VW_RRP00010.*,RRP0.TB_RRP00115.*
> FROM RRP0.MV_RRP00010 VW_RRP00010,rrp0.TB_RRP00115
> where VW_RRP00010.LOAD_DT = TB_RRP00115.LOAD_DT
> AND VW_RRP00010.LOAD_TYPE_CD = TB_RRP00115.LOAD_TYPE_CD
> AND VW_RRP00010.FUSE_CTY_ID = TB_RRP00115.FUSE_CTY_ID
> AND (VW_RRP00010.ISO2_CTY_CD = TB_RRP00115.ISO2_CTY_CD OR ' ' =
> TB_RRP00115.ISO2_CTY_CD)
> /* and tb_rrp00115.load_dt = (SELECT TB_RRP00104.LOAD_DT COL1 FROM
> RRP0.TB_RRP00104 TB_RRP00104 WHERE '1' = TB_RRP00104.VSN_CD AND 'D' =
> TB_RRP00104.LOAD_TYPE_CD) */
> and tb_rrp00115.load_dt = '14/01/2007'
>
> Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
>
> SELECT STATEMENT Optimizer Mode=CHOOSE 63 K 2851
> HASH JOIN 63 K 10 M 2851
> PARTITION RANGE SINGLE KEY KEY
> TABLE ACCESS FULL RRP0.MV_RRP00010 91 K 2 M 1389 KEY KEY
> PARTITION RANGE SINGLE KEY KEY
> TABLE ACCESS FULL RRP0.TB_RRP00115 48 K 6 M 1290 KEY KEY
What version of Oracle? This looks like something old and long since desupported.

Local or global indexes?
What type of partitioning? (post the DDL that created the table too)

-- 
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