Fragmentacion

COMO CORREGIR PROBLEMAS DE FRAGMENTACION DE INDICES

1) Identificar los indices dentro del TS con mayor expacio

select substr(segment_name,1,45)N, segment_type, extents,NEXT_EXTENT,round(bytes/(1024*1024),2)MB
from dba_segments
where segment_name = ‘HD_MOVHOMEBANKINGCONSULTA_U1’
and segment_type = 'INDEX' and owner = 'ODS’
order by extents desc;

NSEGMENT_TYPE EXTENTS NEXT_EXTENT MB
------------------------- ------------------ ---------- ----------- ----------
HD_SALDOSAVING_N1 INDEX 17838524288 9601.06

2) Luego se realiza un ANALYZE

SQL> analyze index ods.HD_SALDOSAVING_N1 validate structure;

Index analyzed.

SQL>

- De esta forma se valida la estructura del indiceLuego :

SQL> select name, blocks, lf_blks, br_blks, blocks-(lf_blks+br_blks) empty
2 from index_stats where name ='HD_SALDOSAVING_N1';

NAME BLOCKSLF_BLKS BR_BLKS EMPTY
------------------------------ ---------- ---------- ---------- ----------
HD_SALDOSAVING_N1 614468 613165 1282 21

SQL>

SQL>select name, btree_space, used_space, pct_used
2 from index_stats where name ='HD_SALDOSAVING_N1';

NAME BTREE_SPACE USED_SPACE PCT_USED------------------------------ ----------- ---------- ----------
HD_SALDOSAVING_N1 9946709060 348587459 4

SQL> select index_name, blevel,
2 decode(blevel,0,'OK BLEVEL',1,'OK BLEVEL',3 2,'OK BLEVEL',3,'OK BLEVEL',4,'OK BLEVEL','BLEVEL HIGH') OK
4 from dba_indexes
5 where index_name='HD_SALDOSAVING_N1';

INDEX_NAMEBLEVEL OK
------------------------------ ---------- -----------
HD_SALDOSAVING_N1 3 OK BLEVEL

SQL> select DEL_LF_ROWS*100/decode(LF_ROWS, 0, 1, LF_ROWS) PCT_DELETED,
2...