Re: CBO when one table has no stats

From: Richard Foote <richard.foote_at_bigpond.com>
Date: Mon, 31 Mar 2003 20:28:34 +1000
Message-ID: <gAUha.3643$1s1.52135@newsfeeds.bigpond.com>

"NetComrade" <andreyNSPAM_at_bookexchange.net> wrote in message news:3e87d30d.2983290304_at_nyc.news.speakeasy.net...
> 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?

Hi NetComrade,

Just one teensy weensy table with statistics and the CBO is in business, providing the optimizer_mode is not rule.

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

You have a number of possible approaches but I would avoid the RBO if possible with supported versions of Oracle. I assume you want to avoid FTS because these queries of yours run at times just after the tables have been loaded but before they've had time to be analyzed. The obvious solution is to gather stats after the load or before the queries. You may also want to look at setting the stats directly with dbms_stats or use hints or plan directives to get the desired result.

Good Luck

Richard Received on Mon Mar 31 2003 - 04:28:34 CST