Re: optimizer for oracle 8

On Thu, 17 Jun 1999 14:42:34 GMT, "Ying" <ying_at_spellchecker.com> wrote:

>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.

If you are using the FIRST_ROWS optimiser directive, it will be relying on statistics for each table to help develop the execution plan. Without stats in place, you'll get weird and wild results. Analyse each table involved to compute statistics, and then try again.

Ciao
Fuzzy
:-) Received on Thu Jun 17 1999 - 18:20:07 CDT