티베로 테이블 용량산정 쿼리

/* 티베로 테이블 데이터 타입별 항목크기 산정 */
WITH T1 AS (
    SELECT A.TABLE_NAME ,
	       A.COLUMN_NAME ,
	       A.DATA_TYPE ,
	       A.DATA_LENGTH ,
	       A.DATA_PRECISION,
	       A.DATA_SIZE,
	       A.COMMENTS,
	       CASE WHEN A.DATA_SIZE < 250 THEN 1
	            ELSE 0
	       END BELOW_250,
	       CASE WHEN A.DATA_SIZE >= 250 THEN 1
	            ELSE 0
	       END OVER_250
      FROM (
			SELECT A.TABLE_NAME ,
			       A.COLUMN_NAME ,
			       A.DATA_TYPE ,
			       A.DATA_LENGTH ,
			       NVL(A.DATA_PRECISION,A.DATA_LENGTH) AS DATA_PRECISION,
			       CASE WHEN A.DATA_TYPE = 'VARCHAR' THEN CEIL(A.DATA_LENGTH * 0.7)
			            WHEN A.DATA_TYPE = 'NUMBER' THEN CEIL(NVL(A.DATA_PRECISION,A.DATA_LENGTH)/2) + 1
			       ELSE A.DATA_LENGTH
			       END DATA_SIZE,
			       B.COMMENTS 
			FROM   dba_tab_columns A,
			       all_col_comments B
			WHERE  A.OWNER = B.OWNER
			AND    A.TABLE_NAME = B.TABLE_NAME
			AND    A.COLUMN_NAME = B.COLUMN_NAME
			AND    A.OWNER = 'NAQSRMS'  -- DB명
		--	AND A.TABLE_NAME = 'TN_MTGR_DMND_L'
	 ) A
	 
)
SELECT TABLE_NAME,
        SUM(DATA_SIZE) AS DATA_SIZE,
        SUM(BELOW_250) AS BELOW_250,
        SUM(OVER_250) AS OVER_250
  FROM T1
 GROUP BY TABLE_NAME;
 
 
 /* 티베로 인덱스 데이터 타입별 항목크기 산정 */
 
 WITH T1 AS (
    SELECT A.TABLE_NAME ,
           A.INDEX_NAME,
	       A.COLUMN_NAME ,
	       A.DATA_TYPE ,
	       A.DATA_LENGTH ,
	       A.DATA_PRECISION,
	       A.DATA_SIZE,
	       CASE WHEN A.DATA_SIZE < 250 THEN 1
	            ELSE 0
	       END BELOW_250,
	       CASE WHEN A.DATA_SIZE >= 250 THEN 1
	            ELSE 0
	       END OVER_250
      FROM (
		 SELECT IX.TABLE_NAME,
               IC.INDEX_NAME,
               IC.COLUMN_NAME,
               DC.DATA_TYPE,
               DC.DATA_LENGTH,
               NVL(DC.DATA_PRECISION,DC.DATA_LENGTH) AS DATA_PRECISION,
               IC.COLUMN_POSITION,
               IX.UNIQUENESS,
               CASE WHEN DC.DATA_TYPE = 'VARCHAR' THEN CEIL(DC.DATA_LENGTH * 0.7)
		            WHEN DC.DATA_TYPE = 'NUMBER' THEN CEIL(NVL(DC.DATA_PRECISION,DC.DATA_LENGTH)/2) + 1
		       ELSE DC.DATA_LENGTH
		       END DATA_SIZE
          FROM USER_INDEXES IX,
               USER_IND_COLUMNS IC,
               DBA_TAB_COLUMNS DC 
         WHERE IC.INDEX_NAME = IX.INDEX_NAME 
           AND IC.TABLE_OWNER = DC.OWNER
           AND IC.TABLE_NAME = DC.TABLE_NAME
           AND IC.COLUMN_NAME = DC.COLUMN_NAME
           AND IX.TABLE_OWNER = 'NAQSRMS' 
         --  AND IX.TABLE_NAME lIKE 'TB%'
    	) A
	 
)       
 SELECT INDEX_NAME,
        SUM(DATA_SIZE) AS DATA_SIZE,
        SUM(BELOW_250) AS BELOW_250,
        SUM(OVER_250) AS OVER_250
  FROM T1
 GROUP BY INDEX_NAME;