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 cbo makes assumptions when stats are missing. They are documented in (I think) the performance manual, but things like average row size 100 bytes etc are used. On later versions of Oracle, the optimizer can dynamically sample the rows to make a better estimate

hth
connor

-- 
=========================
Connor McDonald
http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue"