-- 테이블 스키마 선언별 ROW SIZE 산정
SELECT
c.class_name,
COUNT(a.class_name) AS count_column,
CAST(SUM(CASE
WHEN a.data_type = 'BIGINT' THEN 8.0
WHEN a.data_type = 'INTEGER' THEN 4.0
WHEN a.data_type = 'SMALLINT' THEN 2.0
WHEN a.data_type = 'FLOAT' THEN 4.0
WHEN a.data_type = 'DOUBLE' THEN 8.0
WHEN a.data_type = 'MONETARY' THEN 12.0
WHEN a.data_type = 'STRING' THEN a.prec
WHEN a.data_type = 'VARCHAR' THEN a.prec
WHEN a.data_type = 'NVARCHAR' THEN a.prec
WHEN a.data_type = 'CHAR' THEN a.prec
WHEN a.data_type = 'NCHAR' THEN a.prec
WHEN a.data_type = 'TIMESTAMP' THEN 8.0
WHEN a.data_type = 'DATE' THEN 4.0
WHEN a.data_type = 'TIME' THEN 4.0
WHEN a.data_type = 'DATETIME' THEN 4.0
WHEN a.data_type = 'BIT' THEN FLOOR(prec / 8.0)
WHEN a.data_type = 'BIT VARYING' THEN FLOOR(prec / 8.0)
ELSE 0
END) AS BIGINT) AS [size_column(byte)],
MAX(c.class_type) AS class_type,
MAX(c.partitioned) AS partitioned,
CONCAT(MAX(p.partition_type), MAX(p.partition_expr)) AS partition_info
FROM
db_class c
JOIN db_attribute a ON a.class_name = c.class_name
LEFT JOIN db_partition p ON p.class_name = a.class_name
WHERE
c.is_system_class = 'NO'
AND c.class_type = 'CLASS'
AND c.class_name <> '_cub_schema_comments'
GROUP BY
c.class_name;
-- 테이블 인덱스 선언별 ROW SIZE 산정
SELECT
i.class_name,
i.index_name,
COUNT(ik.index_name) AS ke_count_column,
CAST(SUM(CASE
WHEN a.data_type = 'BIGINT' THEN 8.0
WHEN a.data_type = 'INTEGER' THEN 4.0
WHEN a.data_type = 'SMALLINT' THEN 2.0
WHEN a.data_type = 'FLOAT' THEN 4.0
WHEN a.data_type = 'DOUBLE' THEN 8.0
WHEN a.data_type = 'MONETARY' THEN 12.0
WHEN a.data_type = 'STRING' THEN a.prec
WHEN a.data_type = 'VARCHAR' THEN a.prec
WHEN a.data_type = 'NVARCHAR' THEN a.prec
WHEN a.data_type = 'CHAR' THEN a.prec
WHEN a.data_type = 'NCHAR' THEN a.prec
WHEN a.data_type = 'TIMESTAMP' THEN 8.0
WHEN a.data_type = 'DATE' THEN 4.0
WHEN a.data_type = 'TIME' THEN 4.0
WHEN a.data_type = 'DATETIME' THEN 4.0
WHEN a.data_type = 'BIT' THEN FLOOR(prec / 8.0)
WHEN a.data_type = 'BIT VARYING' THEN FLOOR(prec / 8.0)
ELSE 0.0
END) AS BIGINT) AS [index_size_column(byte)]
FROM
db_index i
JOIN db_index_key ik ON ik.index_name = i.index_name
JOIN db_attribute a ON a.class_name = i.class_name
AND ik.key_attr_name = a.attr_name
WHERE
EXISTS (
SELECT
1
FROM
db_class c
WHERE
c.class_name = i.class_name
AND c.is_system_class = 'NO'
AND c.class_type = 'CLASS'
AND c.class_name <> '_cub_schema_comments'
)
GROUP BY
i.class_name,
i.index_name;