optimizer for oracle 8

From: Ying <ying_at_spellchecker.com>
Date: Thu, 17 Jun 1999 14:42:34 GMT
Message-ID: <uP7a3.3168$ll5.22082@server1.news.adelphia.net>

We are using Oracle 8 an we have a large table with several indexes. When we query the table, we use hints to try to force the use of the proper index. But sometime some queries are very slow anyway. We have checked them
with 'explain plan' and find the indexes we requested are not always being used. For example, with the query below

select /*+ INDEX(mytable i2_index) */ ....

     from mytable
     where name like 'SMITH%'
       and file_date between
        to_date('01/01/1999 000000', 'mm/dd/yyyy hh24miss') and

   to_date('01/02/1999 235959', 'mm/dd/yyyy hh24miss')    order by name, other fields ...

i2_idex is based on name and other fields. The order by is indentical to the
index.

If the date range is small, Oracle will use another index based on date range
and then sort the data. This generates a slow query. If we change the date range to include more days, Oracle will use the correct index.

ALso, we have found some queries work correctly if the optimizer goal is set to FIRST_ROWS. But still others don't work with that setting and do work with the goal of CHOOSE. Do we have to examine every query in the program and set the optimizer goal for each one?

Also, we have one query that has a "group by" clause. Different results are returned depending on the optimizer goal setting.

We would like to know if others are having problems with the Oralce 8 optimizer. This problem does not happen with oracle 7. It only happens with oracle 8. Received on Thu Jun 17 1999 - 09:42:34 CDT