We have made the following approach.
We have three tables which very often joined by th CBO
with hash joins. Now we thought we could provide the
necessary structures for getting the data with hash joins.
Because it is a star schema with static small dimension tables
we stored the dimensions into hash clusters. At this point
hash joins works faster but the hash joins with full table
scans are still in the execution plan of the optimizer.
But what we want is the use of the provided hash cluster.
We forces the use the by applying the hint /*+ rule */. And
now we are completely mislead. The time for the query takes
about 100 times slower. The monitoring with tkprof told us
the reason. Hash Joins needs accessing 400 blocks and our
simulation with nested loops and table access hash takes
400000 blocks.
Now our question is, what is the difference between hash
joins and nested loop with table access hash?
-----== Posted via Deja News, The Leader in Internet Discussion ==-----
http://www.dejanews.com/ Now offering spam-free web-based newsreading
Received on Sat May 23 1998 - 01:33:46 CDT
