Re: ORA 8 optimizer really bad!

We just went through this with one of our projects. I spent 3 days optimizing statements for performance. I even had a two table inner join that insisted on using a full table scan for one of the tables even with the hint. One thing that worked for us was to script a mass "analyze table" and compute statistics for every table in the schema. This seemed to help most of the problems.

Daniel
Austin, TX

"Franz Mueller" <nospam#####franz.mueller_at_orbis.de> wrote in message news:389055a1.23592374_at_news.salink.net...
> 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 - 10:35:18 CST