Re: CBO and 'small' tables

Hi Pei,
Look into hints ... /* index(IDX_SOMETHING) */ "small" tables are a matter of definition, but a full tablescan of 50 rows is usually much faster than using an index for lookup. When you do joins the situation is somehow simular ... but not entirely.

Things that make scans of "small" tables slow are: Highwatermark too high. For instance, you've deleted a lot of rows from the table ... a full tablescan still scans the unallocated blocks. Deallocate them and try again. Scanning using indexes can be slow too ... and indexes are even worse when you delete ... if you have lots of updates to your "little" table, try to recreate the table, and see if performance is better ....

Regards
Peter H. Larsen
Oracle Consultant

Pei L. Ku wrote:

> 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:57:40 CDT