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

Hi John,

> When I query the regular table with some other (indexed)
> column, forcing the optimizer to do a table lookup for the later join
> key, the join strategy to the inline view changes (to the worse - nested
> loop acces with index range scans becomes hash join with full table
> scan).

Small correction.
the second execution plan you provided performs nested loop with full scan on the outer table.

>
> NESTED LOOPS 8 K 185 K 8686
> TABLE ACCESS FULL DBASM0SEMS.TSM29_MULTIMEDIA 8 K 109 K 29
> TABLE ACCESS BY INDEX ROWID
> DBASM0SEMS.TSM17_ITEM 1 9 1
>
> INDEX UNIQUE SCAN DBASM0SEMS.ISM17_P01 1 0

>
> Why is that?

My guess:
The predicates
sm66_seat_num = 1234
AND
itemtype.sm29_logo_seat_num (+) = tsm66.sm66_seat_num are transformed to
itemtype.sm29_logo_seat_num = 1234
i.e. the table tsm29_multimedia in the inline view is accessed with the predicate
tsm29_multimedia.sm29_logo_seat_num = 1234 leading to index access with cardinality = 1

There is no comparable join predicate for sm66_multi_des Something like
itemtype.some_column (+) = tsm66. sm66_multi_des Not to mention the UPPER and LIKE in the switched predicate. From this reason the predicate
UPPER(sm66_multi_des) LIKE '1234'
can't be applied on the table tsm29_multimedia; the in-line view is evaluated completely and the filter is applied in the second join.

Regards,

Jaromir

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