SELECT owner,index_name,table_name,blevel,leaf_blocks,distinct_keys, 
       avg_data_blocks_per_key avg_data_blocks,clustering_factor cl_factor,
       num_rows,decode(num_rows,0,0,clustering_factor*100/num_rows) proc_clust,
       avg_leaf_blocks_per_key avg_leaf_blocks
 FROM dba_indexes WHERE owner ='UBC' AND blevel IS NOT null 
ORDER BY blevel desc,leaf_blocks desc 

Более точную информацию о конкретном индексе можно получить, если его проанализировать таким образом.

ANALYZE INDEX index_name VALIDATE STRUCTURE;

результаты анализа записываются в однострочную таблицу, которую можно просмотреть так :

SELECT * FROM INDEX_STATS

Для того, чтобы просмотреть расширенную информацию по всем индексам, нужно создать вспомогательную таблицу

CREATE TABLE ind_stats AS SELECT * FROM sys.INDEX_STATS;

и пропустить в базе ( очень желательно в нерабочее время ) такой анонимный блок:

DECLARE
CURSOR c_ind IS
  SELECT 'analyze index '||owner||'.'||index_name||' validate structure' txt_sql
    FROM DBA_INDEXES WHERE owner IN ('UBC') -- список нужных владельцев индексов
   ORDER BY owner,index_name;
BEGIN
  DBMS_OUTPUT.PUT_LINE(SYSDATE); 
  FOR cind IN c_ind LOOP
     begin 
       toad.execute_immediate(cind.txt_sql); -- !!! убедитесь,  что это у вас есть !!!
       INSERT INTO apt.ind_stats SELECT * FROM sys.INDEX_STATS;
     exception WHEN others THEN -- в случае, если индекс занят, вывести сообщение
       DBMS_OUTPUT.PUT_LINE( cind.txt_sql);
     END;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE(SYSDATE);
  END;   

Тут надо обратить внимание, что процедура execute_immediate появляется только после установки TOAD.

Для Oracle 8,9 можно воспользоваться родной аналогичной командой.

Ну и проматриваем результаты с акцентом на наиболее важные поля:

SELECT NAME, lf_rows, height, del_lf_rows, pct_used FROM apt.ind_stats
ORDER BY height DESC, lf_rows DESC

• количество уровней в индексе (height) >3 (или =3, но число записей <150 000 )
• много удаленных веток (del_lf_rows)
• маленький pct_used ( <80 )

желательно пересоздать индекс (опять таки лучше в нерабочее время :):

ALTER INDEX your_name_index REBUILD tablespace USERS

и сделать стандартный analyze:

ANALYZE INDEX your_name_index compute statistics

Полный перечень столбцов во вьюшке

SELECT i.TABLE_NAME,i.index_name,si.bytes size_ind, st.bytes size_tab FROM dba_indexes i,
	   (SELECT segment_name, sum(bytes) bytes 
	      FROM dba_extents WHERE owner='UBC' AND segment_type='INDEX'
	     GROUP BY segment_name) si,
	   (SELECT segment_name, sum(bytes) bytes
              FROM dba_extents WHERE owner='UBC' AND segment_type='TABLE'
             GROUP BY segment_name) st 
WHERE i.index_name=si.segment_name AND st.segment_name=i.TABLE_NAME AND st.bytes < si.bytes 

Индексы - кандидаты на изменение параметра PCTFREE.

Если индекс основан на столбце, который заполняется только строго по возростанию и не изменяется, например на основе последовательности, то его можно пересоздать с параметром PCTFREE=0.

Такие индексы можно поискать среди уникальных индексов на основе одного столбца :

SELECT di.index_name, di.table_name, di.pct_free 
  FROM DBA_INDEXES di, (SELECT index_name, table_name, count(*)
                          FROM DBA_IND_COLUMNS
                         GROUP BY index_name, table_name
                        HAVING count(*)=1) dc
 WHERE di.UNIQUENESS='UNIQUE' AND di.table_owner='UBC'
   AND di.index_name=dc.index_name AND di.table_name=dc.table_name
 

Изменить pct_free можно так:

ALTER INDEX name_index REBUILD tablespace USERS_S PCTFREE 0;

Этот же запрос можно использовать и для поиска сжатых уникальных индексов из одного столбца (нонсенс, но изредка бывает :) Для этого к запросу добавить

and compression='ENABLED'

Поиск индексов - кандидатов на перенос в таблспэйс для миниатюрных объектов базы данных (в моём случае до 40-64 К)

SELECT 'alter index '|| index_name||' rebuild tablespace users_ro_s STORAGE (INITIAL 4K);',
       table_name, distinct_keys, num_rows, bytes/1024
  FROM USER_INDEXES i, dba_segments s
 WHERE i.tablespace_name<>'USERS_RO_S' AND blevel<2 AND num_rows<300
   and segment_name=index_name

Из полученного результата нужно отсеять индексы из временных и индекс-организованных таблиц.

Индексы с малой избирательной способностью:

SELECT index_name, distinct_keys, num_rows, 
       to_char(decode(num_rows,0,1,num_rows)/decode(distinct_keys,0,1,distinct_keys),'999G999D00') rows_on_keys
  FROM user_indexes
 WHERE uniqueness<>'UNIQUE' and decode(num_rows,0,1,num_rows)/decode(distinct_keys,0,1,distinct_keys)>2
 ORDER BY decode(num_rows,0,1,num_rows)/decode(distinct_keys,0,1,distinct_keys)   desc

Чем меньше уникальных значений в индексе и чем больше на одно уникальное значение в индексе приходится строк в таблице, тем более бесполезнее становится индекс, так как избирательность его при этом уменьшается. Эти индексы - кандидаты на удаление, хотя с каждым индексом нужно решать вопрос индивидуально, особенно для больших таблиц с составными индексами.

Эти индексы, если они всё же нужны, также являются кандидатами на сжатие