Re: Optimization: change in access path to one table changes join strategy to another table...
- From: "Tim Hopkins" <oracle-l@xxxxxxxxxxxxxxxxxx>
- To: jojo@xxxxxxxxx
- Date: Thu, 24 Aug 2006 15:14:32 -0000 (GMT)
Hi John,
Looks like transitivity.
1) The join condition "itemtype.sm29_logo_seat_num (+) =
tsm66.sm66_seat_num" indicates these two columns should be considered
equal, thus the where condition "sm66_seat_num = 1234" can be transformed
internally into "itemtype.sm29_logo_seat_num = 1234". This would then
allow an index range lookup on your sm29_logo_seat_num table.
2) Conversely, when using "UPPER(sm66_multi_des) LIKE '1234'" there is no
transitivity and thus the only conditions on sm29_log_seat_num are the
join conditions. If the CBO uses that as the leading table then there are
no predicates available at that stage of the execution plan and thus a
full scan makes sense.
Cheers,
Tim
> Hi
>
> *** The query:
> SELECT
> itemtype.logo,
> tsm66.sm66_seat_num
> FROM tsm66_attrmultimedia tsm66,
> tsm30_format tsm30,
> (SELECT
> /*+ MERGE
> INDEX(tsm29 ism29_i02)
> INDEX(tsm17 ism17_p01)
> */
> tsm29.sm29_logo_seat_num,
> tsm29.sm29_page_numb_num,
> MAX(DECODE(tsm17.sm18_info_num, 78,0, 1)) logo
> FROM tsm29_multimedia tsm29,
> tsm17_item tsm17
> WHERE tsm17.sm17_item_num = tsm29.sm17_item_num
> GROUP BY tsm29.sm29_logo_seat_num,
> tsm29.sm29_page_numb_num
> ) itemtype
> WHERE tsm30.sm30_frmt_num = tsm66.sm30_frmt_num
> AND itemtype.sm29_logo_seat_num (+) = tsm66.sm66_seat_num
> AND itemtype.sm29_page_numb_num (+) = tsm66.sm66_page_numb_num
> -- and UPPER(sm66_multi_des) LIKE '1234'
> and sm66_seat_num = 1234
> ;
--
//www.freelists.org/webpage/oracle-l
Other related posts:
