Как узнать размер индекса в Oracle

Приложенный скрипт поможет определить физический размер таблицы и всех её индексов.



Проведем подготовительную работу для демонстрации запроса, а именно, создам таблицу и индекс.

create table all_objects_tab as select * from all_objects;

create index object_type_ndx on all_objects_tab(object_type);

create index object_id_ndx on all_objects_tab(object_id);

Результат:

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 
Connected as ora102
 
SQL> 
SQL> create table all_objects_tab as select * from all_objects;
 
Table created
SQL> create index object_type_ndx on all_objects_tab(object_type);
 
Index created
SQL> create index object_id_ndx on all_objects_tab(object_id);
 
Index created 
SQL> 

Скрипт, определяющий размеры:

select t.table_name table_name,
       t.table_size table_size_KB,
       i.index_name index_name,
       i.index_size index_size_KB
  from (select segment_name table_name, 
               ceil(sum(bytes) / 1024) table_size
          from dba_segments
         where owner like 'ORA102'
           and segment_type = 'TABLE'
           and segment_name = 'ALL_OBJECTS_TAB'
         group by segment_name) t
  left join dba_indexes ti
    on ti.table_name = t.table_name
  left join (select segment_name index_name,
                    ceil(sum(bytes) / 1024) index_size
               from dba_segments
              where owner like 'ORA102'
                and segment_type = 'INDEX'
              group by segment_name) i
    on i.index_name = ti.index_name
 order by i.index_size desc;

Результат:

TABLE_NAME         TABLE_SIZE_KB   INDEX_NAME          INDEX_SIZE_KB
------------------ --------------- ------------------- -------------
ALL_OBJECTS_TAB               3072 OBJECT_TYPE_NDX               640
ALL_OBJECTS_TAB               3072 OBJECT_ID_NDX                 512 

Подчищаем за собой:

drop index object_type_ndx;

drop index object_id_ndx;

drop table all_objects_tab;

Результат:

SQL> 
SQL> drop index object_type_ndx;
 
Index dropped
SQL> drop index object_id_ndx;
 
Index dropped
SQL> drop table all_objects_tab;
 
Table dropped
 
SQL>