Update statistics after creation of index

Oracle update statistics during rebuild and create process. Except you have locked statistics in table with

dbms_stats.lock_table_stats

You can verified it with this query

SELECT stattype_locked FROM dba_tab_statistics WHERE table_name = 'TABLE_NAME' and owner = 'OWNER'

If it returned ALL it is locked.

HTH - Antonio NAVARRO

Hello Zlotan,

you reply "yes" AND you issue "alter index compute statistics"?  Maybe you mean that the statistics will be gathered "later"...

As all the info is available during the creation of the index, the statistics are gathered "for free" during the (re)creation (at the exception mentioned by Antonio, "expected and desired behavior"). You can easily check this by looking at the statistics after the rebuild.

So it is NOT necessary to gather them once again...

Best regards,

Bruno Vroman.

ALTER INDEX <index_name> COMPUTE STATISTICS;

Do not use this command.

For starters, when an index is created, Oracle will gather stats on the index at that time. There is no need to redo the work.

Lastly, use the DBMS_STATS.GATHER_INDEX_STATS command instead of the ALTER INDEX COMPUTE STATISTICS command.

Cheers,
Brian

In some cases, its just extra effort. In other cases, it can be harmful. I have some production databases that don't like the extra work. The creation of an index is taxing on the system and the end users start to feel a performance downgrade until the index creation is completed. The only reason I do this during periods of high usage is to resolve a problem that cannot wait until a later time. In those cases, I wouldn't want to contribute more to their pain by calculating stats one more time than is necessary.

Cheers,
Brian

That's why I came here. Learn, learn and learn

I was looking for this in the reference and concepts book....

Thank you for the overwhelming info.