Re: cost

  • From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Mon, 5 Apr 2004 20:28:49 +0100

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


Jonathan Lewis

The Co-operative Oracle Users' FAQ

April 2004 Iceland 
June  2004      UK - Optimising Oracle Seminar

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


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

Please see the official ORACLE-L FAQ:
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
Archives are at
FAQ is at

Other related posts: