Re: How much space is used on Hash join?

For an in-memory hash, Oracle will need
memory for t1.fld1, t1.id1, 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.

On top of this there is the overhead of preparing the bitmap of used hash buckets, 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.

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.

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

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).

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminar - UK, April 3rd - 5th
http://www.jlcomp.demon.co.uk/seminar.html

Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases


Galen Boyer wrote in message ...


>Suppose:

>

>create table t1 (id1,id2,id3,fld1,fld2,fld3);

>create table t2 (id1,id4,fld4,fld5,fld6,fld7);

>

>I need to query both tables fully.

>

>SELECT /*+ use_hash(t1) */

>       t1.fld1, t2.fld4

>FROM   t1,t2

>WHERE  t1.id1=t2.id1;

>

>Does Oracle need HASH space for all the columns in both tables or does it

>need space for only those columns referenced in the query?

>

>It reads an entire table block by block, but as it is picking off the

>rows from the block it just read and is creating the hash key, does it

>lose the uneeded columns as well, or does it create a hash key that

>references the entire block somehow?

>

>This question could be asked of sort-merge joins as well.  I hope it was

>clear.

>

>--

>Galen deForest Boyer

>Sweet dreams and flying machines in pieces on the ground.