Optimizer problem?

From: Waldemar Schlonsok <wschlonsok_at_quantum.de>
Date: 1996/12/12
Message-ID: <32B00960.56E6_at_quantum.de>#1/1

Hallo all,

i am workin with the Oracle database version 7.2.2.4.0 on a Siemens RM400 and Sinix V 5.42 and i think the optimizer has a bug. There are two tables, table a and b and a is the master of b. Say the column ckey is the primary key of table a and is a part of the primary key of b. There is a column status in the table b, which values ary very bad distributed. There is also a index on table b containig the columns status and ckey (in this order). The distribution of values is:

     status     #rows
       AG       15000
       AN       1500
       AK       200
       TF       30
       XX       14

there are also nearly 5000 rows in table a. If i execute a query

       select * from a, b
       where  a.ckey=b.ckey
       and    a.anycolumn = myvalue
       and    b.status = 'TF'

so behavious the optimizer very strange. If choose the rule based optimizer, so the query is processed very fast, because the optimizer decided to access the rows of table b by my index. If i execute this query with a cost based optimizer, the performance is very bad, because the optimzer access the table b by full table scan and there after joins the results with the table a. Even if i implement a hint to the index in the query, the cost based optimizer does not use the index.
I think either the optimizer has a bug or the cost functon of the optimizer is very simple.
Are there any propositions? Please e-mail me.

Waldemar Schlonsok Received on Thu Dec 12 1996 - 00:00:00 CET