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

From: John Jørgensen <jojo_at_stibo.com>
Date: Thu, 24 Aug 2006 14:24:42 +0200
Message-ID: <C6BF31AC72CA0F47BC838E2332F87CB704B63742@MAILA.graphic.stibo.int>

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_at_amdocs.com         | 
Phone:  +45 8939 8939             | Fax:    +45 8939 7499
Direct: +45 8939 7420             | 
 

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

Received on Thu Aug 24 2006 - 07:24:42 CDT