Forum Stats

  • 3,760,106 Users
  • 2,251,647 Discussions
  • 7,870,976 Comments

Discussions

Update statistics after creation of index

1723552
1723552 Member Posts: 33
edited Aug 5, 2014 9:54AM in General Database Discussions

Hi,

Version : 11g

Will oracle update the statistics after creation or modification of index ?

Thanks.

BrunoVromanZoltan Kecskemethypauljohny100

Best Answer

«1

Answers

  • 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

    1723552
  • Zoltan Kecskemethy
    Zoltan Kecskemethy Member Posts: 1,142 Gold Badge
    Accepted Answer

    Yes when automatic CBO stat collection job runs.

    I usually issue a

    ALTER INDEX <index_name> COMPUTE STATISTICS;
    

    after index created.

  • BrunoVroman
    BrunoVroman Member Posts: 1,848 Silver Crown

    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.

    pauljohny1001723552
  • Zoltan Kecskemethy
    Zoltan Kecskemethy Member Posts: 1,142 Gold Badge
    edited Aug 4, 2014 7:05AM

    Yes "later" when the default CBO stat gather job runs in the maintenance window if it is enabled.

    I meant if I create a new index I usually issue compute stats just after. This is a best practice / habit for me.

    Yes I see this could be an overhead in new versions ...

  • BPeaslandDBA
    BPeaslandDBA Member Posts: 4,615 Blue Diamond
    
    
    1. ALTER INDEX <index_name> COMPUTE STATISTICS; 
    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

  • Zoltan Kecskemethy
    Zoltan Kecskemethy Member Posts: 1,142 Gold Badge
    edited Aug 4, 2014 11:00AM

    Thank you for fixing me.

    I see. But compute statistics does not harm anything IMHO. (just extra effort)

    I know the dbms_stats pkg and that runs from the auto collect job as well.

  • BPeaslandDBA
    BPeaslandDBA Member Posts: 4,615 Blue Diamond

    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

  • Version : 11g
    Will oracle update the statistics after creation or modification of index ?
    

    Yes - since 10g see the SQL Language doc

    ALTER INDEX

    COMPUTE STATISTICS Clause 
    This clause has been deprecated. Oracle Database now automatically collects statistics during index creation and rebuild. This clause is supported for backward compatibility and will not cause errors.
    

    And if you have stats on that table you want to keep then you MUST lock them before any modification since there is no other way to prevent Oracle from recomputing them.

    See the end of this AskTom thread:

    https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5087906100346466422

    Is anyway we can tell Oracle not to run Compute Statistics while creating the indexes?
    . . .
    no, there is not. it is a feature, we compute them on a rebuild/create. 
    
    BrunoVromanZoltan Kecskemethy
  • Zoltan Kecskemethy
    Zoltan Kecskemethy Member Posts: 1,142 Gold Badge

    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.

  • jgarry
    jgarry Member Posts: 13,842

    Here's some examples of the difference: Analyze This – 2 | Oracle Scratchpad

This discussion has been closed.