Wednesday, June 23, 2021

How to list index sizes

For non-partitioned global indexes in a specific schema:
select i.index_name, s.tablespace_name,round(sum(bytes)/1024/1024/1024) "GB"
from dba_indexes i join dba_segments s
on (i.index_name = s.segment_name)
where i.owner='SH'
and i.partitioned='NO'
group by i.index_name,s.tablespace_name
order by 3 desc;
INDEX_NAME TABLESPACE_NAME GB
AN_EEG_IDX SH
2406
PK_EEG SH
994
UIDX_REG_EEG SH
502
IDX_REL_IDX SH
156

For non-partitioned global indexes on a specific table:
select i.index_name, s.tablespace_name,round(sum(bytes)/1024/1024/1024) "GB"
from dba_indexes i join dba_segments s
on (i.index_name = s.segment_name)
where i.owner='SH'
and i.partitioned='NO'
and i.table_name = 'ACCOUNTING'
group by i.index_name,s.tablespace_name
order by 2 desc;

INDEX_NAME TABLESPACE_NAME GB
IDX_ACC1 SH
120
PK_ACC SH
994

For partitioned indexes on a specific table:
select s.segment_name,round(sum(s.bytes)/1024/1024/1024,2) "GB" 
from dba_segments s
where segment_name IN (select unique index_name 
                        from dba_indexes 
                        where table_name='SALES' 
                        and PARTITIONED='YES'
                        and index_type <> 'LOB'
                        and owner='SH')
group by s.segment_name;

SEGMENT_NAME GB
IDX_UK1
28
IDX_ACC_UK2
78,24

For all partitioned indexes in a specific schema:
select i.index_name,i.table_name,s.tablespace_name,round(sum(s.bytes)/1024/1024/1024) "GB"
from dba_indexes i join dba_segments s
on (i.index_name = s.segment_name)
where i.owner='SH'
and i.partitioned='YES'
and i.index_type <> 'LOB'
group by i.index_name,i.table_name,s.tablespace_name
order by 2 ;

INDEX_NAME TABLE_NAME TABLESPACE_NAME GB
IDX_OP_AA ENTITY SH
260
IDX_OP_NA ENTITY_HISTORY SH
1082
IDX_VER_AA EVENT SH
28
IDX_VER_AA_HIST EVENT SH
78
IDX_WW_UK2 RELATION SH
226
IDX_RELNO RELATION_ENTITY SH
350

No comments:

Post a Comment