데이터 베이스를 관리 하다보면 현재 테이블의 스키마 정보를 토대로
각 테이블 별 ROW 사이즈 및 용량을 산정하기 위한 작업이 필요할 때가 있다.
이때 편리하게 용량 산정을 위한 쿼리를 생각해 보았다.
테이블명, 컬럼수, 실제_스키마_ROW_사이즈(byte)가 기본 정보이며
특정 컬럼이 테이블 ROW 사이즈를 많이 잡아 먹는지에 대한 추가정보(예제에는 100 PREC 이상)
와 파티션이 된 테이블인지와 파티션 컬럼이 무었인지 확인 하게 되어 있다.
SELECT
c.class_name,
COUNT(*) AS count_column,
CAST(SUM(CASE
WHEN "data_type" = 'BIGINT' THEN 8.0
WHEN "data_type" = 'INTEGER' THEN 4.0
WHEN "data_type" = 'SMALLINT' THEN 2.0
WHEN "data_type" = 'FLOAT' THEN 4.0
WHEN "data_type" = 'DOUBLE' THEN 8.0
WHEN "data_type" = 'MONETARY' THEN 12.0
WHEN "data_type" = 'STRING' THEN a.prec
WHEN "data_type" = 'VARCHAR' THEN a.prec
WHEN "data_type" = 'NVARCHAR' THEN a.prec
WHEN "data_type" = 'CHAR' THEN a.prec
WHEN "data_type" = 'NCHAR' THEN a.prec
WHEN "data_type" = 'TIMESTAMP' THEN 8.0
WHEN "data_type" = 'DATE' THEN 4.0
WHEN "data_type" = 'TIME' THEN 4.0
WHEN "data_type" = 'DATETIME' THEN 4.0
WHEN "data_type" = 'BIT' THEN FLOOR(prec / 8.0)
WHEN "data_type" = 'BIT VARYING' THEN FLOOR(prec / 8.0)
ELSE 0
END) AS BIGINT) AS [size_column(byte)],
SUM(CASE
WHEN "data_type" = 'STRING' THEN 1
WHEN "data_type" = 'VARCHAR' THEN 1
WHEN "data_type" = 'NVARCHAR' THEN 1
WHEN "data_type" = 'NCHAR' THEN 1
WHEN "data_type" = 'BIT VARYING' THEN 1
ELSE 0
END) AS count_size_over_column,
v.cols AS size_over_columns,
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 AND a.from_class_name IS NULL
LEFT JOIN db_partition p ON p.class_name = a.class_name
LEFT JOIN (
SELECT class_name, GROUP_CONCAT(cols) AS cols
FROM (
SELECT class_name, CONCAT(attr_name,' ', [data_type], '(', prec,')') AS cols
FROM db_attribute
WHERE [data_type] IN ('STRING', 'VARCHAR', 'NVARCHAR', 'NCHAR', 'BIT VARYING')
AND prec >= 100
) z
GROUP BY class_name
) v ON v.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,
v.cols;
위의 예제 쿼리로 CUBRID 설치 시 demodb의 ROW 사이즈별 용량은 다음과 같다.
class_name | count_column | size_column(byte) | count_size_over_column | partitioned | partition_info |
|
|
athlete | 5 | 78 | 2 |
| NO |
|
|
code | 3 | 7 | 1 |
| NO |
|
|
event | 5 | 109 | 2 |
| NO |
|
|
game | 7 | 24 | 0 |
| NO |
|
|
history | 5 | 63 | 3 |
| NO |
|
|
nation | 4 | 83 | 3 |
| NO |
|
|
olympic | 8 | 1632 | 5 | introduction STRING(1500) | NO |
|
|
participant | 5 | 19 | 0 |
| NO |
|
|
record | 6 | 38 | 2 |
| NO |
|
|
stadium | 6 | 161 | 2 | address STRING(100) | NO |
|
|
전체 테이블 UPDATE시 SELECT, UPDATE를 통한 INDEX SCAN방식으로...
