Re: How much space is used on Hash join?

On Fri, 29 Mar 2002, jonathan_at_jlcomp.demon.co.uk wrote:
>
> For an in-memory hash, Oracle will need memory for t1.fld1, t1.id1,

Thanks. This is what I was hoping.

> times the number of rows it thinks it will get from t1; plus a bit to
> cater for the potential for errors and the need to use some space for
> buffering I/O.

Hm... I'll read up on this. I've seen some different papers and urls talking about this. Now, I have a real example to help make it sink in.

> On top of this there is the overhead of preparing the bitmap of used
> hash buckets

Ah... A little clue into how this is actually happening. I bet you have this described in your book somewhere?

> , and since the hash table is created with an excessive number of
> buckets to increase the scatter of the hash function and try to keep
> one row per hash bucket, the "waste" space might be quite large.

Don't you love it. You think you are getting a simple answer, and then ... :-)

> You might want to run a few tests with a 10104 trace set so that you
> can see the list of memory allocations that Oracle makes.

I'll try to give this a shot, sometime this week.

> You can keep the tests quick by executing just the code sample you
> have written, and stopping immediately after the first rows are
> returned.

Hm... I'll have to go with the real tables.

> BTW - in the code you have given, you have instructed Oracle to make
> the hash table from T2, implicitly going in the opposite order to the
> list of tables in the FROM clause. (However, Oracle may choose to
> swap_join_inputs in mid-stream and do it the other way round - even
> with the ORDERED hint).

Where can I read up on swap_join_inputs? I don't see it referenced on the search engine Tahiti, or on Tom Kyte's site. I haven't heard of it, and I'm not sure what is happening when it says it is "swapping join inputs".

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