Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Database gets angry when simulating hash joins

Database gets angry when simulating hash joins

From: <andreas.prusch_at_sintec.de>
Date: Sat, 23 May 1998 06:33:46 GMT
Message-ID: <6k5qka$7hr$1@nnrp1.dejanews.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US