CBO doesn't consider HA Join

From: Jeremy Schneider <jeremy.schneider_at_ardentperf.com>
Date: Wed, 3 Aug 2011 15:30:26 -0500
Message-ID: <CA+fnDAbYmHFAAkF7mgZhaH4VAF9G7hNfpUTnCHiWZ=d9dWsswA_at_mail.gmail.com>

I'm tuning one of those beastly SQL statements which has about 7 views under it right now, and running into something I don't quite understand.

Although there are several tables, there's really one big table and a series of smaller tables with various filters and joins. This is a warehouse environment where big fat I/O pipes mean direct-path parallel multiblock reads are many orders of magnitude faster than sequential reads. We're reading 68K rows, so I decided to just see what a FULL() hint on the big table would do. however, oracle sticks this full tablescan on the inside of a nested loop - and it seems to me that I might want to use a hash join instead of potentially running this full tablescan multiple times. what puzzles me is that oracle doesn't seem willing to consider a USE_HASH() hint. I looked at the 10053 trace, and found that Oracle didn't even consider the HA Join for this join order - although it does consider using a hash join with the big table for other join orders. Probably not a show-stopper here, but does anyone know what would cause Oracle to exclude hash join from it's consideration for a particular table join order?

-J

PS. the plan looks like this:

SELECT STATEMENT
  TABLE ACCESS BY LOCAL INDEX ROWID
   NESTED LOOPS
    NESTED LOOPS

     NESTED LOOPS
      MERGE JOIN CARTESIAN
       NESTED LOOPS
        TABLE ACCESS BY INDEX ROWID
         INDEX FULL SCAN
        TABLE ACCESS BY INDEX ROWID
         INDEX UNIQUE SCAN
       BUFFER SORT
        TABLE ACCESS BY INDEX ROWID
         INDEX RANGE SCAN
      TABLE ACCESS BY INDEX ROWID
       INDEX UNIQUE SCAN
     PARTITION RANGE ITERATOR
      PARTITION LIST ITERATOR
       TABLE ACCESS FULL  ===========> REALLY_BIG_TABLE
    PARTITION RANGE ITERATOR
     PARTITION LIST ITERATOR
      INDEX RANGE SCAN

--

http://www.ardentperf.com
+1 312-725-9249

Jeremy Schneider
Chicago

--

http://www.freelists.org/webpage/oracle-l Received on Wed Aug 03 2011 - 15:30:26 CDT