JOIN-036

가격 비교 사이트로 보내는 상품 파일에 280개 중 241개만 실린다

기초SELECT이커머스186 / 700

이 문제는 교재 18화 졸업 과제와 같은 이커머스 데이터베이스를 씁니다. 『튜너의 1년』을 순서대로 읽고 있다면 졸업 과제를 먼저 풀고 오세요. 아래 「테이블과 인덱스」 칸에 과제의 답 일부(제약 조건, 인덱스, 통계의 실제 상태)가 나옵니다.

상황

마케팅팀은 매일 밤 상품 280개를 대분류와 소분류 이름과 함께 가격 비교 사이트에 보낸다. 업체가 "상품이 39개 빠져 있다"라고 알려 왔다. 빠진 상품을 찾아보니 모두 소분류 없이 대분류에 바로 걸린 상품이었다(기프트카드, 정기배송 상품 등). 파일을 만드는 SQL은 아래 하나다.

테이블과 인덱스

products: 280행 · 8블록 · category_id · PK만 있음
categories: 53행 · 4블록 · category_id · parent_id · name · PK만 있음
분류는 2단계다. 대분류 9개는 parent_id가 NULL이고, 소분류 44개는 대분류를 가리킨다
상품 280개 중 241개는 소분류에, 39개는 대분류에 바로 걸려 있다 · 통계 최신

원본 SQL

sql

SELECT p.product_id, p.name AS product_name,
       u.name AS top_category, g.name AS sub_category
  FROM products p, categories g, categories u
 WHERE g.category_id = p.category_id
   AND u.category_id = g.parent_id
 ORDER BY p.product_id

실행계획 학습용 예시

xplan

-----------------------------------------------------------------------------------------
| Id  | Operation            | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |            |      1 |        |    241 |00:00:00.01 |      16 |
|   1 |  SORT ORDER BY       |            |      1 |    234 |    241 |00:00:00.01 |      16 |
|*  2 |   HASH JOIN          |            |      1 |    234 |    241 |00:00:00.01 |      16 |
|*  3 |    HASH JOIN         |            |      1 |     44 |     44 |00:00:00.01 |       8 |
|   4 |     TABLE ACCESS FULL| CATEGORIES |      1 |     53 |     53 |00:00:00.01 |       4 |
|   5 |     TABLE ACCESS FULL| CATEGORIES |      1 |     53 |     53 |00:00:00.01 |       4 |
|   6 |    TABLE ACCESS FULL | PRODUCTS   |      1 |    280 |    280 |00:00:00.01 |       8 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("G"."CATEGORY_ID"="P"."CATEGORY_ID")
   3 - access("U"."CATEGORY_ID"="G"."PARENT_ID")

문제

실행계획 3번 줄의 A-Rows 44가 무엇을 뜻하는지 설명하고, 상품 39개가 빠진 이유를 쓰시오.
상품 280개가 모두 나오도록 SQL을 고치시오. 대분류에 바로 걸린 상품은 대분류 칸에 그 분류 이름이, 소분류 칸에는 NULL이 나오게 하시오. (+)를 어느 쪽에 붙이는지와 그 이유도 쓰시오.

개선 SQL원본 SQL을 고쳐 쓰세요. 인덱스 DDL과 힌트도 쓸 수 있습니다.

개선 사유무엇이 문제였고 왜 이렇게 고쳤는지 쓰세요.0자

모범답안

모범 답안 SQL

sql

SELECT p.product_id, p.name AS product_name,
       NVL(u.name, g.name)                                 AS top_category,
       CASE WHEN u.category_id IS NOT NULL THEN g.name END AS sub_category
  FROM products p, categories g, categories u
 WHERE g.category_id    = p.category_id
   AND u.category_id(+) = g.parent_id     -- 상위 분류는 없어도 된다 : NULL 로 채울 쪽에 (+)
 ORDER BY p.product_id
-- ANSI : FROM products p JOIN categories g ON g.category_id = p.category_id
--        LEFT JOIN categories u ON u.category_id = g.parent_id

개선 후 실행계획 학습용 예시

xplan

------------------------------------------------------------------------------------------
| Id  | Operation             | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |            |      1 |        |    280 |00:00:00.01 |      16 |
|   1 |  SORT ORDER BY        |            |      1 |    280 |    280 |00:00:00.01 |      16 |
|*  2 |   HASH JOIN           |            |      1 |    280 |    280 |00:00:00.01 |      16 |
|*  3 |    HASH JOIN OUTER    |            |      1 |     53 |     53 |00:00:00.01 |       8 |
|   4 |     TABLE ACCESS FULL | CATEGORIES |      1 |     53 |     53 |00:00:00.01 |       4 |
|   5 |     TABLE ACCESS FULL | CATEGORIES |      1 |     53 |     53 |00:00:00.01 |       4 |
|   6 |    TABLE ACCESS FULL  | PRODUCTS   |      1 |    280 |    280 |00:00:00.01 |       8 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("G"."CATEGORY_ID"="P"."CATEGORY_ID")
   3 - access("U"."CATEGORY_ID"(+)="G"."PARENT_ID")

모범 답안 서술

3번 줄은 분류(g)와 그 상위 분류(u)를 맞대는 셀프 조인이다. A-Rows 44는 상위 분류가 있는 소분류 44개만 짝을 찾았다는 뜻이다. 대분류 9개는 parent_id가 NULL이라 u.category_id = g.parent_id가 참이 될 수 없어 여기서 모두 버려졌다. 그 뒤 2번 줄에서 상품을 붙이므로, 대분류에 바로 걸린 상품 39개는 짝을 잃고 사라졌다. 결과는 280개가 아니라 241개다. 상위 분류는 "있으면 붙이고 없어도 되는" 쪽이므로 u를 아우터 조인한다. (+)는 NULL로 채워질 쪽, 곧 상위 분류 u의 컬럼에 붙인다(u.category_id(+) = g.parent_id). 반대쪽인 g.parent_id에 붙이면 상위 분류를 보존하는 뜻이 되어 상품은 여전히 빠진다. 대분류에 바로 걸린 상품은 u가 NULL이므로 대분류 칸은 NVL(u.name, g.name)으로 채우고, 소분류 칸은 u가 있을 때만 g의 이름을 낸다. 고친 뒤 3번 줄은 HASH JOIN OUTER가 되어 A-Rows가 53이 되고, 결과는 280개다. 테이블이 작아서 Buffers는 16 → 16 그대로다.

해설

계층이 더 깊어지면: 3단계 이상이거나 단계 수가 상품마다 다르면 셀프 조인을 단계마다 늘리는 대신 CONNECT BY나 재귀 WITH로 경로를 만든다. 그때도 최상위 행(parent_id IS NULL)을 시작점으로 잡는 것이 핵심이다.
분류 자체가 없는 상품: 상품의 category_id가 분류 테이블에 없는 값이면 g와의 조인에서도 빠진다. 파일에 빠짐없이 실어야 한다면 g도 아우터 조인하고, 분류에 없는 category_id가 몇 개인지 따로 세어 둔다.
건수 대사: 파일 줄 수를 상품 테이블 행 수(280)와 맞대는 점검을 배치 끝에 넣어 두면 이런 누락은 첫날 드러난다 (교재 08화 §10).
ANSI로는 JOIN categories g ON ... 뒤에 LEFT JOIN categories u ON u.category_id = g.parent_id를 이어 쓴다. 표기는 달라도 계획은 같다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황