Benny wrote:
> 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
Please don't top post if you want help. Scroll to the bottom to reply.
If this is 9.2.0.7 then your explain plan looks like it was made with
an ancient script and your optimizer mode, CHOOSE, makes no sense. All
of which makes me wonder what other things have not been done in a 9i
consistent manner. For example ... have statistics been collected for
the optimizer using DBMS_STATS?
Without the real query and a real explain plan (created with DBMS_XPLAN)
my only advice would be to return to basics, get current with 9.2, run
a trace through TKPROF, and purchase a copy of Jonathan Lewis' book
"Cost Based Oracle Fundamentals" ISBN 1-59059-636-6.
--
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 - 08:45:11 CST
