My guess would be that this is a deliberate
heuristic introduced some time around 9
to avoid nested loop full tablescans when
the numbers are small.

If you create a unique key constraint on
the NAME so that Oracle "knows" that
there is just one name that matches, and
add the FULL() hint on dept so that the
resulting UK index is not used, you get
the expected behaviour (i.e. nested loop
fts cost 4, rather than hash join cost 5).


Regards

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

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

April 2004 Iceland http://www.index.is/oracleday.php
June 2004    UK - Optimising Oracle Seminar


----- Original Message -----
From: "Cary Millsap" <cary.millsap@(protected)>
To: <oracle-l@(protected)>
Sent: Monday, April 05, 2004 6:13 PM
Subject: RE: cost


Mark,

Unfortunately, not *always*. :( See Karen Morton's test case below my
sig.



----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@(protected)
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------