Re: Optimization: change in access path to one table changes join strategy to another table...

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

--
http://www.freelists.org/webpage/oracle-l