Hello,
I am wondering if ESTIMATE_PERCENT plays any role while statistics are gathered on indexes.
Based on my small test case below, I could not see it being considered. Am I mis-understanding something here?
SQL> select * from v$version ;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SQL> create table t1 nologging as select * from all_objects ;
Table created.
SQL> alter table t1 add constraint T1_PK primary key (object_id) ;
Table altered.
SQL> create index t1_idx on t1(created) nologging ;
Index created.
SQL> select index_name, leaf_blocks, sample_size, last_analyzed from user_indexes where table_name = 'T1' ;
INDEX_NAME LEAF_BLOCKS SAMPLE_SIZE LAST_ANAL
------------------------------ ----------- ----------- ---------
T1_IDX 108 40631 18-AUG-10
T1_PK 85 40631 18-AUG-10
SQL> select count(*) from t1 ;
COUNT(*)
----------
40631
SQL> select table_name, blocks, num_rows, sample_size, last_analyzed from user_tables where table_name = 'T1' ;
TABLE_NAME BLOCKS NUM_ROWS SAMPLE_SIZE LAST_ANAL
------------------------------ ---------- ---------- ----------- ---------
T1
SQL> exec dbms_stats.gather_table_stats(user, 'T1', estimate_percent=>0.01, cascade=>true) ;
PL/SQL procedure successfully completed.
SQL> select table_name, blocks, num_rows, sample_size, last_analyzed from user_tables where table_name = 'T1' ;
TABLE_NAME BLOCKS NUM_ROWS SAMPLE_SIZE LAST_ANAL
------------------------------ ---------- ---------- ----------- ---------
T1 585 40692 3391 18-AUG-10
SQL> select index_name, leaf_blocks, sample_size, last_analyzed from user_indexes where table_name = 'T1' ;
INDEX_NAME LEAF_BLOCKS SAMPLE_SIZE LAST_ANAL
------------------------------ ----------- ----------- ---------
T1_IDX 108 40631 18-AUG-10
T1_PK 85 40631 18-AUG-10
SQL> exec dbms_stats.gather_index_stats(user, 'T1_IDX', estimate_percent=>0.01) ;
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_index_stats(user, 'T1_PK', estimate_percent=>0.01) ;
PL/SQL procedure successfully completed.
SQL> select index_name, leaf_blocks, sample_size, last_analyzed from user_indexes where table_name = 'T1' ;
INDEX_NAME LEAF_BLOCKS SAMPLE_SIZE LAST_ANAL
------------------------------ ----------- ----------- ---------
T1_IDX 108 40631 18-AUG-10
T1_PK 85 40631 18-AUG-10
I found the same results on 10.2.0.4 as well.