물리 설계와 칼럼 속성

물리 설계는 논리 모델의 표와 열을 저장 장치에 둘 수 있는 꼴로 바꾸는 단계입니다. 열마다 자료형과 길이, 값을 비울 수 있는지, 비웠을 때 넣을 기본값을 정하고, 표가 차지할 저장 크기를 어림합니다.

논리 데이터 모델링과 품질 검증까지 정한 것은 '학생(학번, 이름, 학년, 학과코드)' 같은 표 이름과 열 이름뿐입니다. 이것만으로는 CREATE TABLE 문을 쓸 수 없습니다 — 학번이 숫자인지 글자인지, 이름을 몇 자까지 받을지, 학과가 정해지지 않은 학생을 받아도 되는지가 비어 있습니다. 설계 다섯 단계에서 이 빈자리를 채우는 단계가 물리적 설계입니다(데이터베이스 설계 단계). 아래 그림은 논리 모델의 열 이름 줄에 무엇이 한 겹씩 덧붙는지 보여 줍니다.

CREATE TABLE 학생 (  학번 ?,  이름 ?,  학년 ?,  학과코드 ?);

1열마다 네 가지를 정한다

열에 붙는 것을 칼럼 속성이라 합니다. 네 가지입니다.

Oracle의 NUMBER는 자릿수를 정하지 않으면 들어온 수에 맞춰 자리를 쓰므로, 숫자형에는 길이를 적지 않아도 됩니다.

NULL 허용은 업무 규칙에서 나옵니다. 학과가 정해지지 않은 학생을 받아야 하면 학과코드를 비울 수 있게 두고, 이름 없는 학생을 받지 않으려면 이름에 NOT NULL을 걸어 막습니다. 네 가지를 거는 문장은 DDL: CREATE·ALTER·DROP에서 배웠습니다.

2고정 길이와 가변 길이는 쓰는 자리가 다르다

문자형은 두 갈래입니다. 길이를 똑같이 10자로 정해도 짧은 값을 넣었을 때 차지하는 자리가 다릅니다.

이 데이터베이스는 한글 한 자를 3바이트로 담으므로 '김민수'는 9바이트입니다. 고정 길이는 남은 7자를 공백으로 채워 16바이트가 됩니다.

길이가 들쭉날쭉한 값에 고정 길이를 쓰면 짧은 값이 공백으로 자리를 낭비합니다. 정한 길이를 넘길 수도 없어, 길이는 업무에서 나올 수 있는 가장 긴 값을 받을 만큼 잡습니다.

3저장 크기를 어림한다 — 사이징

사이징은 데이터베이스가 쓸 저장 공간과 처리 능력을 미리 어림해 정하는 일입니다.

표의 크기 ≒ 한 행의 길이 × 행 수 × (1 + 여유율)

한 행의 길이는 열마다 정한 길이를 더한 값입니다. 실제 저장에는 행마다 붙는 머리말과 나중에 값이 길어질 때를 위해 남겨 두는 빈자리가 더 들기 때문에 여유율을 곱해 넉넉히 잡습니다.

미리 어림하는 까닭은 물리 설계가 다음 셋을 함께 봐야 하기 때문입니다.

셋은 서로 맞바꾸는 관계입니다. 자주 찾는 열에 인덱스를 더하면 응답 시간은 줄지만 저장 공간을 더 쓰고 트랜잭션 처리량은 떨어집니다(인덱스).

예제

결과 표는 Oracle에서 돌린 결과입니다. 블록마다 처음 데이터에서 시작합니다 — 앞 블록이 만들거나 바꾼 것은 남아 있지 않습니다.

정한 칼럼 속성을 다시 읽기

USER_TAB_COLUMNS는 내 표의 열과 그 속성을 보여 주는 시스템 카탈로그입니다. CHAR_LENGTH가 문자형의 길이, NULLABLE이 NULL 허용, DATA_DEFAULT가 기본값입니다.

SELECT COLUMN_NAME,
  DATA_TYPE,
  CHAR_LENGTH,
  NULLABLE,
  DATA_DEFAULT
FROM USER_TAB_COLUMNS
WHERE TABLE_NAME = '학생'
ORDER BY COLUMN_ID;

CHAR_LENGTH는 문자형의 길이를 세는 칸이라 숫자형인 학번·학년은 0입니다. NULLABLE이 Y인 열은 학과코드뿐이고, DATA_DEFAULT가 모두 NULL인 것은 기본값을 정한 열이 없다는 뜻입니다.

정한 길이를 넘기면

이름은 10자까지입니다. 11자 이름을 넣으면 Oracle은 ORA-12899 오류를 내고 행을 넣지 않습니다. 오류 메시지에 실제 길이와 정한 길이가 함께 나옵니다.

INSERT INTO 학생
  (학번, 이름, 학년)
VALUES (108,
    '김민수박서연이서준정하',
    1);

기본값을 정하면

학년에 기본값 1을 걸고, 학년을 적지 않은 행을 넣어 봅니다.

ALTER TABLE 학생
  MODIFY 학년 DEFAULT 1;
INSERT INTO 학생
  (학번, 이름, 학과코드)
VALUES (108, '나은비', 'CS');
SELECT 학번,
  이름,
  학년
FROM 학생
WHERE 학번 = 108;

학년 칸을 비운 채 넣었는데 1이 들어갔습니다. 기본값이 없는 열을 비우면 NULL이 들어가고, 그 열에 NOT NULL이 걸려 있으면 행이 들어가지 못합니다.

표의 저장 크기 어림하기

VSIZE는 값이 실제로 쓰는 바이트를 돌려줍니다. 열마다의 바이트를 더하면 한 행의 길이가 되고, 행마다 더하면 표가 지금 쓰는 바이트가 됩니다. NVL로 NULL인 칸을 0으로 바꿔 더합니다.

SELECT COUNT(*) AS 행수,
  SUM(VSIZE(학번)
    + VSIZE(이름)
    + VSIZE(학년)
    + NVL(VSIZE(학과코드), 0))
    AS 전체바이트
FROM 학생;

학생 여섯 행은 한 행이 16바이트(학번 3 + 이름 9 + 학년 2 + 학과코드 2)입니다. 학과코드가 비어 있는 강도현(107)만 14바이트라 16 × 6 + 14 = 110바이트입니다. 비워 둔 칸은 자리를 쓰지 않습니다.

열마다의 바이트는 한글 한 자가 3바이트, 문자 한 자가 1바이트이고, 숫자는 뒤에서부터 두 자리씩 묶어 묶음마다 1바이트에 머리말 1바이트를 더한 크기입니다. 학번 101은 1·01 두 묶음에 머리말이 붙어 3바이트, 학년 1은 한 묶음에 머리말이 붙어 2바이트입니다. 끝에 오는 00 묶음은 저장하지 않으므로 세 자리인 100도 2바이트입니다.

시험 포인트

• 필기: 물리 설계는 열마다 자료형·길이·NULL 허용·기본값을 정하고 저장 크기를 어림하는 단계입니다. 고정 길이 문자 타입은 값이 짧아도 남는 자리를 공백으로 채워 정한 길이만큼 쓰고, 가변 길이 문자 타입은 들어온 값만큼만 씁니다. 정한 길이를 넘는 값은 들어가지 못하고, 기본값은 값을 적지 않은 행에만 들어갑니다. 사이징은 한 행의 길이 × 행 수에 여유율을 곱해 어림합니다. 함께 보는 셋은 응답 시간·저장 공간 효율·트랜잭션 처리량이고, 하나를 좋게 하면 다른 하나가 나빠지는 맞바꾸는 관계로 나옵니다.
• 실기: 이름은 사이징(Sizing), 기본값(Default), 고정 길이 문자 타입(CHAR), 가변 길이 문자 타입(VARCHAR, Oracle은 VARCHAR2)으로 씁니다.

확인 문제

1. 학과 표의 건물 열을 CHAR(10 CHAR)로 정했습니다. 여기에 '공학관'을 넣으면 이 값이 쓰는 바이트로 알맞은 것을 고르세요. 한글 한 자는 3바이트로 봅니다.

• ① 3바이트
• ② 9바이트
• ③ 16바이트
• ④ 30바이트

2. 다음 (가)~(라)에 맞는 이름을 한글 이름과 영문으로 함께 쓰세요. 답을 끝까지 쓴 뒤 정답을 펼치세요.

• (가) 새 시스템을 열기 전에, 담을 데이터의 양과 처리할 일의 양을 어림해 저장 공간과 장비를 정했습니다.
• (나) 과목 표에 '개설여부' 열을 더하면서, 이 칸을 적지 않고 들어온 행에는 'Y'가 들어가게 걸어 두었습니다.
• (다) 학과코드 열에 쓴 자료형입니다. 'CS'를 넣어도 정해 둔 자리를 모두 차지하고 뒤가 공백으로 채워집니다.
• (라) 이름 열에 쓴 자료형입니다. '김민수'를 넣으면 세 자 몫만 차지하고 뒤에 공백이 붙지 않습니다.

3. 수강 표(학번, 과목코드, 점수)에는 비어 있는 칸이 없습니다. (1) 수강 표의 행 수와 전체 바이트를 함께 구하는 문장을 쓰고 결과를 쓰세요. (2) 같은 꼴의 행이 50만 개이고 여유율을 20%로 잡으면 어림 크기는 몇 바이트인지 쓰세요. 답을 끝까지 쓴 뒤 정답을 펼치세요.

SELECT COUNT(*) AS 행수,
  SUM(VSIZE(학번)
    + VSIZE(과목코드)
    + VSIZE(점수))
    AS 전체바이트
FROM 수강;

더 보기: 블록·스토리지처럼 '어디에 저장하나'는 이어지는 레슨에서 보고, 시간·공간 지역성은 3과목 운영체제 단원에서 다시 봅니다.