Eu ja tinha feito algumas linhas de codigo para o REBUILD do indice mas pesquisando encontrei um blog do Guilherme e achei interessante postar aqui a dica dele.

Essa package criada por mim possibilita a anlise de ínidices para saber se eles precisam sofrer o rebuild. Caso seja necessário, tem um procedimento que automatiza essa tarefa também.

CREATE
PACKAGE manutencao_indice
IS
PROCEDURE cria_tabela;

PROCEDURE analisa_indice(
dono IN VARCHAR2 DEFAULT USER,
indice IN VARCHAR2);

PROCEDURE analisa_schema
(dono IN VARCHAR2 DEFAULT USER);

PROCEDURE analisa_todos;

END; — Package spec
/

CREATE
PACKAGE BODY manutencao_indice

IS

PROCEDURE cria_tabela
IS
BEGIN
EXECUTE IMMEDIATE ‘CREATE TABLE ‘||USER||’.DBA$INDEX_STATS
(DONO VARCHAR2(30),
NOME_INDICE VARCHAR2(30),
PCT_DEL NUMBER(5,2),
QUANDO DATE)’;
END;

PROCEDURE analisa_indice(
dono IN VARCHAR2 DEFAULT USER,
indice IN VARCHAR2)
IS
sql_stmt VARCHAR2(200);
CURSOR cur_stats
IS
SELECT (b.del_lf_rows/(DECODE(b.lf_rows,0,1)))*100 pct_delete
FROM index_stats b
WHERE b.name = indice;

BEGIN
EXECUTE IMMEDIATE ‘ANALYZE INDEX ‘||dono||’.’||indice||’ VALIDATE STRUCTURE’;
FOR rec_stats IN cur_stats LOOP
sql_stmt :=’INSERT INTO ‘||USER||’.dba$index_stats VALUES (:1, :2, :3, :4)’;
EXECUTE IMMEDIATE sql_stmt USING dono, indice, rec_stats.pct_delete, SYSDATE;
END LOOP;
COMMIT;
END;

PROCEDURE analisa_schema
(dono IN VARCHAR2 DEFAULT USER)
IS
CURSOR cur_indices
IS
SELECT owner dono, index_name nome
FROM dba_indexes
WHERE owner = dono;
BEGIN
FOR rec_indices IN cur_indices LOOP
analisa_indice(rec_indices.dono, rec_indices.nome);
END LOOP;
END;

PROCEDURE analisa_todos
IS
CURSOR cur_indices
IS
SELECT owner dono, index_name nome
FROM dba_indexes;
BEGIN
FOR rec_indices IN cur_indices LOOP
analisa_indice(rec_indices.dono, rec_indices.nome);
END LOOP;
END;

PROCEDURE rebuild_index(
dono IN VARCHAR2 DEFAULT USER,
indice IN VARCHAR2)
IS
BEGIN
EXECUTE IMMEDIATE ‘ALTER INDEX ‘||dono||’.’||indice||’ REBUILD ONLINE’;
END;

END;
/

Fonte: http://oraclemais.blogspot.com/