Dbstudy: /* 8. 물리 설계 리뷰 체크리스트 */

2026-11-04T05:30:54Z

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

Dbstudy: /* 7. 명명 규칙 (예시 표준) */

2026-11-04T05:30:29Z

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

Dbstudy: /* 5.1 파티션 방식 선택 */

2026-11-04T05:30:16Z

5.1 파티션 방식 선택

Dbstudy: /* 3. 제약조건 설계 */

2026-11-04T05:30:02Z

3. 제약조건 설계

Dbstudy: /* 1. 물리 설계의 범위 */

2026-11-04T05:29:38Z

1. 물리 설계의 범위

Dbstudy: 새 문서:

DATA MODELING · STEP 4 PHYSICAL DESIGN

Oracle 물리 설계

2026-11-04T03:40:18Z

새 문서: <div style="background:linear-gradient(135deg,#3b1a4f 0%,#6c3483 55%,#8e44ad 100%);color:#ffffff;padding:30px 36px;border-radius:14px;margin-bottom:24px;box-shadow:0 4px 14px rgba(59,26,79,0.25);"> <div style="font-size:12px;letter-spacing:3px;opacity:0.8;margin-bottom:6px;">DATA MODELING · STEP 4 PHYSICAL DESIGN</div> <div style="font-size:28px;font-weight:700;line-height:1.3;">Oracle 물리 설계</div> <div style="font-size:15px;opacity:0.92;margin-top:10px;line-height:1.7;"...

새 문서
<div style="background:linear-gradient(135deg,#3b1a4f 0%,#6c3483 55%,#8e44ad 100%);color:#ffffff;padding:30px 36px;border-radius:14px;margin-bottom:24px;box-shadow:0 4px 14px rgba(59,26,79,0.25);">
<div style="font-size:12px;letter-spacing:3px;opacity:0.8;margin-bottom:6px;">DATA MODELING · STEP 4 PHYSICAL DESIGN</div>
<div style="font-size:28px;font-weight:700;line-height:1.3;">Oracle 물리 설계</div>
<div style="font-size:15px;opacity:0.92;margin-top:10px;line-height:1.7;">논리 모델을 운영 가능한 Oracle 스키마로 — 데이터타입, 제약조건, 인덱스, 파티셔닝, 용량 산정</div>
<div style="margin-top:16px;display:flex;flex-wrap:wrap;gap:8px;">
<span style="background:rgba(255,255,255,0.18);border:1px solid rgba(255,255,255,0.35);padding:4px 12px;border-radius:20px;font-size:12px;">기준 · Oracle 19c</span>
<span style="background:rgba(255,255,255,0.18);border:1px solid rgba(255,255,255,0.35);padding:4px 12px;border-radius:20px;font-size:12px;">커리큘럼 · 10주차</span>
<span style="background:rgba(255,255,255,0.18);border:1px solid rgba(255,255,255,0.35);padding:4px 12px;border-radius:20px;font-size:12px;">난이도 · 중급</span>
</div>
</div>

TOC

== 1. 물리 설계의 범위 ==
{| class="wikitable" style="width:100%;"
|- style="background:#3b1a4f;color:#fff;"
! 영역 !! 결정 사항
|-
| 객체 || 테이블, 컬럼, 데이터타입, 제약조건, 시퀀스, 뷰
|-
| 접근 경로 || 인덱스 종류·컬럼 순서, IOT, 클러스터
|-
| 저장 구조 || 테이블스페이스, 파티셔닝, 압축, PCTFREE
|-
| 운영 || 용량 산정, 보존·삭제 정책, 통계 수집, 백업 단위
|-
| 표준 || 명명 규칙, 표준 단어·도메인 사전, COMMENT
|}

== 2. 데이터타입 선택 ==
=== 2.1 주요 데이터타입 가이드 ===
{| class="wikitable" style="width:100%;font-size:13px;"
|- style="background:#f5eefa;"
! 용도 !! 권장 !! 비권장 !! 이유
|-
| 가변 문자 || <code>VARCHAR2(n CHAR)</code> 또는 <code>VARCHAR2(n BYTE)</code> 표준 통일 || <code>CHAR(n)</code> || CHAR는 공백 패딩으로 비교 오류·공간 낭비
|-
| 정수·금액 || <code>NUMBER(p,s)</code> || <code>NUMBER</code> (정밀도 없음), <code>FLOAT</code> || 도메인 검증, 금액 반올림 오류 방지
|-
| 날짜 || <code>DATE</code> || <code>VARCHAR2(8)</code> 'YYYYMMDD' || 범위 조건·파티션 프루닝·날짜 연산 정확성
|-
| 일시 (초 이하) || <code>TIMESTAMP(6)</code> || <code>DATE</code> + 별도 밀리초 컬럼 || 정밀도 내장
|-
| 대용량 텍스트 || <code>CLOB</code> (SecureFile) || <code>LONG</code> || LONG은 사용 중단(deprecated)
|-
| 바이너리 || <code>BLOB</code> (SecureFile) || <code>LONG RAW</code> || 동일
|-
| 여부 플래그 || <code>VARCHAR2(1)</code> + CHECK ('Y','N') || 제약 없는 문자 || 도메인 무결성
|}

<div style="border-left:5px solid #c0392b;background:#fdf1ef;padding:12px 18px;margin:14px 0;border-radius:0 8px 8px 0;">
⚠ '''묵시적 형변환 주의''' — 조인·조건 컬럼의 타입이 다르면 Oracle은 한쪽에 <code>TO_NUMBER</code> 등을 적용하여 '''인덱스를 사용하지 못한다'''. 표준 도메인 사전으로 "같은 의미 = 같은 타입·길이"를 강제한다.
</div>

<syntaxhighlight lang="sql">
-- 나쁜 예: 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';
</syntaxhighlight>

== 3. 제약조건 설계 ==
{| class="wikitable" style="width:100%;"
|- style="background:#3b1a4f;color:#fff;"
! 제약 !! 역할 !! 설계 포인트
|-
| NOT NULL || 필수 속성 || 옵티마이저가 NOT NULL을 알면 더 좋은 실행계획 (예: COUNT()에 인덱스 사용)
|-
| PRIMARY KEY || 주식별자 || 인덱스를 별도 생성 후 <code>USING INDEX</code>로 지정하면 테이블스페이스·로컬 여부 제어 가능
|-
| UNIQUE || 보조식별자 || 인조 PK 사용 시 본질식별자에 필수
|-
| FOREIGN KEY || 참조 무결성 || '''FK 컬럼 인덱스''' 필수 검토, <code>ON DELETE</code> 정책 명시
|-
| CHECK || 도메인 규칙 || 코드값·범위 검증, 단순 규칙만 (복잡한 규칙은 애플리케이션)
|}

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

<syntaxhighlight lang="sql">
-- 인덱스 없는 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;
</syntaxhighlight>

=== 3.2 대량 적재 시 제약 관리 ===
<syntaxhighlight lang="sql">
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; -- 기존 데이터까지 검증
</syntaxhighlight>

== 4. 인덱스 설계 ==
=== 4.1 인덱스 종류 ===
{| class="wikitable" style="width:100%;font-size:13px;"
|- style="background:#f5eefa;"
! 종류 !! 적합한 경우 !! 주의
|-
| BTree || OLTP 일반 조회, 고유·고선택도 컬럼 || 기본
|-
| 결합 인덱스 || 여러 조건 동시 사용 || 컬럼 순서가 성능을 결정
|-
| Function-Based || <code>UPPER(EMAIL)</code>, CASE 식 조건 || 쿼리 식과 정확히 일치해야 사용
|-
| Bitmap || DW, 저선택도 컬럼, 읽기 위주 || '''OLTP 금지''' — DML 시 심각한 락 경합
|-
| Reverse Key || 시퀀스 PK 우측 블록 경합(RAC) 완화 || 범위 검색 불가
|-
| IOT (Index-Organized Table) || PK로만 접근하는 좁은 테이블, 코드성 || 2차 인덱스 비용 증가
|-
| Local / Global (파티션) || Local: 파티션 관리 용이 / Global: 파티션 키 외 조건 || Global은 파티션 DROP 시 <code>UPDATE INDEXES</code> 필요
|}

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

<syntaxhighlight lang="sql">
-- 조회 패턴: 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; -- = 조건 → 범위 조건 순
</syntaxhighlight>

=== 4.3 RAC 환경 고려 ===
시퀀스 기반 PK는 인덱스 우측 블록에 삽입이 집중 → '''gc buffer busy''', '''enq: TX - index contention''' 발생 가능
* 대안: <code>CACHE</code> 크게 + <code>NOORDER</code> 시퀀스, '''Scalable Sequence'''(18c+ <code>SCALE</code>), Hash 파티션 인덱스, Reverse Key 인덱스

<syntaxhighlight lang="sql">
CREATE SEQUENCE SQ_ORDER_NO CACHE 1000 NOORDER SCALE EXTEND;
</syntaxhighlight>

== 5. 파티셔닝 ==
=== 5.1 파티션 방식 선택 ===
{| class="wikitable" style="width:100%;"
|- style="background:#3b1a4f;color:#fff;"
! 방식 !! 기준 !! 대표 용도
|-
| Range / Interval || 값의 범위 (주로 날짜) || 주문·이력·로그 — 기간 조회, 보존 기간 경과분 DROP
|-
| List || 이산 값 || 지역, 법인, 상태 코드별 분리
|-
| Hash || 해시 분산 || 경합 분산, 파티션 와이즈 조인
|-
| Composite || Range-Hash, Range-List 등 || 기간 관리 + 분산 동시 필요
|-
| Reference || 부모 테이블 파티션 상속 || 주문(Range) → 주문상세 (파티션 키 컬럼 중복 없이)
|}

=== 5.2 예제 — 주문 / 주문상세 Reference 파티션 ===
<syntaxhighlight lang="sql">
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;
</syntaxhighlight>

<div style="border:1px solid #e3d3ee;background:#fbf8fd;padding:12px 18px;border-radius:8px;margin:14px 0;">
💡 '''파티션 키 선정 기준''' — ① 대부분의 핵심 SQL 조건에 등장하는가 (프루닝) ② 데이터 삭제·보존 단위와 일치하는가 ③ 값이 변경되지 않는가 (변경 시 <code>ENABLE ROW MOVEMENT</code> 필요, 행 이동 비용)
</div>

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

=== 6.2 계산 예시 ===
{| class="wikitable" style="width:100%;"
|- style="background:#f5eefa;"
! 항목 !! 값
|-
| 평균 행 길이 || 120 Byte
|-
| 블록 크기 / PCTFREE || 8 KB / 10%
|-
| 블록당 행 수 || (8,192 − 약 100) × 0.9 ÷ 120 ≈ '''60행'''
|-
| 일 증가 / 보존 기간 || 50만 건 / 3년 (약 5.5억 건)
|-
| 테이블 용량 || 5.5억 ÷ 60 × 8 KB ≈ '''약 70 GB'''
|}

<syntaxhighlight lang="sql">
-- 샘플 데이터 적재 후 실측 기반 보정
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;
/
</syntaxhighlight>

== 7. 명명 규칙 (예시 표준) ==
{| class="wikitable" style="width:100%;"
|- style="background:#3b1a4f;color:#fff;"
! 객체 !! 규칙 !! 예
|-
| 테이블 || <code>TB_</code> + 영문 약어 || TB_ORDER_ITEM
|-
| 이력 테이블 || <code>TB_</code> + 원본 + <code>_HIS</code> || TB_CUSTOMER_HIS
|-
| PK / UK / FK || <code>PK_</code>테이블 / <code>UK_</code>테이블_n / <code>FK_</code>자식_부모 || FK_ORDER_ITEM_ORDER
|-
| CHECK || <code>CK_</code>테이블_컬럼 || CK_ORDER_STAT
|-
| 인덱스 || <code>IX_</code>테이블_nn / 유니크 <code>UX_</code> || IX_ORDER_01
|-
| 시퀀스 || <code>SQ_</code>컬럼 || SQ_ORDER_NO
|-
| 컬럼 접미어 || <code>_NO</code> 번호, <code>_CD</code> 코드, <code>_NM</code> 명, <code>_DT</code> 일자, <code>_YN</code> 여부, <code>_AMT</code> 금액, <code>_QTY</code> 수량 || ORDER_STAT_CD
|}

== 8. 물리 설계 리뷰 체크리스트 ==
<div class="mw-collapsible" style="border:1px solid #e3d3ee;border-radius:10px;padding:12px 18px;">
'''✅ 배포 전 점검 항목 (펼치기)'''
<div class="mw-collapsible-content">
* □ 모든 테이블·컬럼에 <code>COMMENT</code>가 등록되었는가
* □ 같은 의미의 컬럼이 모든 테이블에서 동일한 타입·길이인가
* □ 필수 컬럼에 <code>NOT NULL</code>이 지정되었는가
* □ 인조 PK 테이블의 본질식별자에 UNIQUE 제약이 있는가
* □ FK 컬럼에 인덱스가 있는가 (없다면 사유가 문서화되었는가)
* □ 인덱스가 핵심 SQL 기준으로 설계되었고 중복(선두 컬럼 포함 관계) 인덱스는 없는가
* □ 대용량 테이블의 파티션 키·보존 정책·삭제 방식이 정의되었는가
* □ RAC 환경에서 시퀀스 CACHE/NOORDER, 핫 블록 대책이 검토되었는가
* □ 테이블스페이스, 용량 산정, 통계 수집 정책이 정의되었는가
* □ DDL이 형상관리(Git 등)에 등록되었는가
</div>
</div>

<div style="margin-top:30px;padding:14px 20px;border-top:3px solid #3b1a4f;background:#faf6fc;border-radius:0 0 10px 10px;font-size:13px;color:#4a3a5a;">
'''이전''' ← [[ERD 작성 실습]] · [[정규화 심화]]  |  '''처음으로''' → [[데이터 모델링 입문]]  |  '''시험 대비''' → [[SQLD 데이터 모델링 기출 정리]]
</div>

[[분류:데이터 모델링]]
[[분류:Oracle]]
[[분류:물리 설계]]

Oracle DB 물리 설계 - 편집 역사

Dbstudy: /* 8. 물리 설계 리뷰 체크리스트 */

Dbstudy: /* 7. 명명 규칙 (예시 표준) */

Dbstudy: /* 5.1 파티션 방식 선택 */

Dbstudy: /* 3. 제약조건 설계 */

Dbstudy: /* 1. 물리 설계의 범위 */