Re: subselect results in poor performance

Sorry that I forgot to mention Oracle version. Enterprise Edition 9.2.0.7

All tables are range partitioned on load_dt column. This table TB_RRP00104 contains only 10 records and the subquery only retrieves one from it (so this is very fast).

What am I missing here ? Or is the optimizer just not up to the job ? Difficult to believe that....
Kind regards,
Benny

DA Morgan schreef:
> 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
Received on Wed Jan 17 2007 - 01:46:16 CST