Oracle DB 물리 설계

DATA MODELING · STEP 4 PHYSICAL DESIGN

Oracle 물리 설계

논리 모델을 운영 가능한 Oracle 스키마로 — 데이터타입, 제약조건, 인덱스, 파티셔닝, 용량 산정

기준 · Oracle 19c 커리큘럼 · 10주차 난이도 · 중급

1. 물리 설계의 범위

영역	결정 사항
객체	테이블, 컬럼, 데이터타입, 제약조건, 시퀀스, 뷰
접근 경로	인덱스 종류·컬럼 순서, IOT, 클러스터
저장 구조	테이블스페이스, 파티셔닝, 압축, PCTFREE
운영	용량 산정, 보존·삭제 정책, 통계 수집, 백업 단위
표준	명명 규칙, 표준 단어·도메인 사전, COMMENT

2. 데이터타입 선택

2.1 주요 데이터타입 가이드

용도	권장	비권장	이유
가변 문자	VARCHAR2(n CHAR) 또는 VARCHAR2(n BYTE) 표준 통일	CHAR(n)	CHAR는 공백 패딩으로 비교 오류·공간 낭비
정수·금액	NUMBER(p,s)	NUMBER (정밀도 없음), FLOAT	도메인 검증, 금액 반올림 오류 방지
날짜	DATE	VARCHAR2(8) 'YYYYMMDD'	범위 조건·파티션 프루닝·날짜 연산 정확성
일시 (초 이하)	TIMESTAMP(6)	DATE + 별도 밀리초 컬럼	정밀도 내장
대용량 텍스트	CLOB (SecureFile)	LONG	LONG은 사용 중단(deprecated)
바이너리	BLOB (SecureFile)	LONG RAW	동일
여부 플래그	VARCHAR2(1) + CHECK ('Y','N')	제약 없는 문자	도메인 무결성

⚠ 묵시적 형변환 주의 — 조인·조건 컬럼의 타입이 다르면 Oracle은 한쪽에 TO_NUMBER 등을 적용하여 인덱스를 사용하지 못한다. 표준 도메인 사전으로 "같은 의미 = 같은 타입·길이"를 강제한다.

-- 나쁜 예: CUST_NO가 VARCHAR2인데 숫자로 비교 → 인덱스 미사용 (FILTER: TO_NUMBER("CUST_NO")=1001)
SELECT * FROM TB_CUSTOMER WHERE CUST_NO = 1001;

-- 좋은 예
SELECT * FROM TB_CUSTOMER WHERE CUST_NO = '1001';

3. 제약조건 설계

제약	역할	설계 포인트
NOT NULL	필수 속성	옵티마이저가 NOT NULL을 알면 더 좋은 실행계획 (예: COUNT(*)에 인덱스 사용)
PRIMARY KEY	주식별자	인덱스를 별도 생성 후 USING INDEX로 지정하면 테이블스페이스·로컬 여부 제어 가능
UNIQUE	보조식별자	인조 PK 사용 시 본질식별자에 필수
FOREIGN KEY	참조 무결성	FK 컬럼 인덱스 필수 검토, ON DELETE 정책 명시
CHECK	도메인 규칙	코드값·범위 검증, 단순 규칙만 (복잡한 규칙은 애플리케이션)

3.1 FK 인덱스가 중요한 이유

• 부모 행 DELETE 또는 PK UPDATE 시 자식 FK 컬럼에 인덱스가 없으면 Oracle은 자식 테이블 전체에 TM Lock(Share)을 건다 → 동시성 저하, 경합 발생
• 부모-자식 조인 시 NL 조인의 내부 테이블 접근 경로

-- 인덱스 없는 FK 찾기 (단일 컬럼 FK 기준 간이 점검)
SELECT c.owner, c.table_name, c.constraint_name, cc.column_name
  FROM dba_constraints  c
  JOIN dba_cons_columns cc
    ON cc.owner = c.owner AND cc.constraint_name = c.constraint_name
 WHERE c.constraint_type = 'R'
   AND c.owner = :schema
   AND NOT EXISTS (
         SELECT 1
           FROM dba_ind_columns ic
          WHERE ic.table_owner     = c.owner
            AND ic.table_name      = c.table_name
            AND ic.column_name     = cc.column_name
            AND ic.column_position = cc.position )
 ORDER BY c.table_name;

3.2 대량 적재 시 제약 관리

ALTER TABLE TB_ORDER_ITEM DISABLE CONSTRAINT FK_ORDER_ITEM_PROD;
-- ... 대량 적재 ...
ALTER TABLE TB_ORDER_ITEM ENABLE NOVALIDATE CONSTRAINT FK_ORDER_ITEM_PROD;  -- 신규 DML만 검증
ALTER TABLE TB_ORDER_ITEM ENABLE VALIDATE   CONSTRAINT FK_ORDER_ITEM_PROD;  -- 기존 데이터까지 검증

4. 인덱스 설계

4.1 인덱스 종류

종류	적합한 경우	주의
B*Tree	OLTP 일반 조회, 고유·고선택도 컬럼	기본
결합 인덱스	여러 조건 동시 사용	컬럼 순서가 성능을 결정
Function-Based	UPPER(EMAIL), CASE 식 조건	쿼리 식과 정확히 일치해야 사용
Bitmap	DW, 저선택도 컬럼, 읽기 위주	OLTP 금지 — DML 시 심각한 락 경합
Reverse Key	시퀀스 PK 우측 블록 경합(RAC) 완화	범위 검색 불가
IOT (Index-Organized Table)	PK로만 접근하는 좁은 테이블, 코드성	2차 인덱스 비용 증가
Local / Global (파티션)	Local: 파티션 관리 용이 / Global: 파티션 키 외 조건	Global은 파티션 DROP 시 UPDATE INDEXES 필요

4.2 결합 인덱스 컬럼 순서 원칙

1. 항상 사용되는 조건 컬럼을 앞에
2. = 조건 컬럼을 앞에, 범위(BETWEEN, LIKE, >) 조건 컬럼을 뒤에
3. 정렬(ORDER BY)·그룹핑 컬럼을 고려해 SORT 연산 제거
4. 선택도는 위 원칙 다음으로 고려 (선택도만으로 순서를 정하지 않는다)

-- 조회 패턴: WHERE CUST_NO = :b1 AND ORDER_DT BETWEEN :b2 AND :b3 ORDER BY ORDER_DT DESC
CREATE INDEX IX_ORDER_01 ON TB_ORDER (CUST_NO, ORDER_DT) LOCAL;   -- = 조건 → 범위 조건 순

4.3 RAC 환경 고려

• 시퀀스 기반 PK는 인덱스 우측 블록에 삽입이 집중 → gc buffer busy, enq: TX - index contention 발생 가능
• 대안: CACHE 크게 + NOORDER 시퀀스, Scalable Sequence(18c+ SCALE), Hash 파티션 인덱스, Reverse Key 인덱스

CREATE SEQUENCE SQ_ORDER_NO CACHE 1000 NOORDER SCALE EXTEND;

5. 파티셔닝

5.1 파티션 방식 선택

방식	기준	대표 용도
Range / Interval	값의 범위 (주로 날짜)	주문·이력·로그 — 기간 조회, 보존 기간 경과분 DROP
List	이산 값	지역, 법인, 상태 코드별 분리
Hash	해시 분산	경합 분산, 파티션 와이즈 조인
Composite	Range-Hash, Range-List 등	기간 관리 + 분산 동시 필요
Reference	부모 테이블 파티션 상속	주문(Range) → 주문상세 (파티션 키 컬럼 중복 없이)

5.2 예제 — 주문 / 주문상세 Reference 파티션

CREATE TABLE TB_ORDER (
    ORDER_NO      VARCHAR2(20) NOT NULL,
    ORDER_DT      DATE         NOT NULL,
    CUST_NO       VARCHAR2(10) NOT NULL,
    CONSTRAINT PK_ORDER PRIMARY KEY (ORDER_NO)
)
PARTITION BY RANGE (ORDER_DT) INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
( PARTITION P_202601 VALUES LESS THAN (DATE '2026-02-01') );

CREATE TABLE TB_ORDER_ITEM (
    ORDER_NO      VARCHAR2(20) NOT NULL,
    ITEM_SEQ      NUMBER(5)    NOT NULL,
    PROD_CD       VARCHAR2(10) NOT NULL,
    ORDER_QTY     NUMBER(7)    NOT NULL,
    CONSTRAINT PK_ORDER_ITEM PRIMARY KEY (ORDER_NO, ITEM_SEQ),
    CONSTRAINT FK_ORDER_ITEM_ORDER FOREIGN KEY (ORDER_NO) REFERENCES TB_ORDER
)
PARTITION BY REFERENCE (FK_ORDER_ITEM_ORDER);

-- 보존 기간 경과 파티션 정리 (자식 → 부모 순, 인덱스 유지)
ALTER TABLE TB_ORDER DROP PARTITION FOR (DATE '2026-01-15') UPDATE GLOBAL INDEXES;

💡 파티션 키 선정 기준 — ① 대부분의 핵심 SQL 조건에 등장하는가 (프루닝) ② 데이터 삭제·보존 단위와 일치하는가 ③ 값이 변경되지 않는가 (변경 시 ENABLE ROW MOVEMENT 필요, 행 이동 비용)

6. 용량 산정

6.1 산정 절차

1. 평균 행 길이(Average Row Length) 계산 — 컬럼별 평균 바이트 합 + 행 헤더(약 3~5바이트)
2. 블록당 행 수 = (블록크기 − 블록 헤더) × (1 − PCTFREE/100) ÷ 평균 행 길이
3. 필요 블록 수 = 예상 건수 ÷ 블록당 행 수
4. 인덱스 용량 = (키 길이 + ROWID 약 6~10바이트) × 건수 × 1.2~1.5 (여유)

6.2 계산 예시

항목	값
평균 행 길이	120 Byte
블록 크기 / PCTFREE	8 KB / 10%
블록당 행 수	(8,192 − 약 100) × 0.9 ÷ 120 ≈ 60행
일 증가 / 보존 기간	50만 건 / 3년 (약 5.5억 건)
테이블 용량	5.5억 ÷ 60 × 8 KB ≈ 약 70 GB

-- 샘플 데이터 적재 후 실측 기반 보정
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'TB_ORDER');
SELECT table_name, num_rows, avg_row_len, blocks FROM user_tables WHERE table_name = 'TB_ORDER';

-- DBMS_SPACE로 예상 용량 계산
DECLARE
  l_used  NUMBER;  l_alloc NUMBER;
BEGIN
  DBMS_SPACE.CREATE_TABLE_COST('USERS', 120, 550000000, 10, l_used, l_alloc);
  DBMS_OUTPUT.PUT_LINE('used=' || ROUND(l_used/1024/1024/1024,1) || 'GB, alloc=' || ROUND(l_alloc/1024/1024/1024,1) || 'GB');
END;
/

7. 명명 규칙 (예시 표준)

객체	규칙	예
테이블	TB_ + 영문 약어	TB_ORDER_ITEM
이력 테이블	TB_ + 원본 + _HIS	TB_CUSTOMER_HIS
PK / UK / FK	PK_테이블 / UK_테이블_n / FK_자식_부모	FK_ORDER_ITEM_ORDER
CHECK	CK_테이블_컬럼	CK_ORDER_STAT
인덱스	IX_테이블_nn / 유니크 UX_	IX_ORDER_01
시퀀스	SQ_컬럼	SQ_ORDER_NO
컬럼 접미어	_NO 번호, _CD 코드, _NM 명, _DT 일자, _YN 여부, _AMT 금액, _QTY 수량	ORDER_STAT_CD

8. 물리 설계 리뷰 체크리스트

✅ 배포 전 점검 항목 (펼치기)

• □ 모든 테이블·컬럼에 COMMENT가 등록되었는가
• □ 같은 의미의 컬럼이 모든 테이블에서 동일한 타입·길이인가
• □ 필수 컬럼에 NOT NULL이 지정되었는가
• □ 인조 PK 테이블의 본질식별자에 UNIQUE 제약이 있는가
• □ FK 컬럼에 인덱스가 있는가 (없다면 사유가 문서화되었는가)
• □ 인덱스가 핵심 SQL 기준으로 설계되었고 중복(선두 컬럼 포함 관계) 인덱스는 없는가
• □ 대용량 테이블의 파티션 키·보존 정책·삭제 방식이 정의되었는가
• □ RAC 환경에서 시퀀스 CACHE/NOORDER, 핫 블록 대책이 검토되었는가
• □ 테이블스페이스, 용량 산정, 통계 수집 정책이 정의되었는가
• □ DDL이 형상관리(Git 등)에 등록되었는가

이전 ← ERD 작성 실습 · 정규화 심화  |  처음으로 → 데이터 모델링 입문  |  시험 대비 → SQLD 데이터 모델링 기출 정리