Re: Hashing two tables for a join to BIG TABLE?

On Mon, 1 Apr 2002, jonathan_at_jlcomp.demon.co.uk wrote:
>
> This may be highly version dependent,

Oracle 8.1.7

> but it sounds like you have the tables in the wrong order.
>
> I infer that you are not trying to exclude data
> by the joins, but are trying to extend the rows
> of the big table.
>
> The following should do what you want:
>
> select
> /*+
> ordered
> use_hash(big)
> use_hash(small2)
> swap_join_inputs(small2)
> */
> . . .
> from
> small1,
> big,
> small2
> where
> ...
>
> NOTE ESPECIALLY the order of the tables.

Does the plan you displayed always join in the order you describe, or does it only work this way when it was hinted at with the swap_join_inputs hint? ie, if I get this same plan through different hints, will I still get the same order of hash table building?

>
> Despite the apparent contradiction in the ORDERED hint, Oracle should
> visit the tables in the order small2, small2, big.
>
> This set of hints should result in a plan like:
>
> hash join
> table access full of small2
> hash join
> table access full of small1
> table access full of big

Okay,

It looks good! It turns out that my previous query that I was using actually got the correct plan with just the ordered hint, but I messed around with the ordering, running it a few times, and now I'm off to execute the really large one, which is what prompted my question. This really large one never finished, so I tried to think how I might be a little more savvy with it. Jonathan gave me the savvy (I'm hoping anyways)

At least I'm that much more knowledgeable about the plan I'm looking for this time.

> Provided you have your hash_area_size large
> enough to hold an in-memory hash of small1
> and small2 (NB your hash_area_size will be allocated

             ^^^^
What's NB?

> twice using this plan, once for each small table), the first row
> should be generated almost as fast as you can scan small1 and small2
> in turn to build the two hash tables in memory.

Thanks alot Jonathan.

Your answers are even more enticing than your book, because you are answering a direct question.

I really appreciate your answers and I talk up your book to every Oracle person I run across. I haven't even read it cover to cover yet. :-)

-- 
Galen deForest Boyer
Sweet dreams and flying machines in pieces on the ground.