Optimization: change in access path to one table changes join strategy to another table...
- From: John Jørgensen <jojo@xxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 24 Aug 2006 14:24:42 +0200
Hi
I have a query that joins a regular table with a "group by" inline view - outer.
When I query the regular table with an (indexed) part of the join criteria to
the inline view (part of primary key), everything is perfect.
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).
Why is that? What can I do about it? Once the optimizer has decided to use FTS,
it seems to be rather indifferent about whatever I try to hint it about.. :-)
See queries and explain plans below.
NB: The original query is much more complicated - this is a "boiled down"
version...
DB vers: 10.2.0.2.0
Best regards
/John (Brand new sheep in the flock)
*** 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
;
*** has explain plan:
Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
SELECT STATEMENT Optimizer Mode=ALL_ROWS 1 7
HASH JOIN OUTER 1 43 7
INDEX RANGE SCAN DBASM0SEMS.ISM66_P01 1 14 2
VIEW 1 29 4
HASH GROUP BY 1 22 4
NESTED LOOPS 1 22 4
TABLE ACCESS BY INDEX ROWID DBASM0SEMS.TSM29_MULTIMEDIA 1 13
3 .
INDEX RANGE SCAN DBASM0SEMS.ISM29_I02 1 2
TABLE ACCESS BY INDEX ROWID DBASM0SEMS.TSM17_ITEM 1 9 1
INDEX UNIQUE SCAN DBASM0SEMS.ISM17_P01 1 0
*** If you change the swap commenting of the last two lines you get the
following explain plan:
Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
SELECT STATEMENT Optimizer Mode=ALL_ROWS 22 8691
HASH JOIN OUTER 22 1012 8691
TABLE ACCESS BY INDEX ROWID DBASM0SEMS.TSM66_ATTRMULTIMEDIA 22 374
3
INDEX RANGE SCAN DBASM0SEMS.ISM66_I02 1 1
VIEW 8 K 244 K 8687
HASH GROUP BY 8 K 185 K 8687
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
*** Now, the nested loops join is a hash join and we have a table access full.
_________________________________________________________________________________________________
John Jørgensen
Software Engineer
Amdocs Stibo Graphic Software Aps | Sønderhøj 8 | DK-8260
Viby J
john.jorgensen@xxxxxxxxxx |
Phone: +45 8939 8939 | Fax: +45 8939 7499
Direct: +45 8939 7420 |
Other related posts:
