Re: cost

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Mon, 5 Apr 2004 20:28:49 +0100
Message-ID: <006f01c41b44$38ac5280$7102a8c0@Primary>

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_at_hotsos.com> To: <oracle-l_at_freelists.org> 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_at_freelists.org 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

Received on Mon Apr 05 2004 - 14:25:05 CDT