-
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.
-
-
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
-
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.
-
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
-
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.
-
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.
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|