[SQL]Oracle 인덱스 용량 계산

인덱스 용량을 구하되, 해당 인덱스에 특정 컬럼이 포함된 인덱스만 추출하여 인덱스 용량을 계산함

 

SELECT 
    B.TABLE_NAME
    , A.SEGMENT_NAME
    , ROUND(SUM(BYTES)/1024/1024) "SIZE_MB"
    , A.SEGMENT_TYPE
FROM USER_SEGMENTS A, USER_INDEXES B
WHERE A.SEGMENT_NAME = B.INDEX_NAME
AND A.SEGMENT_TYPE IN ('INDEX','INDEX PARTITION')
AND B.TABLE_NAME IN ([테이블명])
AND B.INDEX_NAME IN (
                    SELECT INDEX_NAME 
                    FROM USER_IND_COLUMNS
                    WHERE COLUMN_NAME = [컬럼명]
                    AND TABLE_NAME IN ([테이블명])

                   )
GROUP BY B.TABLE_NAME, A.SEGMENT_NAME, A.SEGMENT_TYPE
;