스키마 A — SCOTT + 주문(기본 실습) · ERD · 조감도 · 인덱스 설명서

실행 계정: sqlp · 만드는 방법: Oracle_실습환경_구축가이드.md의 Quick Start를 따라 접속한 뒤 00_sample_schema_setup.sql을 통째로 실행한다(수십 초에서 1~2분).

1. ERD

mermaid

erDiagram
    DEPT ||--o{ EMP : "1:N (deptno)"
    EMP  ||--o{ EMP : "1:N (mgr→empno 자기참조)"
    CUSTOMERS ||--o{ ORDERS : "1:N (cust_id) · 논리FK"
    PRODUCTS  ||--o{ ORDERS : "1:N (prod_id) · 논리FK"

    DEPT {
        NUMBER deptno PK
        VARCHAR2 dname
        VARCHAR2 loc
    }
    EMP {
        NUMBER empno PK
        VARCHAR2 ename
        VARCHAR2 job
        NUMBER mgr
        DATE hiredate
        NUMBER sal
        NUMBER comm
        NUMBER deptno FK
    }
    SALGRADE {
        NUMBER grade
        NUMBER losal
        NUMBER hisal
    }
    PRODUCTS {
        NUMBER prod_id PK
        VARCHAR2 prod_name
        VARCHAR2 category
        NUMBER list_price
    }
    CUSTOMERS {
        NUMBER cust_id PK
        VARCHAR2 cust_name
        VARCHAR2 grade
        VARCHAR2 city
        DATE reg_date
    }
    ORDERS {
        NUMBER order_id PK
        NUMBER cust_id
        NUMBER prod_id
        DATE order_date
        VARCHAR2 status
        NUMBER qty
        NUMBER amount
    }

관계 메모
DEPT 1:N EMP: EMP.deptno → DEPT.deptno. 물리 FK가 걸려 있다(fk_emp_dept).
EMP 1:N EMP: EMP.mgr → EMP.empno. 자기참조(계층) 관계다. FK 제약은 없지만 논리적으로 계층을 이룬다(KING은 mgr이 NULL인 최상위). CONNECT BY와 재귀 WITH 실습에 쓴다.
CUSTOMERS 1:N ORDERS: ORDERS.cust_id → CUSTOMERS.cust_id. 논리적 관계다(FK는 걸지 않았다).
PRODUCTS 1:N ORDERS: ORDERS.prod_id → PRODUCTS.prod_id. 논리적 관계다(FK는 걸지 않았다).
SALGRADE: 어떤 테이블과도 키 관계가 없는 독립 테이블이다. EMP.sal BETWEEN SALGRADE.losal AND SALGRADE.hisal 형태로 비등가(범위) 조인하는 대상이라 ERD에는 선을 긋지 않았다.

텍스트(ASCII) 조감도

                        [ 개념 학습용 (SCOTT) — 소형 ]
   +-----------+   FK(deptno)    +-----------------------+
   |   DEPT    |1 ------------- N|         EMP           |---+ mgr→empno
   | (4행)     |                 | (14행, 자기참조 계층) |<--+ (계층/자기조인)
   | PK deptno |                 | PK empno              |
   +-----------+                 | sal ──┐               |
                                 +-------│---------------+
                                         │ BETWEEN losal..hisal (범위조인)
                                 +-------▼--------+
                                 |   SALGRADE     |  (5행, 독립·비키)
                                 | grade/lo/hisal |
                                 +----------------+

                        [ 튜닝 실습용 (주문) — 대형 ]
   +----------------+                                   +----------------+
   |   CUSTOMERS    |1                                 1|    PRODUCTS    |
   | (20,000행)     |                                   | (2,000행)      |
   | PK cust_id     |                                   | PK prod_id     |
   | grade A1/B9/C90|                                   | category 5종   |
   | city 6종       |                                   +-------┬--------+
   +-------┬--------+                                           │
           │ N (cust_id, 논리FK)            (prod_id, 논리FK) N │
           │              +----------------------+             │
           +------------> |        ORDERS        | <-----------+
                          | (200,000행)          |
                          | PK order_id          |
                          | status COMP/PEND/CAN |
                          | order_date(~2년)     |
                          | amount/qty (무인덱스) |
                          +----------------------+

2. 테이블 조감도

테이블	의미	PK	대략 행수	핵심 컬럼	관계
DEPT	부서(SCOTT 고전)	`pk_dept(deptno)`	4	`deptno`, `dname`, `loc`	EMP의 부모(1:N, deptno)
EMP	사원(SCOTT 고전 · 계층)	`pk_emp(empno)`	14	`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`	DEPT의 자식(FK deptno) · EMP 자기참조(mgr→empno) · sal로 SALGRADE와 범위 조인
SALGRADE	급여 등급표(독립 · 키 없음)	없음	5	`grade`, `losal`, `hisal`	키 관계 없음. EMP.sal BETWEEN losal..hisal 비등가 조인
PRODUCTS	상품	`pk_products(prod_id)`	2,000	`prod_id`, `prod_name`, `category`(5종), `list_price`	ORDERS의 부모(1:N, prod_id, 논리FK)
CUSTOMERS	고객	`pk_customers(cust_id)`	20,000	`cust_id`, `cust_name`, `grade`(A/B/C 편향), `city`(6종), `reg_date`	ORDERS의 부모(1:N, cust_id, 논리FK)
ORDERS	주문(대용량 팩트)	`pk_orders(order_id)`	200,000	`order_id`, `cust_id`, `prod_id`, `order_date`(~2년), `status`(편향), `qty`, `amount`	CUSTOMERS · PRODUCTS의 자식(논리FK)

데이터 분포(편향): 히스토그램 실습의 핵심

컬럼	분포	생성 규칙
`CUSTOMERS.grade`	A ≈ 1% / B ≈ 9% / C ≈ 90%	`MOD(level,100)=0→A`, `MOD(level,10)=0→B`, else `C`
`CUSTOMERS.city`	6종 균등(각 ≈ 1/6)	`MOD(level,6)` → SEOUL/BUSAN/INCHEON/DAEGU/DAEJEON/GWANGJU
`CUSTOMERS.reg_date`	약 1000일 균등	`DATE '2022-01-01' + MOD(level,1000)`
`PRODUCTS.category`	5종 균등(각 ≈ 1/5)	`MOD(level,5)` → ELECTRONICS/BOOK/FASHION/FOOD/TOY
`ORDERS.status`	COMPLETE ≈ 90% / PENDING ≈ 8% / CANCEL ≈ 2%	`MOD(level,50)=0→CANCEL`, `MOD(level,10)=0→PENDING`, else `COMPLETE`
`ORDERS.order_date`	약 2년(730일) 균등	`DATE '2024-01-01' + MOD(level,730)`
`ORDERS.cust_id` / `prod_id`	1~20000 / 1~2000 순환 균등	`MOD(level,20000)+1` / `MOD(level,2000)+1`

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

생성되는 인덱스 (PK 5개 + 보조 3개 = 총 8개)

DDL을 돌리면 실제로 생기는 인덱스는 다음과 같다. PK 제약 5개가 고유 인덱스를 하나씩 자동으로 만들고, 보조 인덱스 3개는 DDL에 명시해서 만든다.

인덱스	대상	종류	용도/실습 포인트
`pk_dept`	`DEPT(deptno)`	PK(고유)	기본
`pk_emp`	`EMP(empno)`	PK(고유)	자기참조 계층 조인의 inner
`pk_products`	`PRODUCTS(prod_id)`	PK(고유)	ORDERS→PRODUCTS NL 조인에서 inner 쪽 진입
`pk_customers`	`CUSTOMERS(cust_id)`	PK(고유)	ORDERS→CUSTOMERS NL 조인에서 inner 쪽 진입
`pk_orders`	`ORDERS(order_id)`	PK(고유)	단건 조회
`orders_x_cust`	`ORDERS(cust_id)`	보조(비고유)	특정 고객의 주문을 조회할 때 인덱스 vs Full Scan
`orders_x_date`	`ORDERS(order_date)`	보조(비고유)	기간 범위 검색, 클러스터링 팩터(CF) 관찰
`cust_x_city`	`CUSTOMERS(city)`	보조(비고유)	균등 분포 컬럼의 인덱스 선택도 실습

SALGRADE에는 일부러 PK도 인덱스도 두지 않았다. grade NUMBER, losal, hisal 모두 제약이 없다.

일부러 인덱스를 만들지 않은 컬럼 (DDL 주석에 명시)

ORDERS.status(편향 90/8/2): 편향된 컬럼에 히스토그램을 더해 옵티마이저의 카디널리티 추정을 실습한다.
ORDERS.amount: 인덱스 없이 범위 조회와 집계를 해서, Full Scan과 인덱스를 비교하는 기준선으로 쓴다.
ORDERS.prod_id, ORDERS.qty: 인덱스가 없다. prod_id는 논리FK라서 조인할 때 인덱스가 없으면 어떤 영향이 있는지 관찰한다.
CUSTOMERS.grade(편향 1/9/90): 히스토그램(method_opt=>'FOR ALL COLUMNS SIZE AUTO'로 수집)의 효과를 실습한다.

무엇을 실습하나

데이터 분포 편향 + 히스토그램: CUSTOMERS.grade, ORDERS.status. AUTO 히스토그램이 생기면 grade='A'(1%)와 'C'(90%)의 실행계획이 달라지는지 관찰한다.
인덱스 vs Full Scan: 인덱스가 있는 cust_id/order_date/city와 인덱스가 없는 status/amount를 비교한다. 조건이 고르는 행의 비율(선택도)이 클수록 Full Scan이 유리하고, 작을수록 인덱스가 유리하다. 인덱스가 없는 컬럼은 Full Scan밖에 길이 없다.
클러스터링 팩터(CF): orders_x_date는 적재 순서(MOD(level,730))와 물리 순서가 어긋나서 CF가 나쁘다. 이때 인덱스 범위 검색의 효율이 어떤지 관찰한다.
조인 방식(NL vs Hash): ORDERS(20만) ⨝ CUSTOMERS(2만)/PRODUCTS(2천). PK 인덱스가 있는지, 결과 집합이 얼마나 큰지에 따라 옵티마이저가 NL과 Hash 중 무엇을 고르는지 본다.
비등가(범위) 조인: EMP.sal BETWEEN SALGRADE.losal AND SALGRADE.hisal.
계층/자기조인: EMP.mgr → EMP.empno(CONNECT BY PRIOR, 재귀 WITH).
결합 인덱스 직접 설계: 가이드에서는 4주차에 (cust_id, order_date) 같은 결합 인덱스를 만들어 단일 인덱스와 비교한다(지금은 만들어 두지 않았다).

4. DB에서 확인하는 쿼리

sql

-- 접속: sqlp/sqlp

-- (1) 테이블 행수 (통계 기준 + 실측)
SELECT table_name, num_rows, blocks, last_analyzed
FROM   user_tables
WHERE  table_name IN ('DEPT','EMP','SALGRADE','PRODUCTS','CUSTOMERS','ORDERS')
ORDER  BY num_rows;

-- 실측 행수
SELECT 'DEPT' t, COUNT(*) c FROM dept
UNION ALL SELECT 'EMP',       COUNT(*) FROM emp
UNION ALL SELECT 'SALGRADE',  COUNT(*) FROM salgrade
UNION ALL SELECT 'PRODUCTS',  COUNT(*) FROM products
UNION ALL SELECT 'CUSTOMERS', COUNT(*) FROM customers
UNION ALL SELECT 'ORDERS',    COUNT(*) FROM orders
ORDER BY t;

-- (2) 인덱스 목록 + 구성 컬럼 + 클러스터링 팩터
SELECT i.table_name, i.index_name, i.uniqueness,
       i.clustering_factor, i.num_rows,
       ic.column_position, ic.column_name
FROM   user_indexes i
JOIN   user_ind_columns ic
       ON ic.index_name = i.index_name
WHERE  i.table_name IN ('DEPT','EMP','SALGRADE','PRODUCTS','CUSTOMERS','ORDERS')
ORDER  BY i.table_name, i.index_name, ic.column_position;

-- (3) 제약(PK/FK) 확인 — 물리 FK는 EMP.deptno 하나뿐
SELECT c.table_name, c.constraint_name, c.constraint_type,  -- P=PK, R=FK
       cc.column_name, c.r_constraint_name
FROM   user_constraints c
JOIN   user_cons_columns cc ON cc.constraint_name = c.constraint_name
WHERE  c.table_name IN ('DEPT','EMP','SALGRADE','PRODUCTS','CUSTOMERS','ORDERS')
  AND  c.constraint_type IN ('P','R')
ORDER  BY c.table_name, c.constraint_type;

-- (4) 컬럼 통계 + 히스토그램 유무 (편향 실습)
SELECT table_name, column_name, num_distinct, num_nulls, histogram
FROM   user_tab_col_statistics
WHERE  table_name IN ('CUSTOMERS','ORDERS')
  AND  column_name IN ('GRADE','CITY','STATUS','AMOUNT','PROD_ID','CUST_ID','ORDER_DATE')
ORDER  BY table_name, column_name;

-- (5) 편향 분포 직접 확인
SELECT grade, COUNT(*) FROM customers GROUP BY grade ORDER BY grade;    -- A/B/C ≈ 1/9/90
SELECT status, COUNT(*) FROM orders   GROUP BY status ORDER BY status;  -- COMPLETE/PENDING/CANCEL

이 스키마에는 파티션 테이블이 없으므로 user_part_tables를 조회해도 빈 결과가 나온다.

스키마 A — SCOTT + 주문(기본 실습) · ERD · 조감도 · 인덱스 설명서 ​

1. ERD ​

텍스트(ASCII) 조감도 ​

2. 테이블 조감도 ​

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

생성되는 인덱스 (PK 5개 + 보조 3개 = 총 8개) ​

일부러 인덱스를 만들지 않은 컬럼 (DDL 주석에 명시) ​

무엇을 실습하나 ​

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

스키마 A — SCOTT + 주문(기본 실습) · ERD · 조감도 · 인덱스 설명서

1. ERD

텍스트(ASCII) 조감도

2. 테이블 조감도

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

생성되는 인덱스 (PK 5개 + 보조 3개 = 총 8개)

일부러 인덱스를 만들지 않은 컬럼 (DDL 주석에 명시)

무엇을 실습하나

4. DB에서 확인하는 쿼리