#99 Oracle DBの分析関数(FIRST/LAST等)使用時の注意点

概要

Oracle DB で、分析構文のKEEP (... ORDER BY ...)を使用したクエリを見る機会がありました。
簡略化すると、以下のような内容です。

SELECT
  ANY_VALUE("user_id") AS user_id,
  ANY_VALUE("col1") KEEP (DENSE_RANK FIRST ORDER BY "sort") AS col1,
  ANY_VALUE("col2") KEEP (DENSE_RANK FIRST ORDER BY "sort") AS col2,
  ANY_VALUE("col3") KEEP (DENSE_RANK FIRST ORDER BY "sort") AS col3,
  ANY_VALUE("col4") KEEP (DENSE_RANK LAST ORDER BY "sort") AS col4
FROM table
WHERE ...
GROUP BY "user_id";

ユーザー単位で、あるソート条件での最初のデータや最後のデータを抽出しています。
このクエリを見て気になったのが、同じソート条件を使いまわすところが多いが、1度ソートされた結果を内部的に保持して使いまわしてくれるのか、それともカラムごとにソートの再計算が走ってしまうのかという点。

Oracleの公式リファレンスを確認したところ、似たような書き方をしていたので問題なく使いまわしてくれそうかと思ったものの、スッキリさせたいので検証してみました。

SELECT department_id,
     MIN(salary) KEEP (DENSE_RANK FIRST ORDER BY commission_pct) "Worst",
     MAX(salary) KEEP (DENSE_RANK LAST ORDER BY commission_pct) "Best"
FROM employees
GROUP BY department_id
ORDER BY department_id;

引用: Oracle Database SQL Language Reference, 11g Release 2 (11.2)

テストデータ

以下のようなテーブルを用意し、一千万件のダミーデータを作成しました。

id	rand1	rand2	rand3	rand4	rand5
主キー オートインクリメント	乱数(1～1000)	乱数(1～1000)	乱数(1～1000)	乱数(1～1000)	乱数(1～1000)

テーブル作成

  CREATE TABLE "RANDS" 
   ("id" NUMBER GENERATED ALWAYS as IDENTITY(START with 1 INCREMENT by 1),
        "rand1" NUMBER NOT NULL, 
        "rand2" NUMBER NOT NULL, 
        "rand3" NUMBER NOT NULL, 
        "rand4" NUMBER NOT NULL, 
        "rand5" NUMBER NOT NULL
   );

ダミーデータ投入

begin
  for i in 1..1000 loop
    for j in 1..10000 loop
        insert into RANDS ("rand1", "rand2", "rand3", "rand4", "rand5")values(
            FLOOR(DBMS_RANDOM.VALUE(1, 1001)),
            FLOOR(DBMS_RANDOM.VALUE(1, 1001)),
            FLOOR(DBMS_RANDOM.VALUE(1, 1001)),
            FLOOR(DBMS_RANDOM.VALUE(1, 1001)),
            FLOOR(DBMS_RANDOM.VALUE(1, 1001))
        );
    end loop;
    commit;
  end loop;
end;
/

検証

GROUP BY条件としてrand1カラム、ソート条件としてrand2カラムを使用して、実際にSQLを実行して時間を計測してみます。
なお、数回測定してほとんどブレがなかったため、各実行時間は1回目の結果のみ記載してます。

まずは、どのカラムでも同じソート条件を使った場合です。

実行してみたところ、以下の測定結果でした。

実行時間: 3.928s

SELECT 
    ANY_VALUE("rand3") KEEP (DENSE_RANK FIRST ORDER BY "rand2") AS col1,
    ANY_VALUE("rand4") KEEP (DENSE_RANK FIRST ORDER BY "rand2") AS col2,
    ANY_VALUE("rand5") KEEP (DENSE_RANK FIRST ORDER BY "rand2") AS col3
FROM RANDS
GROUP BY "rand1";

続いて、col2、col3計算時のソート条件を外してみます。
GROUP BY後の一時テーブルから任意の値を取るのもGROUP BY&ORDER BY後の一時テーブルから任意の値を取るのも大差ないはずなので、もしこれで実行時間が変わらなければ、ソート結果は使い回されているといえそうです。

実行してみたところ、以下の通りでした。

実行時間: 2.893s

SELECT 
    ANY_VALUE("rand3") KEEP (DENSE_RANK FIRST ORDER BY "rand2") AS col1,
    ANY_VALUE("rand4") AS col2,
    ANY_VALUE("rand5") AS col3
FROM RANDS
GROUP BY "rand1";

明確に時間が短くなりました。どうやらソート結果の使い回しは行われていないようです。

続いて先ほどの2つのクエリの実行計画を確認してみます。

こちら、2つともまったく同じ結果になりました。（Plan hash valueも同じになりました）
どうやら実行計画上はSELECTの1項目ごとにソートし直していることは無視されるようですね。ここまで実行時間に差が生まれるのに実行計画が同じになるのは意外でした。

よって、実行計画のCostからこの2クエリを比較することはできませんでした

（補足）Plan Hash Valueについて

レポートにあるPlan Hash Valueは実行計画ごとに付与された値です。つまり、同じ実行計画であれば同じPlan Hash Valueが付与されます。今回は、SQL Idが「b7twbdp012nbw」のもののAWR SQLレポートですが、表3-1のほかのSQLのAWR SQLレポートを作成すると、特別な理由（*）がない限り、同じPlan Hash Valueとなります。

引用: Oracleデータベース 性能対策機能 ～ StatspackとDiagnostics Packを使いこなす ～ | Oracle 日本

まとめ

分析関数は同条件だとしても再計算が走るようなので、同条件でいくつかのカラムを評価するときや多量のデータを分析するときなどは、サブクエリの使用やSQLの分割を行って最適化を図ったほうがよさそうです。

また、実行計画に表れないところで実行時間に大きく差がつくケースがあることを学びました。実行計画から確認できないため、チューニングの際などに自力で気づくことは難しいですが、そういうケースの存在を知れてよかったです。

おまけ

MySQLの場合は名前つきウィンドウという機能があり、複数のGROUP BY,ORDER BYの条件を使い分けて出力したいときもこのようにスッキリ書けるみたいです

SELECT
    FIRST_VALUE(rand3) OVER w1,
    FIRST_VALUE(rand4) OVER w1,
    LAST_VALUE(rand5) OVER w1,
    FIRST_VALUE(rand4) OVER w2,
    LAST_VALUE(rand5) OVER w2
FROM RANDS
WINDOW w1 AS (PARTITION BY rand1 ORDER BY rand2),
    w2 AS (PARTITION BY rand1 ORDER BY rand3);

検証に使用したDBバージョン

ORACLE: 21c
MySQL: 8.0

参考

• https://qiita.com/Be-cricket/items/f189c2dd5547af04676b
• https://qiita.com/ngyuki/items/c35dd314815939ffbeb6