Forum Stats

  • 3,769,445 Users
  • 2,252,967 Discussions
  • 7,875,030 Comments

Discussions

Update statistics after creation of index

2»

Answers

  • jgarry
    jgarry Member Posts: 13,842
    edited Aug 4, 2014 12:20PM

    That asktom answer seems to beg a question.

    Consider this thought experiment:

    You have a master/detail couple of tables, each with a couple of indices.  There's enough data in the tables so no new statistics are collected for a long time, even though new data is being added to change data distribution.  Now, for whatever reason, one index is altered.  Might there be a problem coming with everything else having old statistics and one index having new?

    Edit:  One more thing I wanted to point out in case anyone didn't know - the ability to revert to old statistics. Managing Optimizer Statistics

  • Martin Preiss
    Martin Preiss Member Posts: 2,381 Gold Trophy

    it seems the most important difference between dbms_stats and the ALTER INDEX ... COMPUTE STATISTICS call is that the latter does not create statistics at all in 11.2:

    -- 11.2.0.1
    -- drop test table
    drop table t;
    
    -- create test table
    create table t
    as
    select rownum id
        , mod(rownum, 10) col1
      from dual
    connect by level <= 100000;
    
    -- delete a lot of rows
    create index t_idx on t(id);
    delete from t where col1 <= 5;
    commit;
    
    -- statistics after object creation and delete
    select index_name, num_rows, leaf_blocks, last_analyzed from user_indexes where index_name = 'T_IDX';
    
     INDEX_NAME                      NUM_ROWS LEAF_BLOCKS LAST_ANALYZED
    ------------------------------ ---------- ----------- -------------------
     T_IDX                             100000         222 04.08.2014 19:59:22
    
    -- table stats deleted
    exec dbms_stats.delete_table_stats(user, 't')
    
    select index_name, num_rows, leaf_blocks, last_analyzed from user_indexes where index_name = 'T_IDX';
    
     INDEX_NAME                      NUM_ROWS LEAF_BLOCKS LAST_ANALYZED
    ------------------------------ ---------- ----------- -------------------
     T_IDX
    
    -- alter index compute statistics;
    alter index t_idx compute statistics;
    
    select index_name, num_rows, leaf_blocks, last_analyzed from user_indexes where index_name = 'T_IDX';
    
     INDEX_NAME                      NUM_ROWS LEAF_BLOCKS LAST_ANALYZED
    ------------------------------ ---------- ----------- -------------------
     T_IDX
    
    -- dbms_stats
    exec dbms_stats.gather_table_stats(user, 't', cascade=>true)
    
    select index_name, num_rows, leaf_blocks, last_analyzed from user_indexes where index_name = 'T_IDX';
    
     INDEX_NAME                      NUM_ROWS LEAF_BLOCKS LAST_ANALYZED
    ------------------------------ ---------- ----------- -------------------
     T_IDX                              40000         222 04.08.2014 19:59:24
    

    So it seems that alter index ... compute statistics; does not recreate the deleted statistics. And when I issue the command after an dbms_stats call then the last_analyzed value is not changed (and the index statistics are also the same). Already in 10.2 the documentation stated: "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. "ALTER INDEX. In the documentation for 11g the clause is not mentioned, does not result in an error - but seems to be quite useless.

    Maybe it would be a good idea if someone checks this example to make sure that I does not talk utter nonsense...

    Regards

    Martin

    Zoltan Kecskemethy
  • Zoltan Kecskemethy
    Zoltan Kecskemethy Member Posts: 1,142 Gold Badge

    I have the very same results under 11g.

    [email protected]> select banner from v$version;
    Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
    PL/SQL Release 11.2.0.4.0 - Production
    CORE    11.2.0.4.0      Production
    TNS for Linux: Version 11.2.0.4.0 - Production
    NLSRTL Version 11.2.0.4.0 - Production
    [email protected]> create table t as select rownum id , mod(rownum, 10) col1 from dual connect by level <= 100000;
    Table created.
    [email protected]> create index t_idx on t(id);
    Index created.
    [email protected]> delete from t where col1 <= 5;
    60000 rows deleted.
    [email protected]> commit;
    Commit complete.
    [email protected]> select index_name, num_rows, leaf_blocks, last_analyzed from user_indexes where index_name = 'T_IDX';
    T_IDX
        100000          55 05-AUG-14
    [email protected]> exec dbms_stats.delete_table_stats(user, 't');
    PL/SQL procedure successfully completed.
    [email protected]> select index_name, num_rows, leaf_blocks, last_analyzed from user_indexes where index_name = 'T_IDX';
    T_IDX
    [email protected]> alter index t_idx compute statistics;
    Index altered.
    [email protected]> select index_name, num_rows, leaf_blocks, last_analyzed from user_indexes where index_name = 'T_IDX';
    T_IDX
    [email protected]> exec dbms_stats.gather_table_stats(user, 't', cascade=>true);
    PL/SQL procedure successfully completed.
    [email protected]> select index_name, num_rows, leaf_blocks, last_analyzed from user_indexes where index_name = 'T_IDX';
    T_IDX
        40000          55 05-AUG-14
    

    and the same behavior under 10gR2 (10.2.0.5.0) I have no 12c handy.

This discussion has been closed.