Hash join order

From: Richard Kuhler <noone_at_nowhere.com>
Date: Thu, 27 Mar 2003 23:41:18 GMT
Message-ID: <y_Lga.6103$Hx.2078857@twister.socal.rr.com>

Is there a significance to the order in which objects are listed in an execution plan for a HASH JOIN. For example, are these two plans really different ...

SELECT STATEMENT
  HASH JOIN
    TABLE ACCESS (FULL) OF 'TABLE_A'
    TABLE ACCESS (FULL) OF 'TABLE_B' SELECT STATEMENT
  HASH JOIN
    TABLE ACCESS (FULL) OF 'TABLE_B'
    TABLE ACCESS (FULL) OF 'TABLE_A' Is this indicating which table will actually have the hash produced and which will be iterated through? If so, how can you force the order in situations where you are hash joining a table and a partial result (two tables joined first before the hash)? For example, I want to be able to switch between plans like this but can't seem to do it ...

SELECT STATEMENT
  HASH JOIN
    TABLE ACCESS (FULL) OF 'TABLE_A'
    NESTED LOOPS

      TABLE ACCESS (FULL) OF 'TABLE_B'
      TABLE ACCESS (BY INDEX ROWID) OF 'TABLE_C'
        INDEX (UNIQUE SCAN) OF 'TABLE_C_IDX'

SELECT STATEMENT
  HASH JOIN
    NESTED LOOPS

      TABLE ACCESS (FULL) OF 'TABLE_B'
      TABLE ACCESS (BY INDEX ROWID) OF 'TABLE_C'
        INDEX (UNIQUE SCAN) OF 'TABLE_C_IDX'

    TABLE ACCESS (FULL) OF 'TABLE_A' Thanks in advance for the help.

Thanks,
Richard Kuhler Received on Thu Mar 27 2003 - 17:41:18 CST