Re: FIRST_ROWS and SYS schema issue

CHOOSE means if stats exist use cost optimization with a goal of all_rows, and if no stats exist to run using the rule based optimizer. Changing the goal to FIRST_ROWS for the database will cause the optimizer to weigh indexes more heavily in its determination of the execution path. This makes the cost based optimizer behave more like, but not the same, as the rule based optimizer and if your application is an OLTP this may be a good choice. Making this change will have a tendency to shift your need to tune SQL from the on-line screens to your reports and batch jobs. But this does bring us to your real question, will changing the optimizer goal at the database level to first_rows from choose cause Oracle to estimate on the fly like for tables with no statistics like a hint does starting with 7.3? My best guess is, it will not.

Roman Gelfand <rgelfand_at_masmid.com> wrote in article <74hgrs$see$1_at_news.monmouth.com>...
> Since SYS schema in version 7.3.4 is tuned for Rule based optimization,
does
> it make sense to use anything other than CHOOSE( specifically FIRST_ROWS)
as
> optimizer goal.
>
> Thanks,
>
> Roman
>
>
>
Received on Tue Dec 08 1998 - 09:47:14 CST