Tag Archives: index fregmentation

Find index larger than their corresponding tables in oracle

Top 5 Indexes bigger than their corresponding Tables

Find the indexes largest than table and need to rebuild it.


set head on
set lines 200
col "Table" format a30
col "Index" format a30
col "Owner" format a20
col tab_gb format 999999.99
col ind_gb format 999999.99
select * from (
select a.owner, a.segment_name "Table", a.tab_gb, b.index_name "Index", b.ind_gb
from
(select owner,segment_name, round(sum(bytes/(1024*1024*1024)),2) TAB_GB
from dba_segments where segment_type=apos;TABLEapos;
and owner not in (apos;SYSapos;,apos;SYSTEMapos;)
group by owner,segment_name) a,
(select s.owner,i.table_name, i.index_name, round(sum(bytes/(1024*1024*1024)),2) IND_GB
from dba_indexes i, dba_segments s
where s.segment_type=apos;INDEXapos;
and s.segment_name=i.index_name
and s.owner not in (apos;SYSapos;,apos;SYSTEMapos;)
group by s.owner,i.table_name, i.index_name) b
where a.owner=b.owner
and a.segment_name=b.table_name
and a.tab_gb < b.ind_gb
order by b.ind_gb desc
) where rownum<6;
set head off

For rebuild index:

-- For offline rebuild
alter index index_name rebuild;

--For online rebuild
alter index index_name rebuild online;

--Rebuild in new tablespace
alter index index_name rebuild tablespace newtablespace;