테마
고객사와 테이블
연습 문제는 『튜너의 1년』에 나오는 고객사 네 곳의 테이블로 만들었다. 행 수와 블록 수는 교재의 방법대로 각 고객사 데이터베이스를 만들었을 때 실측한 값이다. 문제를 푸는 데 필요한 인덱스와 통계 상태는 문제마다 「테이블과 인덱스」 칸에 따로 적었다. 여기 적힌 인덱스가 모든 문제에 다 있는 것은 아니다.
실행계획의
Buffers는 이 규모에 맞춘 학습용 예시다. 테이블을 처음부터 끝까지 한 번 읽으면 대략 그 테이블의 블록 수만큼 읽는다고 생각하면 감을 잡기 쉽다.
한빛유통: 쇼핑몰 주문과 정산 (ora-hanorder, 계정 sqlp)
| 테이블 | 행 | 블록 | 컬럼 · 분포 | 키 |
|---|---|---|---|---|
T_ORD_BIG 대용량 주문 | 1,000,000 | 5,826 | ORD_SEQ NUMBER · CUS_ID VARCHAR2(40) 'CUS_000123' · ORD_DT DATE (2022-01-01 ~ 2024-12-31 · 전부 자정) · ORD_ST (COMPLETE 90 % · PENDING 8 % · CANCEL 2 %) · ORD_AMT | PK 없음 · 인덱스는 문제마다 |
T_ORD 주문 | 100,000 | 896 | ORD_SEQ · CUS_ID · ORD_DT DATE (시분초 있음) · ORD_ST · PAY_DT · PAY_TP · ORD_AMT · PAY_AMT | PK_T_ORD (ORD_SEQ) |
T_ORD_DET 주문상세 | 200,000 | 896 | ORD_SEQ · ORD_DET_NO · ITM_ID · ORD_QTY · UNT_PRC (주문당 1 ~ 3건) | PK_T_ORD_DET (ORD_SEQ, ORD_DET_NO) |
M_CUS 고객 | 2,000 | 30 | CUS_ID · CUS_NM · RGN_ID · GND_TP · BTH_YMD · CUS_GD (A 1 % · B 9 % · C 90 %) | PK_M_CUS (CUS_ID) |
M_RGN 지역 | 8 | 4 | RGN_ID · RGN_NM · SRT_OD | PK_M_RGN |
M_ITM 상품 | 100 | 5 | ITM_ID · ITM_NM · ITM_TP · UNT_PRC | PK_M_ITM |
T_ITM_EVL 상품평가 | 5,000 | 48 | ITM_ID · EVL_LST_NO · CUS_ID · EVL_DT · EVL_PT (상품당 50건) | PK_T_ITM_EVL (ITM_ID, EVL_LST_NO) |
T_ORD_STLM 정산 | 100,000 | 640 | 정산 배치가 T_ORD에서 적재 · STLM_SEQ · ORD_SEQ · CUS_ID · STLM_DT · STLM_AMT | — |
T_STLM_NUM 채번 | 1 | — | NUM_TP · LAST_NO (정산 번호를 MAX+1과 FOR UPDATE로 매기던 테이블) | PK (NUM_TP) |
교재에서 실측한 대표값은 다음과 같다. T_ORD_BIG의 2024년 6월 주문은 27,390건이고, 테이블 전체 스캔 한 번에 5,826블록을 읽는다. 같은 조회를 (ORD_DT, ORD_AMT) 인덱스로 처리하면 91블록이다. 등급별 매출 집계를 NL 조인으로 풀면 1,026,849블록, 해시 조인으로 풀면 5,868블록을 읽는다.
버거: 프랜차이즈 POS (ora-burger, 계정 dev)
| 테이블 | 행 | 블록 | 컬럼 · 분포 | 키 |
|---|---|---|---|---|
ORD 주문 | 1,000,000 | 22,422 | ORD_NO · ORD_DT VARCHAR2(8) '20120101' ~ '20131231' · ORD_HMS VARCHAR2(6) · SHOP_NO (매장 200개) · 문자열 채움 컬럼 | ORD_PK (ORD_NO) · ORD_X01 (ORD_DT, ORD_HMS): 날짜순 적재, CF 22,223 |
ORD_RANDOM | 1,000,000 | 22,414 | ORD와 같은 내용 · 무작위 순서로 적재 | ORD_RANDOM_X01 (ORD_DT, ORD_HMS): CF 999,958 |
ORD_ITEM 주문상품 | 2,472,717 | 55,265 | ORD_NO · ITEM_ID · ORD_ITEM_QTY · ORD_DT · ORD_HMS | ORD_ITEM_PK (ORD_NO, ITEM_ID) · ORD_ITEM_X01 (ORD_DT, ORD_HMS) |
ITEM 상품 | 92 | 5 | ITEM_ID · ITEM_NM · ITEM_TYPE_CD · SALE_YN | PK |
SHOP 매장 | 200 | 5 | SHOP_NO 'SH0001' · SHOP_NM · ADDR | PK |
ORD_LIST, ORD_RANGE(월별 ORD_YM 파티션), ORD_LIST_HASH(LIST + HASH 8)는 14화에서 만드는 파티션 복사본이다.
유통: 본사 레거시 유통 마스터 (ora-dist, 계정 pup)
| 테이블 | 행 | 블록 | 컬럼 · 분포 | 키 |
|---|---|---|---|---|
CF10 판매 | 500,000 | 4,583 | SLE_NO VARCHAR2(15) · SLE_DT VARCHAR2(8) · SLE_HMS · SLE_DOW · SHP_NO | CF10_PK (SLE_NO) |
CF11 판매상품 | 1,096,708 | 8,204 | SLE_NO · ITM_NO · SLE_QTY · SLE_UPRC | CF11_PK (SLE_NO, ITM_NO) (ITM_NO만으로 조회하면 선두 컬럼이 아니다) |
CF01 상품 | 109 | 5 | ITM_NO · ITM_NM · ITM_CL_CD · CTG_NO · PTNR_NO · SLE_YN | PK |
CF02 카테고리 | 18 | 4 | CTG_NO · CTG_NM · UP_CTG_NO (자기 참조 계층) | PK |
CF05 상품가격 | 157 | 5 | ITM_NO · UPRC_KN_CD · APL_DT · ITM_UPRC (적용일자 이력) | PK (ITM_NO, UPRC_KN_CD, APL_DT) |
CF07 매장 | 6 | 4 | SHP_NO · SHP_NM · SHP_ADDR | PK |
CF12 · CF13 발주 · 발주상품 | 734 · 7,102 | 10 · 57 | ORD_NO · ORD_DT · PTNR_NO · ORD_PRGS_CD / ITM_NO · ORD_QTY | PK |
CF15 입출고상품 | 51,497 | 349 | INOUT_NO · ITM_NO · INOUT_QTY | PK |
CF18 일별상품재고 | 408 | 6 | SHP_NO · ITM_NO · INV_BS_DT · INV_QTY | PK (SHP_NO, ITM_NO, INV_BS_DT) |
이커머스: 신규 고객사, 정규화된 테이블 30개 (ora-newshop, 계정 ecommerce)
🔒 교재 18화 졸업 과제에 쓰는 데이터베이스다. 『튜너의 1년』을 순서대로 읽고 있다면 졸업 과제를 먼저 풀어야 한다. 아래 상자와 🔒 표시가 붙은 연습 문제에 과제의 답 일부가 나온다.
| 테이블 | 행 | 블록 | 컬럼 · 분포 | 키 |
|---|---|---|---|---|
orders | 34,908 | 193 | order_id · customer_id · order_date DATE (시분초 있음 · 2020 ~ 2024) · status (6종) · total_amount | PK만 |
order_items | 73,498 | 290 | order_item_id · order_id · product_id · quantity · unit_price | PK만 |
payments · shipping | 34,908 · 34,908 | 192 · 183 | order_id와 1:1 · payment_method (3종) / address_id · status | PK만 |
customers | 5,230 | 51 | customer_id · email (UQ) · name · join_date · grade (4종) | PK · UQ |
customer_addresses | 7,253 | 62 | address_id · customer_id · city · zipcode | PK만 |
products · categories | 280 · 53 | 8 · 4 | category_id · supplier_id · name / parent_id (계층) | PK만 |
reviews | 15,000 | 775 | review_id · product_id · customer_id · rating 1 ~ 5 · content CLOB · created_at (상품당 34 ~ 76건) | PK만 |
product_views | 100,000 | 367 | view_id · product_id · customer_id · view_date | PK만 |
point_transactions | 45,000 | 210 | tx_id · customer_id · amount · tx_type (EARN · USE · EXPIRE) · created_at | PK만 |
customer_grade_history | 12,000 | 64 | customer_id · grade · start_date · end_date (SCD2) | PK만 |
🔒 제약 조건과 인덱스의 실제 상태 (졸업 과제를 끝낸 뒤에 열어 본다)
FK는 39개가 정의되어 있지만 모두 비활성 상태(DISABLED NOT VALIDATED)이고, 조인 키에는 인덱스도 없다. CHECK 제약 8개도 꺼져 있다. 그래서 부모 없는 자식 행(고아 키)이 있다(교재 18화).