Note in-line. Regards
Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html ____UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html ----- Original Message ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Tuesday, January 06, 2004 6:59 PM > Nope. The answer is b). In the FIRST_ROWS mode, optimizer prefers NL to all other > methos despite the price. > Unless the alternative is a full tablescan on the inner table - in which case merge or hash joins can be considered. The question itself is non-trivial, as the cost of a nested loop is: Cost of outer acquisition + Cost of inner access * cardinality of outer acquisition. But the cost of a merge join is: Cost of first acquisition + cost of first sort + Cost of second acquisition + cost of second sort + Cost of merge It seems likely that if the first table returned 1 or 2 rows, then a nested loop with FTS could be cheaper than a sort merge, but if the outer table returned 3 Oracle would switch to a sort merge. (Assuming equijoin). On the other hand, if the second table required a very large sort, I'm sure you could engineer a sort_area_size that would make the sort cost more than three times the cost of a simple tablescan - which means you could change the access path by changing the sort_area_size. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
