【PL/SQL】FUNCTION RESULT_CACHEの使い方｜関数キャッシュの判断基準・確認SQL・注意点

CREATE OR REPLACE FUNCTION calc_tax(
  p_amount IN NUMBER
) RETURN NUMBER
RESULT_CACHE
IS
BEGIN
  RETURN ROUND(p_amount * 0.10);
END;
/

SELECT calc_tax(1000) FROM dual;
SELECT calc_tax(1000) FROM dual; -- 同じ引数ならキャッシュ再利用候補

-- NG例: 時刻に依存する
CREATE OR REPLACE FUNCTION today_label
RETURN VARCHAR2
RESULT_CACHE
IS
BEGIN
  RETURN TO_CHAR(SYSDATE, 'YYYY-MM-DD');
END;
/

-- NG例: セッション依存値に依存する
CREATE OR REPLACE FUNCTION current_tenant
RETURN VARCHAR2
RESULT_CACHE
IS
BEGIN
  RETURN SYS_CONTEXT('APP_CTX', 'TENANT_ID');
END;
/

CREATE TABLE m_status (
  status_code VARCHAR2(20) PRIMARY KEY,
  status_name VARCHAR2(100) NOT NULL
);

CREATE OR REPLACE FUNCTION get_status_name(
  p_status_code IN VARCHAR2
) RETURN VARCHAR2
RESULT_CACHE
IS
  l_status_name m_status.status_name%TYPE;
BEGIN
  SELECT status_name
  INTO l_status_name
  FROM m_status
  WHERE status_code = p_status_code;

  RETURN l_status_name;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RETURN NULL;
END;
/

SELECT order_id,
       get_status_name(status_code) AS status_name
FROM orders;

-- 古い11g系サンプルで見かける形
CREATE OR REPLACE FUNCTION get_customer_sales(p_customer_id NUMBER)
RETURN NUMBER
RESULT_CACHE RELIES_ON (orders)
IS
  l_total NUMBER;
BEGIN
  SELECT NVL(SUM(amount), 0)
  INTO l_total
  FROM orders
  WHERE customer_id = p_customer_id;

  RETURN l_total;
END;
/

-- 現行設計では通常 RELIES_ON を書かず、依存関係の自動検出に任せる
CREATE OR REPLACE FUNCTION get_customer_sales(p_customer_id NUMBER)
RETURN NUMBER
RESULT_CACHE
IS
  l_total NUMBER;
BEGIN
  SELECT NVL(SUM(amount), 0)
  INTO l_total
  FROM orders
  WHERE customer_id = p_customer_id;

  RETURN l_total;
END;
/

-- 関数にRESULT_CACHEが付いているか
SELECT object_name,
       procedure_name,
       result_cache
FROM user_procedures
WHERE result_cache = 'YES'
ORDER BY object_name, procedure_name;

-- キャッシュされているオブジェクト
SELECT id,
       type,
       status,
       name,
       namespace,
       creation_timestamp
FROM v$result_cache_objects
ORDER BY creation_timestamp DESC;

-- 統計情報
SELECT name, value
FROM v$result_cache_statistics
ORDER BY name;

-- キャッシュ全体のフラッシュ
BEGIN
  DBMS_RESULT_CACHE.FLUSH;
END;
/

-- メモリ使用状況のレポート
SET SERVEROUTPUT ON
BEGIN
  DBMS_RESULT_CACHE.MEMORY_REPORT;
END;
/

SET SERVEROUTPUT ON
DECLARE
  l_start PLS_INTEGER;
  l_value NUMBER;
BEGIN
  DBMS_RESULT_CACHE.FLUSH;

  l_start := DBMS_UTILITY.GET_TIME;
  l_value := get_customer_sales(1001);
  DBMS_OUTPUT.PUT_LINE('first=' || (DBMS_UTILITY.GET_TIME - l_start));

  l_start := DBMS_UTILITY.GET_TIME;
  l_value := get_customer_sales(1001);
  DBMS_OUTPUT.PUT_LINE('second=' || (DBMS_UTILITY.GET_TIME - l_start));
END;
/

-- 1回目: キャッシュ作成
SELECT get_customer_sales(1001) FROM dual;

-- 2回目: キャッシュ再利用候補
SELECT get_customer_sales(1001) FROM dual;

-- 参照表を更新してコミット
UPDATE orders
SET amount = amount + 100
WHERE customer_id = 1001;
COMMIT;

-- 関連キャッシュは無効化され、再計算される
SELECT get_customer_sales(1001) FROM dual;