analyzing tables

[davey23uk]

Hi, we recently had a problem where querying a view was taking a long time and it basically wasnt using the indexes on the base table.

Now we anlayzed the base table with analyze table xxx estimate statistics and all was fine.

When the tables were first created in out scripts the tables are analyzed then. Does the fact that they are no rows in the table when it was analyzed tell Oracle to go and do a full table scan on it?

Thanks

[DaPi]

YES.

It's faster to read a small table by FULL SCAN than to bother with the indexes. So analyze from time to time.

[DaPi]

I would add that because of FTS being used on small tables, if you have some that are frequently used (e.g. a table of codes that doesn't fill a block) then ALTER TABLE . . . CACHE; will help keep them in memory.

[slimdave]

Let me just add that an index scan could still be faster than an FTS even when there are no rows in the table. This is because the FTS will scan up to the high-water mark, which could be many Mb if the table has had all of it's rows deleted, not truncated, whereas the index scan would still be very fast. Doesn't always apply, but worth noting.

[DaPi]

slimdave, YES! - can happen if you use a table to keep updates between ROW & STATEMENT triggers - in one I inherited I ended up with 20Mb of nothing! VERY slow with FTS and no CACHE!

[davey23uk]

Thanks for the info.

Apparantly we analyze the tables on creation because the cost based optimiser didnt always pick up the fact that the indexes were there even with hints - is this a known problem anyone else has seen?

These are large tables normally, a single partition holding anywhere from 10 million records to 50 million so I dont think cache will work either

And we never delete from these tables either, we truncate the partition we are going to load into for that day so the HVM gets reset

[slimdave]

You might like to look into the MONITORING option for tables, so you can compare then number of rows at the time of the last analyze with the number of insert/update/delete/trucates since then, and use that as the basis for analysis.