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?

Greg Rahn


Search Discussions

Discussion Posts


Related Discussions

Discussion Navigation
viewthread | post
posts ‹ prev | 2 of 2 | next ›
Discussion Overview
grouporacle-l @
postedAug 3, '11 at 8:30p
activeAug 3, '11 at 10:22p

2 users in discussion

Greg Rahn: 1 post Jeremy Schneider: 1 post



site design / logo © 2022 Grokbase