SQL> select * from part_range; C1 C2 C3 ---------- -- -- 1 xx a 150 yy b 150 yy e 150 yy f 250 ww c SQL> select table_name,partition_name,high_value from user_tab_partitions where table_name='PART_RANGE'; TABLE_NAME PARTITION_NAME HIGH_VALUE ------------------------------ -------------------- -------------------- PART_RANGE P1 100 PART_RANGE P2 200 PART_RANGE P3 MAXVALUE SQL> SQL> create index part_range_idx on part_range(c3) local; Index created. SQL> select index_name,partition_name from user_ind_partitions where index_name='PART_RANGE_IDX'; INDEX_NAME PARTITION_NAME ------------------------------ -------------------- PART_RANGE_IDX P1 PART_RANGE_IDX P2 PART_RANGE_IDX P3 SQL> select name,partition_name,distinct_keys from index_stats; no rows selected SQL> analyze index part_range_idx validate structure; Index analyzed. SQL> select name,partition_name,distinct_keys from index_stats; NAME PARTITION_NAME DISTINCT_KEYS ------------------------------ -------------------- ------------- PART_RANGE_IDX P3 1 SQL> insert into part_range values(260,'ww','d'); 1 row created. SQL> analyze index part_range_idx validate structure; Index analyzed. SQL> -- Analyze index without the 'partition' cluase always gives you the last partition. SQL> select name,partition_name,distinct_keys from index_stats; NAME PARTITION_NAME DISTINCT_KEYS ------------------------------ -------------------- ------------- PART_RANGE_IDX P3 2 SQL> -- Specify partition SQL> analyze index part_range_idx partition(p2) validate structure; Index analyzed. SQL> select name,partition_name,distinct_keys from index_stats; NAME PARTITION_NAME DISTINCT_KEYS ------------------------------ -------------------- ------------- PART_RANGE_IDX P2 3 SQL> SQL> create bitmap index part_range_bi on part_range(c2) local; Index created. SQL> select index_name,partition_name from user_ind_partitions where index_name='PART_RANGE_BI'; INDEX_NAME PARTITION_NAME ------------------------------ -------------------- PART_RANGE_BI P2 PART_RANGE_BI P3 PART_RANGE_BI P1 SQL> analyze index part_range_bi validate structure; Index analyzed. SQL> -- It seems that index_stats is meant for B*Tree indexes only, the distinct_keys SQL> -- in this view after analyzing a bitmap index does not make sense. SQL> select name,partition_name,distinct_keys from index_stats; NAME PARTITION_NAME DISTINCT_KEYS ------------------------------ -------------------- ------------- PART_RANGE_BI P3 1 SQL> insert into part_range values(260,'pp','d'); 1 row created. SQL> analyze index part_range_bi validate structure; Index analyzed. SQL> select name,partition_name,distinct_keys from index_stats; NAME PARTITION_NAME DISTINCT_KEYS ------------------------------ -------------------- ------------- PART_RANGE_BI P3 3 SQL> insert into part_range values(270,'dd','d'); 1 row created. SQL> analyze index part_range_bi validate structure; Index analyzed. SQL> select name,partition_name,distinct_keys from index_stats; NAME PARTITION_NAME DISTINCT_KEYS ------------------------------ -------------------- ------------- PART_RANGE_BI P3 4 SQL> insert into part_range values(270,'dd','d'); 1 row created. SQL> analyze index part_range_bi validate structure; Index analyzed. SQL> select name,partition_name,distinct_keys from index_stats; NAME PARTITION_NAME DISTINCT_KEYS ------------------------------ -------------------- ------------- PART_RANGE_BI P3 3 SQL> SQL> drop table part_range; Table dropped. SQL> SQL> spool off