고객 이름이나 주문번호 하나만 넣는 통합 검색이 고객과 주문을 다 읽고 해시 조인한다

실전SELECT한빛유통59 / 700

상황

고객센터 「통합 검색」은 상담원이 고객 이름이나 주문번호 가운데 아는 것 하나를 넣으면 주문을 고객 정보와 함께 보여 준다. 처음에는 주문번호 칸만 있었는데, 고객 이름 칸을 더하면서 두 조건을 OR로 이었다. 상담원 40명이 하루 약 1만 2천 번 쓴다. 한 번에 926블록이라 하루 1,100만 블록이 넘는다. 박팀장은 "OR를 없애는 방법을 두세 개 놓고, 결과가 틀릴 위험과 운영 부담까지 비교해 와라"고 했다.

테이블과 인덱스

T_ORD 주문: 100,000행 · 896블록 · ORD_SEQ(PK) · CUS_ID(고객당 약 50건, 테이블 곳곳에 흩어져 있다) · ORD_DT · ORD_ST · PAY_TP · PAY_DT · ORD_AMT
M_CUS 고객: 2,000행 · 30블록 · CUS_ID(PK) · CUS_NM(동명이인이 있다, 한 이름에 1 ~ 3명) · RGN_ID
인덱스 PK_T_ORD (ORD_SEQ) · IX_T_ORD_CUS (CUS_ID) · PK_M_CUS (CUS_ID) · IX_M_CUS_NM (CUS_NM) · 모두 BLEVEL 1 · 통계 최신
화면은 넣지 않은 칸을 NULL로 넘긴다

원본 SQL

sql

SELECT o.ORD_SEQ, o.ORD_DT, o.ORD_ST, o.PAY_TP, o.PAY_DT, o.ORD_AMT, c.CUS_ID, c.CUS_NM, c.RGN_ID
  FROM T_ORD o, M_CUS c
 WHERE c.CUS_ID = o.CUS_ID
   AND (c.CUS_NM = :v_nm OR o.ORD_SEQ = :v_ord)
 ORDER BY o.ORD_DT DESC

실행계획 학습용 예시

xplan

[고객 이름 하나(동명이인 2명, 주문 100건)를 넣었을 때 · 주문번호만 넣어도 계획은 같다(1건, Buffers 926)]
-----------------------------------------------------------------------------------------
| Id  | Operation            | Name  | Starts | E-Rows | A-Rows | A-Time      | Buffers |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |       |      1 |        |    100 | 00:00:00.03 |     926 |
|   1 |  SORT ORDER BY       |       |      1 |   1051 |    100 | 00:00:00.03 |     926 |
|*  2 |   HASH JOIN          |       |      1 |   1051 |    100 | 00:00:00.03 |     926 |
|   3 |    TABLE ACCESS FULL | M_CUS |      1 |   2000 |   2000 | 00:00:00.01 |      30 |
|   4 |    TABLE ACCESS FULL | T_ORD |      1 |   100K |   100K | 00:00:00.02 |     896 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C"."CUS_ID"="O"."CUS_ID")
       filter(("C"."CUS_NM"=:V_NM OR "O"."ORD_SEQ"=:V_ORD))

문제

인덱스가 다 있는데도 두 테이블을 다 읽고 해시 조인한 뒤에야 거르는 이유를 OR의 두 갈래가 걸린 테이블로 설명하시오.
① 갈래마다 드라이빙 테이블이 다른 UNION ALL로 손수 나누기 ② USE_CONCAT 같은 힌트로 옵티마이저에게 펼치게 하기 ③ 화면에서 검색 칸마다 SQL을 따로 부르기를 결과의 정확성(겹치는 행, 정렬)과 운영 부담의 관점에서 비교하시오.
채택한 방법을 SQL로 작성하고 고친 뒤의 읽는 양을 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

-- ① 채택 : 갈래마다 드라이빙 테이블이 다르다 (커서 하나 · 넣지 않은 칸의 갈래는 = NULL이라 바로 끝난다)
-- 이름 갈래 : M_CUS(IX_M_CUS_NM) → T_ORD(IX_T_ORD_CUS)
SELECT o.ORD_SEQ, o.ORD_DT, o.ORD_ST, o.PAY_TP, o.PAY_DT, o.ORD_AMT, c.CUS_ID, c.CUS_NM, c.RGN_ID
  FROM M_CUS c, T_ORD o
 WHERE c.CUS_NM = :v_nm
   AND o.CUS_ID = c.CUS_ID
UNION ALL
-- 주문번호 갈래 : T_ORD(PK) → M_CUS(PK) · 이름 갈래에 이미 나온 행은 뺀다
SELECT o.ORD_SEQ, o.ORD_DT, o.ORD_ST, o.PAY_TP, o.PAY_DT, o.ORD_AMT, c.CUS_ID, c.CUS_NM, c.RGN_ID
  FROM T_ORD o, M_CUS c
 WHERE o.ORD_SEQ = :v_ord
   AND c.CUS_ID = o.CUS_ID
   AND LNNVL(c.CUS_NM = :v_nm)
 ORDER BY ORD_DT DESC

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

xplan

[고객 이름 하나 · 동명이인 2명 · 주문 100건]
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name         | Starts | E-Rows | A-Rows | A-Time      | Buffers |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |              |      1 |        |    100 | 00:00:00.01 |     108 |
|   1 |  SORT ORDER BY                           |              |      1 |     51 |    100 | 00:00:00.01 |     108 |
|   2 |   UNION-ALL                              |              |      1 |        |    100 | 00:00:00.01 |     108 |
|   3 |    NESTED LOOPS                          |              |      1 |     50 |    100 | 00:00:00.01 |     108 |
|   4 |     NESTED LOOPS                         |              |      1 |     50 |    100 | 00:00:00.01 |       8 |
|   5 |      TABLE ACCESS BY INDEX ROWID BATCHED | M_CUS        |      1 |      1 |      2 | 00:00:00.01 |       4 |
|*  6 |       INDEX RANGE SCAN                   | IX_M_CUS_NM  |      1 |      1 |      2 | 00:00:00.01 |       2 |
|*  7 |      INDEX RANGE SCAN                    | IX_T_ORD_CUS |      2 |     50 |    100 | 00:00:00.01 |       4 |
|   8 |     TABLE ACCESS BY INDEX ROWID          | T_ORD        |    100 |     50 |    100 | 00:00:00.01 |     100 |
|   9 |    NESTED LOOPS                          |              |      1 |      1 |      0 | 00:00:00.01 |       0 |
|  10 |     TABLE ACCESS BY INDEX ROWID          | T_ORD        |      1 |      1 |      0 | 00:00:00.01 |       0 |
|* 11 |      INDEX UNIQUE SCAN                   | PK_T_ORD     |      1 |      1 |      0 | 00:00:00.01 |       0 |
|* 12 |     TABLE ACCESS BY INDEX ROWID          | M_CUS        |      0 |      1 |      0 | 00:00:00.01 |       0 |
|* 13 |      INDEX UNIQUE SCAN                   | PK_M_CUS     |      0 |      1 |      0 | 00:00:00.01 |       0 |
--------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   6 - access("C"."CUS_NM"=:V_NM)
   7 - access("O"."CUS_ID"="C"."CUS_ID")
  11 - access("O"."ORD_SEQ"=:V_ORD)
  12 - filter(LNNVL("C"."CUS_NM"=:V_NM))
  13 - access("C"."CUS_ID"="O"."CUS_ID")

[주문번호만 넣었을 때 · 같은 커서] 3 ~ 8번은 0건으로 끝나고 9 ~ 13번이 한 건을 찾는다 · Buffers 6

모범 답안 서술

OR의 두 갈래가 서로 다른 테이블의 조건이다. 이름은 M_CUS에, 주문번호는 T_ORD에 있다. 어느 한 테이블에서 OR 전체를 먼저 걸러 낼 수 없으므로, 옵티마이저는 이 조건을 어느 쪽의 access로도 쓰지 못하고 두 테이블을 다 읽어 조인한 뒤에야 2번 줄에서 거른다(filter). 그래서 이름을 넣든 주문번호를 넣든 M_CUS 30블록과 T_ORD 896블록을 다 읽는다(Buffers 926). 이 계획에서는 옵티마이저가 OR를 펼치지 않았다(VW_ORE_ 없음). ① 손으로 UNION ALL을 쓰는 방법이 가장 확실하다. 이름 갈래는 M_CUS에서 출발해 IX_M_CUS_NM으로 고객을 찾고, IX_T_ORD_CUS로 그 고객의 주문을 찾는다. 주문번호 갈래는 T_ORD에서 출발해 PK로 주문 한 건을 찾고 PK_M_CUS로 고객을 붙인다. 넣지 않은 칸은 NULL이라 그 갈래의 = NULL 조건은 바로 0건으로 끝난다. 두 칸을 다 넣었을 때 같은 행이 두 갈래에 다 나오지 않도록 둘째 갈래에 LNNVL(c.CUS_NM = :v_nm)을 붙인다. :v_nm이 NULL이면 LNNVL이 참이라 주문번호 갈래의 행을 지우지 않는다. 정렬(ORDER BY)은 UNION ALL 바깥에서 전체에 한 번 건다. 이름 검색은 Buffers 926 → 108, 주문번호 검색은 926 → 6이 된다. 대가는 SQL이 두 배로 길어지고, 화면에 칸이 늘면 갈래도 는다는 점이다. ② USE_CONCAT 같은 힌트로 펼치게 하는 방법은 SQL을 거의 고치지 않아도 되고, 펼치면 옵티마이저가 LNNVL을 스스로 붙여 결과도 맞다. 하지만 펼칠지는 여전히 비용으로 정해지므로 힌트가 무시될 수 있고, 갈래마다 드라이빙 테이블을 바꾸는 계획까지 나온다는 보장이 없다. 버전이 바뀌면 계획이 달라질 수 있어 배포마다 계획을 확인해야 한다. ③ 화면에서 칸마다 SQL을 따로 부르면 SQL은 가장 단순하다(갈래마다 조인 하나). 그러나 두 칸을 다 넣었을 때 결과를 합치고, 겹친 행을 지우고, 다시 정렬하는 일이 화면 코드로 간다. 같은 검색을 쓰는 화면이 여럿이면 같은 로직이 여러 곳에 퍼진다. 그래서 ①을 고른다. 결과의 정확성(LNNVL, 바깥 정렬)을 SQL 한 곳에서 보장하고, 계획이 힌트나 버전에 기대지 않는다.

해설

함정: 둘째 갈래에서 겹침을 AND c.CUS_NM <> :v_nm으로 빼면, :v_nm이 NULL일 때(주문번호만 넣었을 때) 조건이 판정 불가가 되어 주문번호 갈래의 행까지 사라진다. 주문번호 검색이 0건이 된다. 빼는 조건은 LNNVL로 쓴다.
이름 갈래의 결과는 고객 수 × 50건 안팎이다. 화면이 첫 페이지만 보여 준다면 UNION ALL 바깥의 정렬 때문에 두 갈래를 다 읽은 뒤에 자른다는 점을 알고 쓴다(교재 11화의 OR-Expansion 나쁜 얼굴).
화면을 바꿀 수 있다면 검색 칸을 하나로 두고 입력 모양(숫자면 주문번호, 아니면 이름)으로 갈래를 고르는 방법도 있다. 그때도 갈래 선택은 바인드 조건으로 SQL 안에 두는 편이 화면 코드를 덜 늘린다.
OR가 한 테이블의 두 컬럼에 걸려 있다면(IDX-049) 갈래마다 인덱스만 있으면 옵티마이저가 펼친다. 테이블에 걸친 OR는 갈래마다 조인 순서까지 달라져야 해서 손으로 나누는 쪽이 확실하다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황