ORA 8 optimizer really bad!

Hi,

I have seen really disappointing behaviour of the ORACLE 8 optimizer:

in statement that looks like this:
SELECT .... FROM TABLE1,TABLE2....
WHERE
TABLE1.Col1=x AND TABLE1.Col2=y AND
TABLE1.T2_ID=TABLE2.T2_ID AND TABLE2.Col1=z.... (i.e. inner join on TABLE1 and TABLE2)
There is an Index Col1,Col2 on TABLE1 and T2_ID,Col1 on TABLE2. Both tables have something like 100000 entries and there are approx 5 hits.
EXPLAIN PLAN takes the index on Table1, then performs a full table scan on Table2 and finally combines them using a HASH JOIN.. The estimated costs are 3500
I would have expexted the optimizer to do a nested loop on the 2 tables since the indexes are perfectly suited. EXPLAIN PLAN computes costs of 4000 (if I force the it using the /*+ USE_NL (T1 T2)*/ hint), and therefore it uses variant 1.
In fact, if I run the 2 ways, the nested loop is 100 times faster than the Hash Join. Why does the optimizer such a bad job?

Franz Received on Thu Jan 27 2000 - 08:37:00 CST