Re: hash joins and pga/temp space?

The algorithm Oracle uses for hash joins is wonderfully explained starting on page 319 of Jonathan Lewis' "Cost Based Oracle", including the memory management and even some events that may help you answer most of your questions about memory sizing. With figures too :)

There are three variants of the hash join - optimal, one-pass and multipass - the last one relies heavily on temp for storing intermediate results, the first one uses only memory.

I'd suggest that you take a look to the book - it's a difficult topic and I can't claim to have mastered it completely ;)

regards
Alberto

On 11/14/06, ryan_gaffuri_at_comcast.net <ryan_gaffuri_at_comcast.net> wrote:
>
> If I do a query that is doing a hash join. Is the amount of pga/temp space
> required based entirely on the table that is hashed (smaller) table. Or will
> more temp space be required for a larger table?
>
> Also, after a table is hashed and the join takes place, is the result set
> temporarily stored in pga/temp space before being return (or pushed to
> another operations?).
>
> Is there anyway to calculate how much pga/temp space will be required for a
> hash join if you know how much data will be queried from each table? This
> could be useful for predictive modelling. For example we have a query that
> does a hash join in. We know our data sets will increase to X. How much more
> temp space can we estimate we need for this query?

-- 
Alberto Dell'Era
"Per aspera ad astra"
--
http://www.freelists.org/webpage/oracle-l