JOIN-008

매장 하루치 영수증 150장을 펼치는데 판매상품 110만 줄을 다 읽는다

기초SELECT유통158 / 700

상황

매장 관리 화면의 「일별 영수증 상세」는 매장과 날짜를 고르면 그날 영수증마다 판매 상품 줄을 보여 준다. 점장 다섯 명과 본사 영업팀이 하루 2천 번쯤 누른다. 03화에서 SLE_DT 인덱스를 만든 뒤로 판매 헤더 쪽은 빨라졌는데, 이 화면은 여전히 0.1초가 넘는다. SQL은 2009년에 만든 그대로이고, 조인 조건 끝에 주석이 하나 붙어 있다.

테이블과 인덱스

CF10 판매: 500,000행 · 4,583블록 · CF10_PK (SLE_NO) · IX_CF10_SLE_DT (SLE_DT) (03화에서 만든 인덱스, 하루치가 약 14블록에 모여 있다) · 하루 약 700건 · 판매 매장은 1001 ~ 1005의 5곳
CF11 판매상품: 1,096,708행 · 8,204블록 · CF11_PK (SLE_NO, ITM_NO) · 영수증 한 장에 1 ~ 4줄
강남점(SHP_NO = 1001)의 2023-06-01 영수증 150장, 판매 줄 332줄, 금액 2,844,500원 · 통계 최신

원본 SQL

sql

SELECT a.SLE_NO, a.SLE_HMS, b.ITM_NO, b.SLE_QTY, b.SLE_UPRC
  FROM CF10 a, CF11 b
 WHERE a.SLE_DT = :v_dt
   AND a.SHP_NO = :v_shp
   AND b.SLE_NO || '' = a.SLE_NO     -- CF11 인덱스 억제, 헤더부터 읽게 (2009.04 RBO)
 ORDER BY a.SLE_NO, b.ITM_NO

실행계획 학습용 예시 · 행 수는 실데이터

xplan

-------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                |      1 |        |    332 |00:00:00.12 |    8223 |           |
|   1 |  SORT ORDER BY                         |                |      1 |    300 |    332 |00:00:00.12 |    8223 |   34K (0) |
|*  2 |   HASH JOIN                            |                |      1 |    300 |    332 |00:00:00.12 |    8223 | 1243K (0) |
|*  3 |    TABLE ACCESS BY INDEX ROWID BATCHED | CF10           |      1 |    137 |    150 |00:00:00.01 |      19 |           |
|*  4 |     INDEX RANGE SCAN                   | IX_CF10_SLE_DT |      1 |    685 |    706 |00:00:00.01 |       5 |           |
|   5 |    TABLE ACCESS FULL                   | CF11           |      1 |  1096K |  1096K |00:00:00.05 |    8204 |           |
-------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("A"."SLE_NO"="B"."SLE_NO"||'')
   3 - filter("A"."SHP_NO"=:V_SHP)
   4 - access("A"."SLE_DT"=:V_DT)

문제

Predicate Information 2번 줄을 보고, 영수증 150장의 판매 줄을 찾는 데 왜 CF11을 처음부터 끝까지 읽는지 설명하시오.
결과가 바뀌지 않게 SQL을 고쳐 영수증마다 CF11_PK로 판매 줄을 찾아가도록 하시오. 2009년의 주석이 왜 붙었는지와 지금은 왜 필요 없는지도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

SELECT a.SLE_NO, a.SLE_HMS, b.ITM_NO, b.SLE_QTY, b.SLE_UPRC
  FROM CF10 a, CF11 b
 WHERE a.SLE_DT = :v_dt
   AND a.SHP_NO = :v_shp
   AND b.SLE_NO = a.SLE_NO
 ORDER BY a.SLE_NO, b.ITM_NO
-- 순서를 꼭 고정해야 한다면 컬럼을 가공하지 말고 힌트로 뜻을 적는다: /*+ LEADING(a b) USE_NL(b) */

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

xplan

-------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                |      1 |        |    332 |00:00:00.01 |     653 |          |
|   1 |  SORT ORDER BY                          |                |      1 |    300 |    332 |00:00:00.01 |     653 |  34K (0) |
|   2 |   NESTED LOOPS                          |                |      1 |    300 |    332 |00:00:00.01 |     653 |          |
|   3 |    NESTED LOOPS                         |                |      1 |    300 |    332 |00:00:00.01 |     321 |          |
|*  4 |     TABLE ACCESS BY INDEX ROWID BATCHED | CF10           |      1 |    137 |    150 |00:00:00.01 |      19 |          |
|*  5 |      INDEX RANGE SCAN                   | IX_CF10_SLE_DT |      1 |    685 |    706 |00:00:00.01 |       5 |          |
|*  6 |     INDEX RANGE SCAN                    | CF11_PK        |    150 |      2 |    332 |00:00:00.01 |     302 |          |
|   7 |    TABLE ACCESS BY INDEX ROWID          | CF11           |    332 |      2 |    332 |00:00:00.01 |     332 |          |
-------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter("A"."SHP_NO"=:V_SHP)
   5 - access("A"."SLE_DT"=:V_DT)
   6 - access("B"."SLE_NO"="A"."SLE_NO")

모범 답안 서술

2번 줄 HASH JOIN의 조건은 access("A"."SLE_NO"="B"."SLE_NO"||'')다. CF11 쪽 조인 컬럼에 || ''를 붙여 가공했으므로, CF11_PK에는 이 값으로 찾아갈 자리가 없다. 영수증 한 장마다 인덱스로 찾아가는 NL은 할 수 없어서, 옵티마이저는 영수증 150장을 메모리에 올린 뒤 CF11 110만 줄을 처음부터 끝까지 읽어 맞춰 보는 해시 조인을 골랐다. 전체 8,223블록 중 8,204블록이 5번 줄 TABLE ACCESS FULL CF11에서 나온다. || ''는 값을 바꾸지 않는다. 2009년의 규칙 기반 옵티마이저(RBO)는 비용을 계산하지 않고 '인덱스가 있으면 쓴다'는 규칙으로 계획을 정했다. 그래서 개발자가 조인 컬럼을 일부러 가공해 인덱스를 막고 조인 순서를 통제했다. 주석의 '헤더부터 읽게'가 그 뜻이다. 지금의 비용 기반 옵티마이저(CBO)에서는 이 가공이 inner 쪽 인덱스를 막는 부작용만 남았다. 가공을 지우면 CF10에서 강남점 영수증 150장을 먼저 읽고, 한 장마다 CF11_PK로 판매 줄을 찾아간다. 6번 줄 Starts가 150이고 한 번에 2블록 정도면 된다. Buffers는 8,223 → 653으로 준다. 순서를 꼭 고정해야 한다면 컬럼을 가공하지 말고 LEADING(a b) USE_NL(b) 힌트로 뜻을 적는다.

해설

같은 흔적 찾기: 숫자 컬럼에 + 0, 문자 컬럼에 || ''를 붙인 조건은 RBO 시절에 인덱스를 막던 기법이다. 레거시 소스에서 이런 조건을 찾으면 왜 넣었는지 주석과 이력을 확인하고 지운 뒤, 지운 다음의 계획도 다시 확인한다.
어느 쪽을 가공했는가: 가공이 바깥(드라이빙) 쪽 a.SLE_NO에 붙었다면 NL에는 문제가 없다. 바깥 쪽 값은 이미 읽은 값이고, inner 쪽 인덱스는 가공되지 않은 컬럼으로 찾아가기 때문이다. 조인 조건의 가공은 inner 쪽에 붙었을 때 인덱스를 막는다.
숫자로 보면: 하루 2천 번이면 약 1,645만 → 131만 블록이다. 날짜와 매장을 함께 담은 CF10 (SLE_DT, SHP_NO) 인덱스를 만들면 CF10 쪽 19블록도 줄지만, 이 화면에서 얻는 것은 작다.
ORDER BY a.SLE_NO, b.ITM_NO의 정렬은 그대로 남는다. 332줄을 정렬하는 비용은 작다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

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

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황