Re: CBO when one table has no stats

NetComrade wrote:
>
> For whatever reason i thought that CBO will be used if ALL tables have
> statistics collected for them, but it seems it's the other way around,
> if at least ONE table in the query has statistics for it it would be
> the other way around, could somebody confirm?
>
> We have some tables that stay empty, and get filled very quickly and
> get used right away, and i hoped to avoid FTS by deleting statistics
> for those, and relying on RBO, now i need to come up with a better
> strategy.
> .......
> We use Oracle 8.1.7.4 on Solaris 2.7 boxes
> remove NSPAM to email

The only time RBO will be used is if you have OPTIMIZER_GOAL set to RULE or are using the RULE hint. And in either case not using any other hints.

Or if the OPTIMIZER_GOAL is set to CHOOSE and *none* of the objects in your query have statistics generated for them. I have a feeling using techniques the RBO doesn't support, partitioning, bitmap indexes etc, also invokes the CBO, but I've never checked that.

If you have tables you don't want to generate statistics for the CBO will make a guess about the number of rows in the table based on the number of blocks, which isn't much help if your high water mark is way above your data. Anyway, you should be able to use a hint in your query to guide the CBO to use whatever index you feel is appropriate rather than using a FTS. Received on Mon Mar 31 2003 - 03:53:39 CST