Re: CBO doesn't consider HA Join
Date: Wed, 3 Aug 2011 15:22:18 -0700
Message-ID: <CAGXkmis5cSU_fqma0ckR=wY7dGxuAZNHSjrHMs2Q32dD=k1fCg_at_mail.gmail.com>
It would be much easier to comment if you can provide the dbms_xplan.display(format=>'+outline') output.
On Wed, Aug 3, 2011 at 1:30 PM, Jeremy Schneider < jeremy.schneider_at_ardentperf.com> wrote:
> 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
>
-- Regards, Greg Rahn http://structureddata.org -- http://www.freelists.org/webpage/oracle-lReceived on Wed Aug 03 2011 - 17:22:18 CDT