스키마 B — SQL BOOSTER 쇼핑몰 주문 : ERD·조감도·인덱스 설명서

계정: sqlp · 만드는 방법: 실습/00_SQLBOOSTER_실습스키마_setup.sql을 sqlp 계정으로 실행한다. 원서의 15GB 데이터 대신 CONNECT BY로 만든 축소판을 직접 생성한다. 도메인은 쇼핑몰 주문이다(지역, 상품, 고객, 주문, 주문상세, 상품평가 + 대용량 주문). 컬럼명과 타입은 원서 DDL을 그대로 따랐다.

1. ERD

mermaid

erDiagram
    M_RGN ||--o{ M_CUS : "1:N (RGN_ID)"
    M_CUS ||--o{ T_ORD : "1:N (CUS_ID)"
    M_CUS ||--o{ T_ITM_EVL : "1:N (CUS_ID)"
    T_ORD ||--o{ T_ORD_DET : "1:N (ORD_SEQ)"
    M_ITM ||--o{ T_ORD_DET : "1:N (ITM_ID)"
    M_ITM ||--o{ T_ITM_EVL : "1:N (ITM_ID)"
    M_ITM ||--o{ M_ITM_PRC_HIS : "1:N (ITM_ID)"

    M_RGN {
        VARCHAR2 RGN_ID PK
        VARCHAR2 RGN_NM
        NUMBER SRT_OD
    }
    M_ITM {
        VARCHAR2 ITM_ID PK
        VARCHAR2 ITM_NM
        VARCHAR2 ITM_TP
        NUMBER UNT_PRC
    }
    M_ITM_PRC_HIS {
        VARCHAR2 ITM_ID PK
        VARCHAR2 TO_YMD PK
        VARCHAR2 FR_YMD
        NUMBER UNT_PRC
    }
    M_CUS {
        VARCHAR2 CUS_ID PK
        VARCHAR2 CUS_NM
        VARCHAR2 RGN_ID
        VARCHAR2 GND_TP
        VARCHAR2 BTH_YMD
        VARCHAR2 CUS_GD
    }
    T_ORD {
        NUMBER ORD_SEQ PK
        VARCHAR2 CUS_ID
        DATE ORD_DT
        VARCHAR2 ORD_ST
        DATE PAY_DT
        NUMBER ORD_AMT
    }
    T_ORD_DET {
        NUMBER ORD_SEQ PK
        NUMBER ORD_DET_NO PK
        VARCHAR2 ITM_ID
        NUMBER ORD_QTY
        NUMBER UNT_PRC
    }
    T_ITM_EVL {
        VARCHAR2 ITM_ID PK
        NUMBER EVL_LST_NO PK
        VARCHAR2 CUS_ID
        DATE EVL_DT
        NUMBER EVL_PT
    }
    T_ORD_BIG {
        NUMBER ORD_SEQ
        VARCHAR2 CUS_ID
        DATE ORD_DT
        VARCHAR2 ORD_ST
        NUMBER ORD_AMT
    }

중요: 모든 관계는 논리적 관계다(FK 없음). DDL에는 테이블마다 PRIMARY KEY 제약만 있고, 외래키(FOREIGN KEY) 제약은 하나도 선언하지 않았다. 위의 1:N 선은 컬럼명(조인 키)이 같아서 데이터상으로 이어지는 논리 관계일 뿐이고, DB가 참조 무결성을 강제하지는 않는다.
T_ORD_BIG에는 어떤 키로도 연결선이 없다. PK조차 없는 독립(고립) 대용량 테이블이다. CUS_ID 값은 M_CUS와 같은 패턴이지만 제약도 인덱스도 전혀 없어서, 인덱스와 조인 성능 실습에만 쓴다.

텍스트(ASCII) 조감도

                         [ M_RGN ]  지역 8건
                         RGN_ID(PK)
                              │ 1:N (RGN_ID)
                              ▼
      [ M_ITM ] 상품 100건            [ M_CUS ] 고객 2,000건
      ITM_ID(PK)                      CUS_ID(PK)  (VARCHAR2! 'CUS_000123')
        │   │   │                       │            CUS_GD = A(1%)/B(9%)/C(90%)
        │   │   └────────────┐          │
        │   │ 1:N(ITM_ID)    1:N(ITM_ID)│ 1:N (CUS_ID)
        │   ▼                 ▼          ▼
        │ [M_ITM_PRC_HIS]  [ T_ITM_EVL ]   [ T_ORD ] 주문 100,000건
        │  ITM_ID,TO_YMD    ITM_ID,         ORD_SEQ(PK)
        │  (PK) 가격이력      EVL_LST_NO(PK)  ORD_ST = COMPLETE/PENDING/CANCEL
        │  100건             평가 5,000건       │ 1:N (ORD_SEQ)
        │                                       ▼
        │ 1:N (ITM_ID)                   [ T_ORD_DET ] 주문상세 ~200,000건
        └──────────────────────────────► ORD_SEQ,ORD_DET_NO(PK)
                                          (주문당 1~3건)

      ┌─────────────────────────────────────────────────────┐
      │ [ T_ORD_BIG ] 대용량 주문 1,000,000건                  │
      │  ★ PK 없음 · 인덱스 전혀 없음 · 모든 관계 미연결(고립)    │
      │  CUS_ID VARCHAR2 → 6·7장 인덱스/조인 성능 실습 전용       │
      └─────────────────────────────────────────────────────┘

2. 테이블 조감도

테이블	의미	PK	대략 행수	핵심 컬럼	관계
M_RGN	지역(마스터)	`RGN_ID`	8	RGN_ID(VARCHAR2 'RGN01'), RGN_NM(SEOUL…JEJU), SRT_OD	M_CUS.RGN_ID의 부모(논리)
M_ITM	상품(마스터)	`ITM_ID`	100	ITM_ID('ITM00001'), ITM_NM, ITM_TP(TP_A~D), UNT_PRC	T_ORD_DET / T_ITM_EVL / M_ITM_PRC_HIS의 부모(논리)
M_ITM_PRC_HIS	상품 가격이력	`ITM_ID, TO_YMD`	100	TO_YMD(VARCHAR2(8) '20231231'), FR_YMD, UNT_PRC	M_ITM.ITM_ID의 자식(논리)
M_CUS	고객(마스터)	`CUS_ID`	2,000	CUS_ID VARCHAR2 'CUS_000123', RGN_ID, GND_TP(M/F), BTH_YMD, CUS_GD A/B/C	M_RGN의 자식 / T_ORD · T_ITM_EVL의 부모(논리)
T_ORD	주문	`ORD_SEQ`	100,000	ORD_SEQ(NUMBER), CUS_ID, ORD_DT(DATE), ORD_ST, PAY_DT, PAY_TP, ORD_AMT, PAY_AMT	M_CUS의 자식 / T_ORD_DET의 부모(논리)
T_ORD_DET	주문상세	`ORD_SEQ, ORD_DET_NO`	~200,000	ITM_ID, ORD_QTY, UNT_PRC(주문당 1~3건)	T_ORD · M_ITM의 자식(논리)
T_ITM_EVL	상품평가	`ITM_ID, EVL_LST_NO`	5,000	CUS_ID, EVL_DSC, EVL_DT(DATE), EVL_PT	M_ITM · M_CUS의 자식(논리)
T_ORD_BIG	대용량 주문	없음(★)	1,000,000	ORD_SEQ, CUS_ID(VARCHAR2), ORD_DT, ORD_ST, ORD_AMT	연결 없음(고립). 인덱스 실습용

데이터 분포 메모(DDL 기준):
CUS_GD 편향: A(MOD 100=0) 1% · B(MOD 10=0) 9% · C 90%. 등급 조건마다 선택도가 얼마나 다른지 실습한다.
ORD_ST 편향: CANCEL(MOD 50=0) 2% · PENDING(MOD 10=0) ~8% · COMPLETE 90%. 상태 조건도 한쪽으로 치우쳐 있다.
ORD_DT 분포: T_ORD는 2023-01-01부터 730일(2년), T_ORD_BIG는 2022-01-01부터 1095일(3년)에 걸쳐 있다.
CUS_ID 타입 주의: 스키마 B는 VARCHAR2(40)이고 'CUS_000123' 형식이다. 스키마 A의 cust_id(NUMBER)와 다르므로 조인이나 형변환을 실습할 때 헷갈리면 안 된다.

3. 인덱스·키 & 튜닝 포인트

현재 존재하는 인덱스(= PK가 만든 고유 인덱스뿐)

DDL에는 따로 만드는 CREATE INDEX가 하나도 없다. PRIMARY KEY 제약이 자동으로 만든 고유 인덱스만 있다.

테이블	자동 생성 인덱스(PK)	컬럼 구성
M_RGN	PK_M_RGN	(RGN_ID)
M_ITM	PK_M_ITM	(ITM_ID)
M_ITM_PRC_HIS	PK_M_ITM_PRC_HIS	(ITM_ID, TO_YMD) 복합
M_CUS	PK_M_CUS	(CUS_ID)
T_ORD	PK_T_ORD	(ORD_SEQ)
T_ORD_DET	PK_T_ORD_DET	(ORD_SEQ, ORD_DET_NO) 복합
T_ITM_EVL	PK_T_ITM_EVL	(ITM_ID, EVL_LST_NO) 복합
T_ORD_BIG	없음	(PK · 인덱스 전무)

의도적으로 "없는" 것 → 실습으로 직접 만든다

T_ORD_BIG(1,000,000건): PK도 인덱스도 일부러 비워 두었다. 6장과 7장 실습에서 인덱스를 직접 만들고, Full Scan과 Index Range Scan의 Buffers와 Cost가 전후로 어떻게 달라지는지 비교한다.
- 단일/복합 인덱스 후보: (CUS_ID), (ORD_DT), (ORD_ST, ORD_DT), (CUS_ID, ORD_DT) 등.
외래키(FK) 없음: 부모-자식 관계가 모두 논리적이다. FK가 없으면 옵티마이저가 조인 제거(Join Elimination) 같은 일부 변환을 하지 못하므로, FK가 있고 없음에 따라 실행계획이 어떻게 달라지는지도 실습한다.
자식 쪽 조인 키에 인덱스 없음: T_ORD.CUS_ID, T_ORD_DET.ITM_ID, T_ITM_EVL.CUS_ID 등에는 인덱스가 없다. NL 조인에서 자식 쪽 탐색이 Full Scan으로 풀리는 현상을 관찰하고, 인덱스를 추가하기 전과 후를 비교한다.

무엇을 실습하나 (테마)

데이터 편향(Skew): CUS_GD(A/B/C)와 ORD_ST(COMPLETE/PENDING/CANCEL)의 극단적인 분포로 히스토그램, 바인드 변수 Peeking, 선택도 오판을 실습한다.
클러스터링 팩터(CF): ORD_SEQ는 INSERT 순서(=물리 순서)와 정렬 순서가 같아서 CF가 좋다. CUS_ID나 ORD_DT로 만든 인덱스는 CF가 흩어져 있다. 이 둘의 인덱스 효율 차이를 실습한다.
대용량 처리(T_ORD_BIG): Full Scan과 인덱스를 비교하고, 파티션 없이 100만 행을 스캔해 보고, 인덱스를 추가한 효과를 측정한다.
복합키·결합 인덱스: T_ORD_DET(ORD_SEQ, ORD_DET_NO)와 M_ITM_PRC_HIS(ITM_ID, TO_YMD)에서 선두 컬럼을 조건에 쓰는지에 따라 액세스가 어떻게 달라지는지 본다.
VARCHAR2 키 조인: 'CUS_000123' 같은 문자 키로 조인하거나 범위 조건을 걸 때의 동작과 묵시적 형변환의 위험을 본다(스키마 A의 NUMBER 키와 비교).
계층/이력 처리: M_ITM_PRC_HIS의 기간(FR_YMD~TO_YMD)으로 특정 시점의 단가를 찾는 이력 조회 패턴을 익힌다.

4. DB에서 확인하는 쿼리

sql

-- (1) 테이블 목록·행수·블록수 (통계는 setup 의 GATHER_TABLE_STATS 로 수집됨)
SELECT table_name, num_rows, blocks, avg_row_len
FROM   user_tables
WHERE  table_name IN ('M_RGN','M_ITM','M_ITM_PRC_HIS','M_CUS',
                      'T_ORD','T_ORD_DET','T_ITM_EVL','T_ORD_BIG')
ORDER  BY num_rows;

-- (2) 컬럼 구성·타입·NULL 여부
SELECT table_name, column_name, data_type, data_length, data_precision, data_scale, nullable
FROM   user_tab_columns
WHERE  table_name IN ('M_RGN','M_ITM','M_ITM_PRC_HIS','M_CUS',
                      'T_ORD','T_ORD_DET','T_ITM_EVL','T_ORD_BIG')
ORDER  BY table_name, column_id;

-- (3) 제약(PK 등) — FK가 정말 없는지 확인 (R = 외래키)
SELECT table_name, constraint_name, constraint_type, status
FROM   user_constraints
WHERE  table_name IN ('M_RGN','M_ITM','M_ITM_PRC_HIS','M_CUS',
                      'T_ORD','T_ORD_DET','T_ITM_EVL','T_ORD_BIG')
ORDER  BY table_name, constraint_type;
-- constraint_type 에 'R'(FK)이 한 건도 없으면 모든 관계가 논리적임을 확인.

-- (4) 인덱스 목록 — T_ORD_BIG 는 0건이어야 함
SELECT table_name, index_name, uniqueness
FROM   user_indexes
WHERE  table_name IN ('M_RGN','M_ITM','M_ITM_PRC_HIS','M_CUS',
                      'T_ORD','T_ORD_DET','T_ITM_EVL','T_ORD_BIG')
ORDER  BY table_name, index_name;

-- (5) 인덱스 컬럼 구성(복합키 순서 확인)
SELECT index_name, column_position, column_name
FROM   user_ind_columns
WHERE  table_name IN ('M_ITM_PRC_HIS','T_ORD_DET','T_ITM_EVL')
ORDER  BY index_name, column_position;

-- (6) 인덱스 클러스터링 팩터(CF) 확인 — 인덱스를 만든 뒤 비교
SELECT index_name, leaf_blocks, distinct_keys, clustering_factor, num_rows
FROM   user_indexes
WHERE  table_name = 'T_ORD_BIG';

-- (7) 데이터 편향 직접 확인 (히스토그램 판단 근거)
SELECT cus_gd, COUNT(*) FROM M_CUS GROUP BY cus_gd ORDER BY cus_gd;   -- A/B/C
SELECT ord_st, COUNT(*) FROM T_ORD GROUP BY ord_st ORDER BY ord_st;  -- COMPLETE/PENDING/CANCEL

-- 참고: 파티션 테이블은 없으므로 user_part_tables 는 0건(확인용)
SELECT table_name, partitioning_type FROM user_part_tables;

스키마 B — SQL BOOSTER 쇼핑몰 주문 : ERD·조감도·인덱스 설명서 ​

1. ERD ​

텍스트(ASCII) 조감도 ​

2. 테이블 조감도 ​

3. 인덱스·키 & 튜닝 포인트 ​

현재 존재하는 인덱스(= PK가 만든 고유 인덱스뿐) ​

의도적으로 "없는" 것 → 실습으로 직접 만든다 ​

무엇을 실습하나 (테마) ​

4. DB에서 확인하는 쿼리 ​

스키마 B — SQL BOOSTER 쇼핑몰 주문 : ERD·조감도·인덱스 설명서

1. ERD

텍스트(ASCII) 조감도

2. 테이블 조감도

3. 인덱스·키 & 튜닝 포인트

현재 존재하는 인덱스(= PK가 만든 고유 인덱스뿐)

의도적으로 "없는" 것 → 실습으로 직접 만든다

무엇을 실습하나 (테마)

4. DB에서 확인하는 쿼리