Estimate stats on table and indexes...
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Estimate stats on table and indexes...

Hybrid View

  1. #1
    Join Date
    Mar 2002
    Posts
    12
    Hello again all,

    could anyone tell me if doing

    analyze table ... estimate statistics sample x percent

    does an estimate or a compute on the associated indexes?

    Thanks,
    Noel.

  2. #2
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    Yes, it does.

  3. #3
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    Example about tis post:

    SQL> select index_name, last_analyzed from user_indexes where table_name = 'EMPS';
    INDEX_NAME LAST_ANAL
    ------------------------------ ---------
    EMPS_PK 16-AUG-02
    IDX_DEP_ID_EMP 16-AUG-02
    IDX_FIRST_NAME 16-AUG-02
    IDX_JOB_ID_EMP 16-AUG-02
    IDX_U_LAST_NAME 16-AUG-02

    SQL> analyze table emps delete statistics;
    SQL> select index_name, last_analyzed from user_indexes where table_name = 'EMPS';

    INDEX_NAME LAST_ANAL
    ------------------------------ ---------
    EMPS_PK
    IDX_DEP_ID_EMP
    IDX_FIRST_NAME
    IDX_JOB_ID_EMP
    IDX_U_LAST_NAME

    SQL> analyze table emps compute statistics;

    Table analyzed.
    SQL> select index_name, last_analyzed from user_indexes where table_name = 'EMPS';

    INDEX_NAME LAST_ANAL
    ------------------------------ ---------
    EMPS_PK 16-AUG-02
    IDX_DEP_ID_EMP 16-AUG-02
    IDX_FIRST_NAME 16-AUG-02
    IDX_JOB_ID_EMP 16-AUG-02
    IDX_U_LAST_NAME 16-AUG-02

  4. #4
    Join Date
    Mar 2002
    Posts
    12
    Thanks Shestakov,

    The question is, does it do an estimate or a compute on the index if you do an estimate on the table?

    Also does it do a compute on the index if you do a compute on the table?

    Thanks,
    Noel.

  5. #5
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    See example:

    SQL> select index_name, last_analyzed from USER_INDEXES where table_name = 'EMPS';then:
    analyze TABLE emps delete statistics;
    then:
    elect index_name, last_analyzed from USER_INDEXES where table_name = 'EMPS';
    and so on...

    i analyzed TABLE statistics and show result of INDEX statistics.
    this is answer on ur question.

    in addition:
    SQL> select index_name from user_indexes where table_name = 'EMPS';

    INDEX_NAME
    ------------------------------
    EMPS_PK
    IDX_DEP_ID_EMP
    IDX_FIRST_NAME
    IDX_JOB_ID_EMP
    IDX_U_LAST_NAME

  6. #6
    Join Date
    Mar 2002
    Posts
    12
    Sorry Shestakov, I'm not being as clear as I could be about my question.

    When you ESTIMATE stats on a table, does it ESTIMATE the stats on the indexes or does it COMPUTE the stats on the indexes?

    And, when you COMPUTE the stats on a table, does it COMPUTE the stats on the indexes or does it ESTIMATE the stats?

    Appologies for the confusion.

    Thanks,
    Noel.

  7. #7
    Join Date
    Mar 2001
    Posts
    144
    depending on which version of Oracle you are using may get into this bug with the analyze statement:

    http://metalink.oracle.com/metalink/...1&p_showHelp=1

    It says for 8.0.5 but it applies to 8.1.7 too and in some weird cases to 9.0.1

    Also, don't use 50 percent if you intend to use estimate statistics as this turns the analyze into a compute instead:

    http://metalink.oracle.com/metalink/...1&p_showHelp=1

    Also there are issues with analyze on IOT's and partitioned tables and some other issues about how analyze gathers it's sampling. Found that when I attempted to use it in my 8.1.7.3 environment.

    Suggest using dbms_stats instead.

  8. #8
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    1. compute statistics on table ALWAYS compute statistics on INDEXES
    2. in 8i and higher estimate statistics on table ALWAYS estimate statistics on INDEXES.

    U may check it (it simple):

    SQL> analyze table emps delete statistics;

    Table analyzed.

    SQL> analyze table emps compute statistics;

    Table analyzed.

    SQL> select index_name, sample_size, last_analyzed from user_indexes where table_name = 'EMPS';

    INDEX_NAME SAMPLE_SIZE LAST_ANAL
    ------------------------------ ----------- ---------
    EMPS_PK 0 16-AUG-02
    IDX_DEP_ID_EMP 0 16-AUG-02
    IDX_FIRST_NAME 0 16-AUG-02
    IDX_JOB_ID_EMP 0 16-AUG-02
    IDX_U_LAST_NAME 0 16-AUG-02

    SQL> analyze table emps delete statistics;

    Table analyzed.

    SQL> analyze table emps estimate statistics sample 30 percent;

    Table analyzed.

    SQL> select index_name, sample_size, last_analyzed from user_indexes where table_name = 'EMPS';

    INDEX_NAME SAMPLE_SIZE LAST_ANAL
    ------------------------------ ----------- ---------
    EMPS_PK 30 16-AUG-02
    IDX_DEP_ID_EMP 30 16-AUG-02
    IDX_FIRST_NAME 30 16-AUG-02
    IDX_JOB_ID_EMP 30 16-AUG-02
    IDX_U_LAST_NAME 30 16-AUG-02

    SQL> select index_name, sample_size, last_analyzed from user_indexes where table_name = 'EMPS';

    In first case we had SAMPLE_SIZE = 0 (compute statistics)
    In second we had SAMPLE_SIZE = 30 (estimate statistics (30%))

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width