Re: Sqlplus tunning

> I thought that hash_join was
> supposed to be best....

Just tried it both ways, with nested loops and hash join, on tables with the right indexes.

Nested loops has elapsed time of 10 seconds, hash join is 5 seconds.

However, the hash join spends about 4 seconds building the hash table before any results start to come back. The nested loops starts returning rows right away -- so if you are returning data to a user's screen nested loops would be a better choice even thought it takes longer because they see results right away -- by the time they read the first screen and start scrolling the rest will be there for them.

Guess it depends on what your definition of fast is.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Greg Moore
  INET: sqlgreg_at_pacbell.net

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).