카테고리 개편 뒤 상품 등록 화면의 카테고리 목록이 비었다

모범 답안 SQL ​

-- '하위가 없다' = 나를 부모로 가리키는 행이 없다 → NOT EXISTS (NULL 이 끼어도 뜻이 바뀌지 않는다)
SELECT c.CTG_NO, c.CTG_NM
  FROM CF02 c
 WHERE NOT EXISTS (SELECT 1 FROM CF02 s WHERE s.UP_CTG_NO = c.CTG_NO)
 ORDER BY c.CTG_NO;

-- NOT IN 을 꼭 쓰려면 서브쿼리에서 NULL 을 뺀다 (같은 14 건)
-- SELECT c.CTG_NO, c.CTG_NM FROM CF02 c
--  WHERE c.CTG_NO NOT IN (SELECT s.UP_CTG_NO FROM CF02 s WHERE s.UP_CTG_NO IS NOT NULL);

개선 후 실행계획 학습용 예시 · 행 수는 실데이터 ​

---------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        |     14 |00:00:00.01 |       8 |       |       |          |
|   1 |  SORT ORDER BY      |      |      1 |     14 |     14 |00:00:00.01 |       8 |  2048 |  2048 | 2048  (0)|
|*  2 |   HASH JOIN ANTI    |      |      1 |     14 |     14 |00:00:00.01 |       8 |  2078K|  2078K| 1227K (0)|
|   3 |    TABLE ACCESS FULL| CF02 |      1 |     18 |     18 |00:00:00.01 |       4 |       |       |          |
|*  4 |    TABLE ACCESS FULL| CF02 |      1 |     14 |     14 |00:00:00.01 |       4 |       |       |          |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("S"."UP_CTG_NO"="C"."CTG_NO")
   4 - filter("S"."UP_CTG_NO" IS NOT NULL)

모범 답안 서술 ​

서브쿼리 결과에 NULL이 있다. 최상위 카테고리 4개(음료, 푸드, 굿즈, 기타)는 부모가 없어 UP_CTG_NO가 NULL이다. CTG_NO NOT IN (100, 200, 300, 900, NULL, …)은 CTG_NO <> 100 AND … AND CTG_NO <> NULL과 같고, 마지막 항은 참도 거짓도 아닌 UNKNOWN이라 AND 전체가 참이 될 수 없다. NULL이 하나라도 있으면 어떤 행도 남지 않는다. 개편 전에는 '전체(0)'가 자기 자신을 가리켜 NULL이 없었을 뿐이다. 계획 2번 줄 HASH JOIN ANTI NA의 NA는 Null-Aware를 뜻한다. 옵티마이저는 NOT IN을 NOT EXISTS로 바꾸지 않고, NULL을 고려하는 안티 조인으로 'NULL이 있으면 0건'이라는 NOT IN의 뜻을 정확히 지켰다. 계획은 빠르지만 결과는 틀렸다. SQL이 질문을 잘못 던진 것이다. 요구는 '하위 카테고리가 없는 카테고리'이므로 NOT EXISTS로 쓴다. NOT EXISTS는 짝이 있는지만 보므로 NULL이 끼어도 뜻이 바뀌지 않는다. 결과는 0 → 14개가 된다(110 ~ 115, 210 ~ 212, 310 ~ 312, 910, 911). 계획은 HASH JOIN ANTI가 되고, 서브쿼리 쪽에 filter("S"."UP_CTG_NO" IS NOT NULL)이 붙는다. NOT IN을 꼭 쓰려면 서브쿼리에 WHERE UP_CTG_NO IS NOT NULL을 넣어야 답이 같아진다. 개발자의 제안대로 최상위의 UP_CTG_NO를 0으로 채우는 것은 답이 아니다. '전체(0)'는 이제 없으므로 있지도 않은 부모 0을 만드는 셈이고, 최상위를 UP_CTG_NO IS NULL로 찾는 계층 조회(CONNECT BY … START WITH)와 다른 화면을 망가뜨린다. 최상위의 부모는 NULL인 것이 맞다. 데이터는 옳고 SQL이 틀렸다. 성능을 보면, 이 표는 18행이라 블록 차이가 없다(8 → 8). 그러나 NA 안티 조인이 없던 10g까지는(그리고 버전에 따라 다중 컬럼 NOT IN은 지금도) NULL 허용 컬럼의 NOT IN을 안티 조인으로 펼치지 못하고, FILTER로 바깥 행마다 서브쿼리를 다시 돌렸다. 큰 표에서는 바깥 행 수 × 서브쿼리 한 번의 일량이 든다. NOT EXISTS는 NULL 걱정 없이 일반 안티 조인으로 펼쳐진다.

해설 ​

• '지금 NULL이 없어서 같다'는 착각: 개편 전에도 이 SQL은 틀린 질문이었다. '전체(0)'라는 가짜 부모 덕에 NULL이 없는 동안 NOT IN과 NOT EXISTS의 답이 같았을 뿐이다 (교재 08화 §7, 구 시스템 EMP의 KING 사례: NOT IN 0명, NOT EXISTS 8명).
• 바깥 쪽 NULL도 있다: 바깥 컬럼(c.CTG_NO)이 NULL인 행은 NOT IN에서는 언제나 빠지지만 NOT EXISTS에서는 남는다. 이 표의 바깥 컬럼은 PK라 해당이 없지만, 두 문장이 '모든 경우'에 같지는 않다는 점은 리포트에 적어 둔다.
• 다른 정답: NOT IN (… WHERE UP_CTG_NO IS NOT NULL), 자기 외부 조인 후 s.CTG_NO IS NULL, MINUS도 모두 14건을 낸다.
• 같은 함정이 유통의 CF01.PTNR_NO에도 있다. 매장에서 만드는 상품 41개는 거래처가 없어 NULL이다. 그래서 거래처를 NOT IN (SELECT PTNR_NO FROM CF01 …)로 거르는 SQL은 전부 0건이 된다.

관련 교재 ​

• 08화 · NOT IN 과 NULL — Null-Aware 안티 조인