FAQ
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-l

Search Discussions

Discussion Posts

Previous

Related Discussions

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

2 users in discussion

Greg Rahn: 1 post Jeremy Schneider: 1 post

People

Translate

site design / logo © 2022 Grokbase