FAQ
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

Search Discussions

  • Greg Rahn at Aug 3, 2011 at 10:22 pm
    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

Related Discussions

Discussion Navigation
viewthread | post
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