Verificar se Indices devem passar por Rebuild

(1) -- CRIAR A TABELA ONDE ARMAZENAMENTOS AS INFORMAÇÕES

CREATE TABLE MMED_INDICE_STATUS (INDEX_NAME VARCHAR(100),PCT_DELETED NUMBER,PCT_USED NUMBER,BLOCK NUMBER,DTEXECUAO DATE DEFAULT SYSDATE) ;

ALTER TABLE MMED_INDICE_STATUS ADD CONSTRAINT PK_MMED_IND_STATUS PRIMARY KEY (INDEX_NAME,DTEXECUAO) ;


(2) -- RODAR A QUERY ABAIXO E PEGAR O RESULTADO DELA PARA EXECUAR

SELECT COMM
FROM(
SELECT 1 AS ID,
ROWNUM AS ID2,
'ANALYZE INDEX ' ||INDEX_NAME || ' VALIDATE STRUCTURE ; ' AS COMM
FROM DBA_INDEXES
WHERE OWNER = '&user'

UNION ALL

SELECT 2 AS ID,
ROWNUM ID2,
'INSERT INTO MMED_INDICE_STATUS SELECT NAME,TRUNC(100*(NVL(DEL_LF_ROWS,1)/NVL(LF_ROWS,1))) PCT_DELETED, PCT_USED ,BLOCKS ,SYSDATE FROM INDEX_STATS ;' AS COMM
FROM DBA_INDEXES
WHERE OWNER = '&user'
ORDER BY ID2,ID
)

(3) -- EXECUTAR O RESULTADO DO PASSO 3 E DAR COMMIT .

COMMIT ;



(4) -- VERIFICAR OS INDICES COM MAIS DE 30 % DE FRAGMENTAÇÃO

SELECT DISTINCT A.* ,B.TABLE_NAME
FROM MMED_INDICE_STATUS A
JOIN DBA_INDEXES B ON B.INDEX_NAME = A.INDEX_NAME
WHERE PCT_DELETED > 20 AND PCT_DELETED < 100
ORDER BY PCT_DELETED DESC


(5) -- AJUSTA OS INDICES QUE FORAM ENCONTADOS COM FRAGMENTAÇÃO MAIOR QUE 20 %

-- RODAR A QUERY ABEIXO E DEPOIS EXECUTAR O RESULTADO

SELECT DISTINCT 'ALTER INDEX ' || A.INDEX_NAME || ' REBUILD TABLESPACE TSP_CLINICA_INDEX ; ' AS RESULTADO
FROM MMED_INDICE_STATUS A
JOIN DBA_INDEXES B ON B.INDEX_NAME = A.INDEX_NAME
WHERE PCT_DELETED > 20 AND PCT_DELETED < 100