Re: A bit disappointed in CBO..

"NetComrade" <andreyNSPAM_at_bookexchange.net> wrote in message news:3e4aa9cd.621980441_at_nyc.news.speakeasy.net...
> We have been running RBO forever.

<snip>

> Oracle started to pick the wrong tables as driving tables.. Is that
> normal?
>
> We are running 8.1.7.4, did things improve significantly in 9i?

8.1.7.4 is pretty reasonable IMO.

> cost-based (for whatever reason i had to set optimizer_goal to
> all_rows in order to get CBO, all tables are analyzed, user_group is a
> view on team_group)

You don't need to set optimiser_goal to all_rows to use the CBO, statistics on the tables and optimiser_mode=choose means you will be using the CBO. The only ways to be running RULE are

1. optimiser_goal=rule
2. no stats on any tables, and no use of features that kick of the CBO (eg partitioning etc). Thus with stats on I would suggest that your second explain is using CBO, and that it is setting the goal to all_rows that is causing the problem for this particular query (especially as the subquery has a rownum in it).

--
Niall Litchfield
Oracle DBA
Audit Commission UK