Why the optimizer is not choosing the best plan?

From: Sami Seerangan <dba.orcl@xxxxxxxxx>
To: Oracle Discussion List <oracle-l@xxxxxxxxxxxxx>
Date: Wed, 20 Oct 2004 15:49:01 -0400

Hi All,

I have 4 tables join query(all tables statistics are upto date) that
is working fine if it goes for HASH_JOIN.  But without HINT it is
always doing NL_JOIN and  takes about 50 secs to execute.

However using ORDERED hint it is going for HASH_JOIN and the response
time got reduced to less than 5 secs, but we don't prefer to implement
HINT based solution

Why the optimizer is not choosing the best execution plan(In this case
hash_join rather than NL_join)?


-- 
Sami Seerangan
Oracle DBA
--
http://www.freelists.org/webpage/oracle-l

Follow-Ups:

Re: Why the optimizer is not choosing the best plan?
From: Antonio Belloni

RE: Why the optimizer is not choosing the best plan?
From: Cary Millsap

Re: Why the optimizer is not choosing the best plan?
From: Ryan

Why the optimizer is not choosing the best plan?

Other related posts: