CBO and 'small' tables

Maybe some of you have seen this on v.7.3.x and v.7.2.x and know how to fix it...

In some cases, CBO would decide to perform a full table scan on a (relative) small table that is part of a multi-table join. There is an index on the relevant column on the small table. By using a stop watch, sql*trace, tkprof, explain plan, etc, I have determined that it would be faster to use index-search rather than full table scan on the small table. Since my personal preference is to use CBO except for instances where it ain't working, my workaround was to do a 'ANLAYZE TABLE <small table> DELETE STATISTICS' on the small table, thereby forcing rule-based optimization on the small table. That did the trick (that is, index-search is now being done on that small table).

Anyway, I was wondering if there is a less kludgy way to deal with this? Specifically, how does CBO decide when a full table scan on a table would be more efficient than index search (where in reality it ain't). I vaguely remember there is a init.ora parameter used to define the threshold between small and big tables -- but I can't remember what it was (I'm pretty sure it is not multi_block_read).

Pei Received on Thu Jun 11 1998 - 12:48:09 CDT