언밸런싱된 인덱스를 찾고 해단인덱스의 크기를 알아보자

#unbalance index & size 확인

 

SELECT A.owner, A.SEGMENT_NAME,
              ROUND(SUM(A.BYTES)/1024/1024) "SIZE_MB",
              A.SEGMENT_TYPE
FROM DBA_SEGMENTS A,
            DBA_INDEXES B
WHERE A.SEGMENT_NAME = B.INDEX_NAME
      AND A.SEGMENT_TYPE IN ('INDEX','INDEX PARTITION')
      --AND A.OWNER IN ('APPLSYS','XLA')
      AND A.segment_name  IN( select index_name from
      dba_indexes where blevel >=3 )
GROUP BY A.SEGMENT_NAME, A.SEGMENT_TYPE, A.OWNER
ORDER BY 3 DESC;