DETERMINISTIC을 붙였더니 개발자 시험은 빨라졌는데 관리자 화면 내려받기는 그대로다

중급SELECT이커머스658 / 700

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

상황

관리자 화면의 「주문상품 내려받기」는 한 해 치 주문상품을 카테고리명과 함께 파일로 내려 준다. 카테고리명은 함수 fn_category_name이 붙인다. 느리다는 말에 개발자는 블로그 글을 보고 함수에 DETERMINISTIC을 붙였다. "같은 상품이면 한 번만 계산된다고 해서요." 개발자가 SQL*Plus에서 ARRAYSIZE 5000으로 시험했을 때는 함수 본문 실행이 14,697번에서 4,412번으로 줄었다. 그런데 관리자 화면(Java, JDBC)에서 내려받으면 그대로다.

테이블과 인덱스

orders: 34,908행 · 193블록 · order_date DATE(시분초 있음) · PK만 · 2024년 주문 6,982건
order_items: 73,498행 · 290블록 · PK만 · 2024년 주문의 상품 14,697행 · 그해 팔린 상품 271종
products: 280행 · 8블록 · PK(product_id) · category_id
categories: 53행 · 4블록 · PK(category_id) · name
관리자 화면은 JDBC의 기본 페치 크기(한 번에 10행)로 받는다 · 통계 최신

원본 SQL

sql

CREATE OR REPLACE FUNCTION fn_category_name(p_product_id NUMBER) RETURN VARCHAR2 DETERMINISTIC IS
  v_name categories.name%TYPE;
BEGIN
  SELECT c.name INTO v_name
    FROM products p, categories c
   WHERE p.product_id  = p_product_id
     AND c.category_id = p.category_id;
  RETURN v_name;
END;
/

SELECT o.order_id, o.order_date, oi.product_id, oi.quantity, oi.unit_price,
       fn_category_name(oi.product_id) AS category_name
  FROM orders o, order_items oi
 WHERE o.order_date >= DATE '2024-01-01'
   AND o.order_date <  DATE '2025-01-01'
   AND oi.order_id = o.order_id;

실행계획 학습용 예시

xplan

[관리자 화면에서 내려받을 때: DISPLAY_CURSOR (ALLSTATS LAST)]
-------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |      1 |        |  14697 |00:00:00.69 |    1953 |          |
|*  1 |  HASH JOIN         |             |      1 |  14620 |  14697 |00:00:00.69 |    1953 | 1199K (0)|
|*  2 |   TABLE ACCESS FULL| ORDERS      |      1 |   6982 |   6982 |00:00:00.01 |     193 |          |
|   3 |   TABLE ACCESS FULL| ORDER_ITEMS |      1 |  73498 |  73498 |00:00:00.03 |    1760 |          |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("OI"."ORDER_ID"="O"."ORDER_ID")
   2 - filter(("O"."ORDER_DATE">=TO_DATE(' 2024-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "O"."ORDER_DATE"<TO_DATE(' 2025-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
   (ORDER_ITEMS 1,760 = 290블록 + 페치 1,470번마다 다시 잡은 블록)

[함수 본문 실행 수: 함수 안 SELECT의 V$SQL.EXECUTIONS 증가분]
 받는 쪽                          페치 크기   페치 수   DETERMINISTIC 전   DETERMINISTIC 후   경과(후)
 관리자 화면 (JDBC)                    10     1,470             14,697             14,453     0.69 초
 SQL*Plus 시험 (ARRAYSIZE 5000)     5,000         3             14,697              4,412     0.27 초

문제

같은 함수와 같은 SQL인데 시험에서는 호출이 줄고 관리자 화면에서는 줄지 않은 이유를 설명하시오.
받는 쪽의 페치 크기와 상관없이 함수 호출이 줄도록 SQL을 고치시오. 함수에 붙인 DETERMINISTIC은 어떻게 할지도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- ① 함수: 테이블을 읽으므로 DETERMINISTIC 을 뗀다 (다른 호출부는 그대로 쓴다)
CREATE OR REPLACE FUNCTION fn_category_name(p_product_id NUMBER) RETURN VARCHAR2 IS
  v_name categories.name%TYPE;
BEGIN
  SELECT c.name INTO v_name
    FROM products p, categories c
   WHERE p.product_id  = p_product_id
     AND c.category_id = p.category_id;
  RETURN v_name;
END;
/

-- ② 내려받기: 함수 대신 조인 (못 찾으면 NULL 이던 함수와 같게 아우터 조인)
SELECT o.order_id, o.order_date, oi.product_id, oi.quantity, oi.unit_price,
       c.name AS category_name
  FROM orders o
  JOIN order_items oi    ON oi.order_id   = o.order_id
  LEFT JOIN products p   ON p.product_id  = oi.product_id
  LEFT JOIN categories c ON c.category_id = p.category_id
 WHERE o.order_date >= DATE '2024-01-01'
   AND o.order_date <  DATE '2025-01-01';

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

xplan

---------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |             |      1 |        |  14697 |00:00:00.05 |    1965 |          |
|*  1 |  HASH JOIN RIGHT OUTER |             |      1 |  14620 |  14697 |00:00:00.05 |    1965 | 1288K (0)|
|   2 |   TABLE ACCESS FULL    | CATEGORIES  |      1 |     53 |     53 |00:00:00.01 |       4 |          |
|*  3 |   HASH JOIN RIGHT OUTER|             |      1 |  14620 |  14697 |00:00:00.04 |    1961 | 1310K (0)|
|   4 |    TABLE ACCESS FULL   | PRODUCTS    |      1 |    280 |    280 |00:00:00.01 |       8 |          |
|*  5 |    HASH JOIN           |             |      1 |  14620 |  14697 |00:00:00.03 |    1953 | 1199K (0)|
|*  6 |     TABLE ACCESS FULL  | ORDERS      |      1 |   6982 |   6982 |00:00:00.01 |     193 |          |
|   7 |     TABLE ACCESS FULL  | ORDER_ITEMS |      1 |  73498 |  73498 |00:00:00.01 |    1760 |          |
---------------------------------------------------------------------------------------------------------
   1 - access("C"."CATEGORY_ID"(+)="P"."CATEGORY_ID")
   3 - access("P"."PRODUCT_ID"(+)="OI"."PRODUCT_ID")
   5 - access("OI"."ORDER_ID"="O"."ORDER_ID")

[함수 본문 실행]  관리자 화면 0 · SQL*Plus 0 (페치 크기와 상관없다)

모범 답안 서술

DETERMINISTIC은 함수 결과를 저장해 두는 캐시가 아니라 '같은 입력이면 같은 답'이라는 작성자의 약속이다. 오라클은 그 약속을 믿고 SQL 안에서 같은 입력의 호출을 줄이는데, 그 기억은 한 번의 페치 안에서만 산다. 시험은 ARRAYSIZE 5000으로 3번 페치해서 한 페치(5,000행) 안에 같은 상품이 여러 번 나왔고, 본문 실행이 14,697 → 4,412로 줄었다. 관리자 화면은 JDBC 기본값대로 한 번에 10행씩 1,470번 페치한다. 10행 안에서 같은 상품이 다시 나올 일은 드물어서 14,697 → 14,453으로 거의 줄지 않았다. 같은 SQL이라도 받는 쪽의 페치 크기가 효과를 정한 것이다. 게다가 이 함수는 products와 categories를 읽는다. 카테고리 이름을 고치면 답이 바뀌므로 '같은 입력이면 같은 답'은 거짓 약속이고, 오라클은 이를 검사하지 않는다. DETERMINISTIC은 떼어 낸다. 내려받기 SQL은 함수 대신 products, categories와 조인한다. 함수는 상품이나 카테고리를 못 찾으면 NULL을 돌려주므로(SQL 안의 NO_DATA_FOUND), 결과가 바뀌지 않게 아우터 조인으로 받는다. 조인이면 함수 호출이 0번이고, 작은 두 테이블(12블록)을 해시 조인에 한 번 올릴 뿐이라 Buffers는 1,953 → 1,965로 거의 같다. 함수 본문은 14,453 → 0, 경과는 0.69 → 0.05초가 된다. 페치 크기와 상관이 없다. 함수 호출을 스칼라 서브쿼리 (SELECT fn_category_name(oi.product_id) FROM DUAL)로 감싸는 것도 답이다. 스칼라 서브쿼리 캐시는 페치가 아니라 실행이 끝날 때까지 기억하므로 관리자 화면에서도 줄어든다. 다만 271종이면 캐시 칸에 다 들지 못해 일부 상품은 매번 호출된다(교재 07화: 256종류에 55개가 자리를 못 얻었다). 조인이 더 확실하다.

해설

같은 SQL, 다른 숫자. DETERMINISTIC의 효과는 SQL이 아니라 받는 쪽이 정한다. 교재 15b화에서도 같은 10만 행을 ARRAYSIZE 15로 받으면 100,000번, 5000이면 86,143번이었다. 시험 도구의 페치 크기를 운영 화면과 맞춰 재야 한다.
페치 크기를 키우는 처방(JDBC setFetchSize(500))은 왕복 수를 줄이는 데는 좋지만(교재 09화), 함수 호출을 줄이는 근거로 삼으면 안 된다. 화면이 바뀌면 효과도 사라진다.
DETERMINISTIC을 남겨 두면 나중에 이 함수로 함수 기반 인덱스를 만들거나 결과 캐시를 붙일 때 그 거짓 약속이 영속된다(교재 15b화). 테이블을 읽는 함수에는 붙이지 않는다.
다른 호출부(상품 상세 한 건 등)는 함수를 그대로 써도 된다. 한 번 부르는 자리에서 함수는 문제가 아니다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황