How to Collect index Statistics

INDEX_STATS figures for multiple indexes, because a normal 'analyze index ... validate structure' always replaces the content of index_stats when it is run. Script below also adds the number of columns in the index to assist when looking at the compression statistics

create table IDXSTAT
as select * from index_stats;

alter table idxstat add colcnt number;

begin
for i in ( select index_name from dba_indexes
      where owner = '...') loop
execute immediate 'analyze index iips.'||i.index_name||'

validate structure';
insert into idxstat
select x.*, cnt from index_stats x,
( select count(*) cnt from dba_ind_columns
where index_owner = '...'
and index_name = i.index_name );
commit;
end loop;
end;
/

select name, LF_BLKS, OPT_CMPR_COUNT, OPT_CMPR_PCTSAVE, COLCNT
from idxstat