Re: Optimization: change in access path to one table changes join strategy to another table...
- From: "jaromir nemec" <jaromir@xxxxxxxxxxxx>
- To: <jojo@xxxxxxxxx>
- Date: Thu, 24 Aug 2006 16:45:44 +0200 (CEST)
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?
Only wild 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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
