DROP SHADOW CTRL


GaragebandとiMovieで



検索

  • Google
フォト

Twitter

2025年12月
日 月 火 水 木 金 土
  1 2 3 4 5 6
7 8 9 10 11 12 13
14 15 16 17 18 19 20
21 22 23 24 25 26 27
28 29 30 31      

気まぐれ写真館

  • spider lily - 白と赤の彼岸花（曼珠沙華）

カテゴリー

Oracle

Apple

その他

  • ブログ de ノベルティ
    ノベルティグッズをただ、ただ、記録していくブログ。Mac De Oracleでは出来ないネタなので、別ブログを立てた次第。

Googleの広告



RSS

ブログ:ココログ

当サイトについて

プライバシーポリシー
■プライバシーポリシー

■個人情報の利用目的

　当ブログでは、メールでのお問い合わせ、ブログへのコメントの際、名前（ハンドルネーム）等の個人情報をご登録いただく場合がございます。これらの個人情報は質問に対する回答などでご連絡する場合や、スパムコメント抑止のために利用させていただくものであり、個人情報をご提供いただく際の目的以外では利用いたしません。

■個人情報の第三者への開示

　当サイトでは、個人情報は適切に管理し、以下に該当する場合を除いて第三者に開示することはありません。

  • 本人のご了解がある場合
  • 法令等への協力のため、開示が必要となる場合

個人情報の開示、訂正、追加、削除、利用停止については、ご本人からの個人データの開示、訂正、追加、削除、利用停止のご希望の場合には、ご本人であることを確認させていただいた上で速やかに対応させていただきます。

■アクセス解析ツールについて

　当サイトでは、Googleによるアクセス解析ツール「Googleアナリティクス」を利用しています。このGoogleアナリティクスはトラフィックデータの収集のためにCookieを使用しています。このトラフィックデータは匿名で収集されており、個人を特定するものではありません。この機能はCookieを無効にすることで収集を拒否することが出来ますので、お使いのブラウザの設定をご確認ください。

  • 目的：アクセス数を把握するため
  • 送信先：Google LCC
  • Googleは受け取った情報をどのように利用するか：Googleの規約に従います
  • 送信される情報：以下４点(R), ユーザー数、セッションの統計情報、おおよその位置情報、ブラウザとデバイスの情報

■広告の配信について

当サイトはGoogleとAmazonの広告を配信しています。

Googleについて

Google及びGoogleのパートナー（第三者配信事業者）の提供する広告を設置しております。その広告配信にはCookieを使用し、当サイトやその他のサイトへの過去のアクセス情報に基づいて広告を配信します。

Google が広告 Cookie を使用することにより、当サイトや他のサイトにアクセスした際の情報に基づいて、Google やそのパートナーが適切な広告を表示しています。

お客様はGoogleアカウントの広告設定ページ（https://adssettings.google.com/u/0/authenticated）で、パーソナライズ広告を無効にできます。また aboutads.info のページにアクセスして頂き、パーソナライズ広告掲載に使用される第三者配信事業者のCookieを無効にできます。

その他、Googleの広告におけるCookieの取り扱い詳細については、Googleのポリシーと規約ページ（https://policies.google.com/technologies/ads）をご覧ください。

Amazonについて

AmazonはアソシエイトリンクのURLに含まれるパラメータを通じて利用者が直前に閲覧したアソシエイトサイトに関する情報などを収集し、これをアソシエイトパートナーに対する紹介料の算定、報告及び支払いに使用したり、アソシエイトパートナーによる複数のウェブサイトや商品戦略毎のパフォーマンスの分析に供したりしています。

  • アソシエイトパートナーの利用目的：閲覧の傾向や履歴の分析のため広告効果の分析のため
  • 外部送信プログラム名：Amazonアソシエイトプログラム
  • 送信先の会社名：アマゾンジャパン合同会社
  • 送信する情報の内容：閲覧した内容についての情報送信先の利用目的：自社の広告宣伝を目的とした広告情報の掲載のため、ウェブサイトやアプリの監視のため

Amazonによる個人に関する情報の取得及び取扱いに関する方針につきましてはAmazon.co.jpプライバシー規約をご覧ください。

■免責事項

　当サイトからリンクやバナーなどによって他のサイトに移動された場合、移動先サイトで提供される情報、サービス等について一切の責任を負いません。当サイトのコンテンツ・情報につきまして、可能な限り正確な情報を掲載するよう努めておりますが、誤情報が入り込んだり、情報が古くなっていることもございます。当サイトに掲載された内容によって生じた損害等の一切の責任を負いかねますのでご了承ください。

■プライバシーポリシーの変更について

　当サイトは、個人情報に関して適用される日本の法令を遵守するとともに、本ポリシーの内容を適宜見直しその改善に努めます。
修正された最新のプライバシーポリシーは常に本ページにて開示されます。

2025年9月30日 (火)

帰ってきた！ 標準はあるにはあるが癖の多いSQL #19 - c_alias の癖（おまけ）

書き漏らしていたことに気づいたので、
今日のテーマは、

帰ってきた！ 標準はあるにはあるが癖の多いSQL #18 - t_alias と c_alias にも癖が出る

の癖のおまけw

Oracle Database、PostgreSQL、MySQLの表エイリアスと列エイリアスの文法の癖の違いを思い出してもらった上で、今日の列エイリアスの癖も合わせて覚えておくと良いかもしれません。

とは言え、サブクエリで該当構文を使うことはあまり無さそうな気はします。
どちらかというと、表値コンストラクタの記述で効果的な構文だと思うので。。。（表値コンストラクタネタは、別エントリーにて）

 

まず、帰ってきた！ 標準はあるにはあるが癖の多いSQL #18 - t_alias と c_alias にも癖が出るの列エイリアスの癖の復習から。

 

インラインビュー（サブクエリ）の列エイリアスを以下のような構文で書けるDBと書けないDBを見てみましょう。

SELECT
  *
FROM
(
   SELECT
     empno
     , 'a'
   FROM
     emp
) t01 (empno, dummy_col)
;

 

Oracle Database 23ai ver. 23.8 Oracle Databaseではこの構文は許されていませんよね！　表値コンストラクタがサポートされた流れで通常のサブクエリでも使えるようになるかもしれませんが、、、、。

SCOTT@localhost:1521/freepdb1> select banner_full from v$version;

BANNER_FULL
--------------------------------------------------------------------------------
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.8.0.25.04

SCOTT@localhost:1521/freepdb1> l
  1  SELECT
  2    *
  3  FROM
  4  (
  5     SELECT
  6       empno
  7       , 'a'
  8     FROM
  9       emp
 10  ) t01 (empno, dummy_col)
 11*
COTT@localhost:1521/freepdb1> /
) t01 (empno, dummy_col)
      *
行10でエラーが発生しました。:
ORA-03048: SQL予約語'('は、'..., 'a'
FROM
emp
) t01 'の後では構文的に有効ではありません ヘルプ:
https://docs.oracle.com/error-help/db/ora-03048/

 

 

 

MySQL 8.4 MySQLでは可能でしたよね。

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.4.6     |
+-----------+

mysql> SELECT
    ->   *
    -> FROM
    -> (
    ->    SELECT
    ->      empno
    ->      , 'a'
    ->    FROM
    ->      emp
    -> ) t01 (empno, dummy_col)
    -> ;
+-------+-----------+
| empno | dummy_col |
+-------+-----------+
|  7782 | a         |
|  7839 | a         |

...略...

|  7844 | a         |
|  7900 | a         |
+-------+-----------+
14 rows in set (0.01 sec)

 

 

PostgreSQL 17.5 PostgreSQLでも可能ですよね。
ここまでは、帰ってきた！ 標準はあるにはあるが癖の多いSQL #18 - t_alias と c_alias にも癖が出るでも書いてました。

perftestdb=> SELECT
perftestdb->   *
perftestdb-> FROM
perftestdb-> (
perftestdb(>    SELECT
perftestdb(>      empno
perftestdb(>      , 'a'
perftestdb(>    FROM
perftestdb(>      emp
perftestdb(> ) t01 (empno, dummy_col)
perftestdb-> ;
 empno | dummy_col 
-------+-----------
  7369 | a
  7499 | a

...略...

  7902 | a
  7934 | a
(14 rows)

 

で、書き忘れていたのは以降の癖。

以下のように、複数ある列の一方だけのつもりで、列エイリアスを書いたら。。。。どうなるか。

どうなると思います？

SELECT
  *
FROM
(
   SELECT
     empno
     , 'a'
   FROM
     emp
) t01 (dummy_col)
;

 

 

Oracle Database 23ai 23.8 Oracle Databaseではそもそもサブクエリに対するこの構文は許されていないのでエラーです。

SCOTT@localhost:1521/freepdb1> l
  1  SELECT
  2    *
  3  FROM
  4  (
  5     SELECT
  6       empno
  7       , 'a'
  8     FROM
  9       emp
 10  ) t01 (dummy_col)
 11*
10:30:52 SCOTT@localhost:1521/freepdb1> /
) t01 (dummy_col)
      *
行10でエラーが発生しました。:
ORA-03048: SQL予約語'('は、'..., 'a'
FROM
emp
) t01 'の後では構文的に有効ではありません ヘルプ:
https://docs.oracle.com/error-help/db/ora-03048/

 

MySQL 8.4.5 おお！　変化しましたね。 MySQLでは全列定義しないとエラーになるようです。全列のエイリアスを指定するか、しないかのどちらかということですね！　わかりやすい気がしますね。これ。

mysql> SELECT
    ->   *
    -> FROM
    -> (
    ->    SELECT
    ->      empno
    ->      , 'a'
    ->    FROM
    ->      emp
    -> ) t01 (dummy_col)
    -> ;
ERROR 1353 (HY000): In definition of view, derived table or common table expression, SELECT list and column names list have different column counts

 

PostgreSQL 17.5 では、真打w PostgreSQL。PostreSQLでは一部でも構文エラーにはならない！！！！　まじか、じゃ、指定した列エイリアスはどの列を対象にするの？！！！！！！

サブクエリの列数と同数の列エイリアスを指定しない場合、SELECTリストの列順に対応させているだけのようですね！。この例では、empno列と、無名の列の2列がありますが、列エイリアスで置き換えられたのは、最初のempno列！です

エラーにならないだけに、ちょっと注意が必要な癖ですよね！
エラーにはならないことが、正しいというわけでもないので。。。このケースでは意図しない列名を列エイリアスで置換してしまっているわけで。。MySQLのようにエラーにしてくれた方が嬉しいのではないだろうか。

perftestdb=> SELECT
perftestdb->   *
perftestdb-> FROM
perftestdb-> (
perftestdb(>    SELECT
perftestdb(>      empno
perftestdb(>      , 'a'
perftestdb(>    FROM
perftestdb(>      emp
perftestdb(> ) t01 (dummy_col)
perftestdb-> ;
 dummy_col | ?column? 
-----------+----------
      7369 | a
      7499 | a

...略...

      7902 | a
      7934 | a
(14 rows)

 

最後に、サブクエリで、各列毎に列エイリアスを指定した方が可読性は良いと考えているので、その比較用w
（前述した列エイリアス構文が、その威力を発揮するのは表値コンストラクタを利用する場合ぐらいになるだろうと思っています）

 

こんな感じでも、

SELECT
  *
FROM
(
   SELECT
     empno 
     , 'a' AS dummy_col
   FROM
     emp
) t01
;

 

下記のような場合でも読みやすいとおもいます:)

SELECT
  *
FROM
(
   SELECT
     empno AS empno
     , 'a' AS dummy_col
   FROM
     emp
) t01
;

 

 

Oracle

SCOTT@localhost:1521/freepdb1> l  1  SELECT
  2    *
  3  FROM
  4  (
  5     SELECT
  6       empno
  7       , 'a' AS dummy_col
  8     FROM
  9       emp
 10  ) t01
 11*
SCOTT@localhost:1521/freepdb1> /

     EMPNO DUMMY_COL
---------- ---------
      7369 a
      7499 a

...略...

      7902 a
      7934 a

14行が選択されました。

SCOTT@localhost:1521/freepdb1> l
  1  SELECT
  2    *
  3  FROM
  4  (
  5     SELECT
  6       empno AS empno
  7       , 'a' AS dummy_col
  8     FROM
  9       emp
 10  ) t01
 11*
SCOTT@localhost:1521/freepdb1> /

     EMPNO DUMMY_COL
---------- ---------
      7369 a
      7499 a

...略...

      7902 a
      7934 a

14行が選択されました。

 

 

MySQL

mysql> SELECT
    ->   *
    -> FROM
    -> (
    ->    SELECT
    ->      empno 
    ->      , 'a' AS dummy_col
    ->    FROM
    ->      emp
    -> ) t01
    -> ;
+-------+-----------+
| empno | dummy_col |
+-------+-----------+
|  7782 | a         |
|  7839 | a         |

...略...

|  7844 | a         |
|  7900 | a         |
+-------+-----------+
14 rows in set (0.00 sec)

mysql> SELECT
    ->   *
    -> FROM
    -> (
    ->    SELECT
    ->      empno AS empno
    ->      , 'a' AS dummy_col
    ->    FROM
    ->      emp
    -> ) t01
    -> ;
+-------+-----------+
| empno | dummy_col |
+-------+-----------+
|  7782 | a         |
|  7839 | a         |

...略...

|  7844 | a         |
|  7900 | a         |
+-------+-----------+
14 rows in set (0.00 sec)

 

 

PostgreSQL

perftestdb=> SELECT
perftestdb->   *
perftestdb-> FROM
perftestdb-> (
perftestdb(>    SELECT
perftestdb(>      empno 
perftestdb(>      , 'a' AS dummy_col
perftestdb(>    FROM
perftestdb(>      emp
perftestdb(> ) t01
perftestdb-> ;
 empno | dummy_col 
-------+-----------
  7369 | a
  7499 | a

...略...

  7902 | a
  7934 | a
(14 rows)

perftestdb=> SELECT
perftestdb->   *
perftestdb-> FROM
perftestdb-> (
perftestdb(>    SELECT
perftestdb(>      empno AS empno
perftestdb(>      , 'a' AS dummy_col
perftestdb(>    FROM
perftestdb(>      emp
perftestdb(> ) t01
perftestdb-> ;
 empno | dummy_col
-------+-----------
  7369 | a
  7499 | a

...略...

  7902 | a
  7934 | a
(14 rows)

 

癖にも色々あります。
PostgreSQLのケースではエラーにならないからといって、だいじょーふだ！！、とは言えないタイプも癖もあます。

別エントリーで予定している表値コンストラクタではこの列エイリアスの構文が重要だったりするので、混乱しないよう、
効果的な場所で使っていく必要はありそうだなぁと思っているところ:)
Oracleのように、そもそも表値エイリアスでしか使えない場合は悩む必要もないわけですが。（いまのところ）

 

やっと東京も東北方面の気温に近づきつつある。。気もするw

では、また。

 

Enjoy SQL and 癖 !

 

関連エントリー

・標準はあるにはあるが癖の多いSQL 全部俺　#1 Pagination
・標準はあるにはあるが癖の多いSQL 全部俺　#2 関数名は同じでも引数が逆の罠！
・標準はあるにはあるが癖の多いSQL 全部俺　#3 データ型確認したい時あるんです
・標準はあるにはあるが癖の多いSQL 全部俺　#4 リテラル値での除算の内部精度も違うのよ!
・標準はあるにはあるが癖の多いSQL 全部俺　#5 和暦変換機能ある方が少数派
・標準はあるにはあるが癖の多いSQL 全部俺　#6 時間厳守！
・標準はあるにはあるが癖の多いSQL 全部俺　#7 期間リテラル！
・標準はあるにはあるが癖の多いSQL 全部俺　#8 翌月末日って何日？
・標準はあるにはあるが癖の多いSQL 全部俺　#9 部分文字列の扱いでも癖が出る＞＜
・標準はあるにはあるが癖の多いSQL 全部俺　#10 文字列連結の罠（有名なやつ）
・標準はあるにはあるが癖の多いSQL 全部俺　#11 デュエル、じゃなくて、デュアル
・標準はあるにはあるが癖の多いSQL 全部俺　#12 文字[列]探すにも癖がある
・標準はあるにはあるが癖の多いSQL 全部俺　#13 あると便利ですが意外となかったり
・標準はあるにはあるが癖の多いSQL 全部俺　#14 連番の集合を返すにも癖がある
・標準はあるにはあるが癖の多いSQL 全部俺　#15 SQL command line client
・標準はあるにはあるが癖の多いSQL 全部俺　#16 SQLのレントゲンを撮る方法
・標準はあるにはあるが癖の多いSQL 全部俺　#17 その空白は許されないのか？
・標準はあるにはあるが癖の多いSQL 全部俺　#18 (+)の外部結合は方言
・標準はあるにはあるが癖の多いSQL 全部俺　#19 帰ってきた、部分文字列の扱いでも癖w
・標準はあるにはあるが癖の多いSQL 全部俺　#20 結果セットを単一列に連結するにも癖がある
・標準はあるにはあるが癖の多いSQL 全部俺　#21 演算結果にも癖がある
・標準はあるにはあるが癖の多いSQL 全部俺　#22 集合演算にも癖がある
・標準はあるにはあるが癖の多いSQL 全部俺　#23 複数行INSERTにも癖がある
・標準はあるにはあるが癖の多いSQL 全部俺　#24 乱数作るにも癖がある
・標準はあるにはあるが癖の多いSQL 全部俺　#25 SQL de Fractalsにも癖がある:)
・標準はあるにはあるが癖の多いSQL 全部俺　おまけ SQL de 湯婆婆やるにも癖がでるw
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #1 SQL de ROT13 やるにも癖が出るw
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #2 Actual Plan取得中のキャンセルでも癖が出る
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #3 オプティマイザの結合順評価テーブル数上限にも癖が出る
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #4 Optimizer Traceの取得でも癖がでる
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #5 - Optimizer Hint でも癖が多い
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #6 - Hash Joinの結合ツリーにも癖がでる
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #7 - Hash Joinの実行計画にも癖がでる
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #8 - Hash Joinさせるにも癖が出る
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #9、BOOLEAN型にも癖が出る
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #10、BOOLEAN型にも癖が出る（後編）
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #10、BOOLEAN型にも癖が出る（後編）の　おまけ　- SQL*PlusのautotraceでSQL Analysis Reportが出力される！ (23ai〜)
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #11 - 引用符にも癖がでるし、NULLのソート構文にも癖がある！（前編）
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #12 - 引用符にも癖がでるし、NULLのソート構文にも癖がある！（後編）ー 列エイリアスの扱いにも癖がある！
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #13 - コメント書くにも癖がある
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #14 - コメントを書く位置にも癖がでる (SQL Clientにも癖がある)
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #15 - 実行計画でスカラー副問合せの見せ方にも癖がでる
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #16 - FROM句のインラインビューのエイリアスにもクセがある（必須だったり、任意だったり）
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #17 - ANY_VALUE() ってなかなかいいじゃん、癖無さそう！
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #18 - t_alias と c_alias にも癖が出る

投稿日時 2025年9月30日 (火) 17時00分 Oracle, FAQ, SQL, MySQL, PostgreSQL, Oracle Database 23ai | | | コメント (0)

2025年8月22日 (金)

帰ってきた！ 標準はあるにはあるが癖の多いSQL #17 - ANY_VALUE() ってなかなかいいじゃん、癖無さそう！

さて、
今回は久々に、標準はあるにはあるが癖の多いSQLシリーズです！　

データが小さいとその価値はほぼわからないかもしれませんが、ひょんなところで出会ってしまった！ と、いうような状況で役立つかもしれませんwwww

今日のお題は、ANY_VALUE()関数。　集約関数の仲間です:)

MySQLやOracle Databaseのマニュアルでは使い所を理解しやすい解説があります。おすすめです。
一方、PostgreSQLのマニュアルシンプルすぎる解説ゆえ、この関数はなに？　なにが美味しいの？　みたいな顔になってしまうかもしれません。がw、ググってみてください、いろいろ見つかります！

この関数に出会ってよかった！　ということを思いながらw
以下の曲をBGMにして眺めてみてください:)

ラブ・ストーリーは突然に / 小田和正

少量のデータだとその良さに気づきにくいのですが、万が一の時は、ANY_VALUE()集約関数を思い出してみてください。
リソース消費は数が多くなるとボディーブローにはなるので、リソース使用量削減に重箱の隅をつつくようなことしないといけないとかw そんな時にも役立つかも。。。しれないです。

参考）

Oracle / ANY_VALUE() - 19cからサポートされました
https://docs.oracle.com/cd/F19136_01/sqlrf/any_value.html

MySQL / ANY_VALUE() - 5.7からサポートされました
https://dev.mysql.com/doc/refman/8.0/ja/miscellaneous-functions.html#function_any-value

PostgreSQL / ANY_VALUE() - 16からサポートされました
https://www.postgresql.jp/document/17/html/functions-aggregate.html

環境

HostOS : macOS Sonoma 14.7.7 (arm64)
VirtualBox 7.1 (arm64)

GuestOS : Oracle Linux 8u10 (arm64)
　Oracle Database 23ai 23.8 (arm64)
　PostgreSQL 17.5 (arm64)
　MySQL 8.4.6 (arm64)

テストケース

テストケース1)

集約する列データ長が長がーーーい

テストケース2)

集約する列データは短めでもデータ量が多いケース

の2つを用意しました。
データ量はどちらも多めにしました。理由は、集計関数やGROUP BYの性能差分はデータ量が少ないと差分が見にくいためです:)
これぐらいデータにして、やっと、ふむふむと頷ける差分が見えるのではないかと思います。

計測は３回実行しています（１回目には諸々ノイズが乗りやすいので参考程度にしています）

 

では、先に結果から。
全体的に ANY_VALUE()が軽めの傾向として出てきているのは間違いないと思います。あえてそういう目的で追加してきた関数ですし。MySQLやOracle DatabaseのマニュアルではANY_VALUE()集約関数についての解説もわかりやすいとおもいます。
ANY_VALUE()の用途が広く認知されれば、可読性向上という意味もきっちり出てきそうな気はします。（個人的にはw。今は微妙は感じを持っている方は多いと思いますが、非集約列をGROUP BY句に記述するのも、MIN/MAX集約関数を使うのも可読性という意味では微妙だと思っているので、そういう目的の関数の登場で方向は定まるのではないかと。。。。w）
PostgreSQLのマニュアルに目を向けると、他の関数の説明とのバランスもあると思うのでw、さらりと書かれていて、初めて見た方は、君は何？　
という感じになりそうではあるのですが、ググると結構情報も多くなってきたので何ものかを知るのに困ることもないと思います。

 

個別のまとめ

Oracle Database 23ai free

環境による差異は多少ありそうですが、GROUP BYで対処する場合とANY_VALUE()で対処する場合では、列サイズが長い場合にはANY_VALUE()の方が効果的に対処できそうですね。CPUに優しくなっています。
一方該当列の列サイズが比較的短い場合には、GROUP BY / ANY_VALUE()大きな差はでにくです。ANYU_VALUE()の認知度次第ですが、この手のハンドリングのための記述として認知度があがると、可読性としては向上しそうな気がします。

注）軽かった順に列挙してます。

テストケース1)

ANY_VALUE() -> GROUP BY句で対処 -> MAX()

テストケース2)

ANY_VALUE() -> GROUP BY句で対処 -> MAX()

 

PostgreSQL

PostgreSQLでは、やはり、ANY_VALUE()が早いですが、MAX()とかなり近い結果となり、GROUP BYが最も遅いという結果になりました。
面白い。

テストケース1)

ANY_VALUE() -> MAX() -> GROUP BY句で対処

テストケース2)

ANY_VALUE() -> MAX() -> GROUP BY句で対処

 

MySQL

MySQLではすべてが、Aggregate using temporary table　となっていたので条件的には同じ状態で比較できた分わかりやすい結果になっていました。
MySQLでもこの手のケースでANY_VALUE()を利用しておいた方がお得でしょうね。

テストケース1)

ANY_VALUE() -> MAX() -> GROUP BY句で対処

テストケース2)

ANY_VALUE() -> MAX() -> GROUP BY句で対処

 

Oracle Databaseでの処理時間まとめ

SQLモニターを利用して取得.

列サイズ長め GROUP BY

Global Stats
==============================================================================
| Elapsed |   Cpu   |    IO    | Application | Fetch | Buffer | Read | Read  |
| Time(s) | Time(s) | Waits(s) |  Waits(s)   | Calls |  Gets  | Reqs | Bytes |
==============================================================================
|    0.67 |    0.56 |     0.11 |        0.00 |     2 |   250K | 1981 |   2GB |
|    0.62 |    0.53 |     0.09 |             |     2 |   250K | 1981 |   2GB |
|    0.61 |    0.52 |     0.09 |             |     2 |   250K | 1981 |   2GB |
==============================================================================

 

MAX

Global Stats
================================================================
| Elapsed |   Cpu   |    IO    | Fetch | Buffer | Read | Read  |
| Time(s) | Time(s) | Waits(s) | Calls |  Gets  | Reqs | Bytes |
================================================================
|    0.78 |    0.67 |     0.11 |     2 |   250K | 1981 |   2GB |
|    0.74 |    0.65 |     0.09 |     2 |   250K | 1981 |   2GB |
|    0.73 |    0.65 |     0.09 |     2 |   250K | 1981 |   2GB |
================================================================

 

ANY_VALUE

Global Stats
================================================================
| Elapsed |   Cpu   |    IO    | Fetch | Buffer | Read | Read  |
| Time(s) | Time(s) | Waits(s) | Calls |  Gets  | Reqs | Bytes |
================================================================
|    0.34 |    0.25 |     0.09 |     2 |   250K | 1981 |   2GB |
|    0.33 |    0.25 |     0.09 |     2 |   250K | 1981 |   2GB |
|    0.34 |    0.25 |     0.09 |     2 |   250K | 1981 |   2GB |
================================================================

 

列サイズ短めで件数が多い

GROUP BY

Global Stats
================================================================
| Elapsed |   Cpu   |    IO    | Fetch | Buffer | Read | Read  |
| Time(s) | Time(s) | Waits(s) | Calls |  Gets  | Reqs | Bytes |
================================================================
|    1.65 |    1.42 |     0.23 |     2 |   171K | 1363 |   1GB |
|    1.15 |    1.10 |     0.06 |     2 |   171K | 1363 |   1GB |
|    1.16 |    1.10 |     0.06 |     2 |   171K | 1363 |   1GB |
================================================================

 

MAX

Global Stats
================================================================
| Elapsed |   Cpu   |    IO    | Fetch | Buffer | Read | Read  |
| Time(s) | Time(s) | Waits(s) | Calls |  Gets  | Reqs | Bytes |
================================================================
|    1.66 |    1.55 |     0.12 |     2 |   171K | 1363 |   1GB |
|    1.29 |    1.23 |     0.06 |     2 |   171K | 1363 |   1GB |
|    1.29 |    1.23 |     0.06 |     2 |   171K | 1363 |   1GB |
================================================================

 

ANY_VALUE

Global Stats
================================================================
| Elapsed |   Cpu   |    IO    | Fetch | Buffer | Read | Read  |
| Time(s) | Time(s) | Waits(s) | Calls |  Gets  | Reqs | Bytes |
================================================================
|    1.47 |    1.38 |     0.10 |     2 |   171K | 1363 |   1GB |
|    1.15 |    1.09 |     0.06 |     2 |   171K | 1363 |   1GB |
|    1.15 |    1.09 |     0.06 |     2 |   171K | 1363 |   1GB |
================================================================

 

PostgreSQL

PostgreSQLのwork_memが小さかった影響で、GROUP BYによる対処ではTemp落ちして一人負けしてました。設定チューニングしていたらMAX/ANY_VALUEに近い結果になっていたかもね。（Oracleみたいにデフォでいい感じってあれではなかった、しくじりw）
とはいえ、MAX()とANY_VALUE()の差があまりないのもPostgreSQLの特徴ですかね。

 

列サイズ長め

GROUP BY

 Execution Time: 3533.979 ms
 Execution Time: 3570.032 ms
 Execution Time: 3553.467 ms

 

MAX

 Execution Time: 439.917 ms
 Execution Time: 434.463 ms
 Execution Time: 434.348 ms

 

ANY_VALUE

 Execution Time: 461.668 ms
 Execution Time: 444.132 ms
 Execution Time: 431.056 ms

 

 

列サイズ短めで件数が多い

GROUP BY

 Execution Time: 4300.000 ms
 Execution Time: 4635.630 ms
 Execution Time: 4595.763 ms

 

MAX

 Execution Time: 4449.759 ms
 Execution Time: 4449.591 ms
 Execution Time: 4425.708 ms

 

ANY_VALUE

 Execution Time: 4240.994 ms
 Execution Time: 4145.707 ms
 Execution Time: 4018.328 ms

 

MySQL

列サイズ長め

GROUP BY

1 row in set (13.16 sec)
1 row in set (13.03 sec)
1 row in set (13.03 sec)

 

MAX

1 row in set (8.59 sec)
1 row in set (8.60 sec)
1 row in set (9.40 sec)

 

ANY_VALUE

1 row in set (0.49 sec)
1 row in set (0.43 sec)
1 row in set (0.49 sec)

 

列サイズ短めで件数が多い

GROUP BY

1 row in set (21.31 sec)
1 row in set (21.90 sec)
1 row in set (20.89 sec)

 

MAX

1 row in set (15.68 sec)
1 row in set (16.16 sec)
1 row in set (16.16 sec)

 

ANY_VALUE

1 row in set (8.35 sec)
1 row in set (8.54 sec)
1 row in set (8.20 sec)

 

ということで、

帰ってきた！ 標準はあるにはあるが癖の多いSQL #17 - ANY_VALUE() 、
癖があるとおもったのですが、癖はなかったですw

では、また。

 

朝晩の風が、あきっぽい、北のエリアより。。。夏祭りが終われば、あっというまに秋、、、になるはずw

Enjoy SQL! and RDBMS!

 

関連エントリー

・標準はあるにはあるが癖の多いSQL 全部俺　#1 Pagination
・標準はあるにはあるが癖の多いSQL 全部俺　#2 関数名は同じでも引数が逆の罠！
・標準はあるにはあるが癖の多いSQL 全部俺　#3 データ型確認したい時あるんです
・標準はあるにはあるが癖の多いSQL 全部俺　#4 リテラル値での除算の内部精度も違うのよ!
・標準はあるにはあるが癖の多いSQL 全部俺　#5 和暦変換機能ある方が少数派
・標準はあるにはあるが癖の多いSQL 全部俺　#6 時間厳守！
・標準はあるにはあるが癖の多いSQL 全部俺　#7 期間リテラル！
・標準はあるにはあるが癖の多いSQL 全部俺　#8 翌月末日って何日？
・標準はあるにはあるが癖の多いSQL 全部俺　#9 部分文字列の扱いでも癖が出る＞＜
・標準はあるにはあるが癖の多いSQL 全部俺　#10 文字列連結の罠（有名なやつ）
・標準はあるにはあるが癖の多いSQL 全部俺　#11 デュエル、じゃなくて、デュアル
・標準はあるにはあるが癖の多いSQL 全部俺　#12 文字[列]探すにも癖がある
・標準はあるにはあるが癖の多いSQL 全部俺　#13 あると便利ですが意外となかったり
・標準はあるにはあるが癖の多いSQL 全部俺　#14 連番の集合を返すにも癖がある
・標準はあるにはあるが癖の多いSQL 全部俺　#15 SQL command line client
・標準はあるにはあるが癖の多いSQL 全部俺　#16 SQLのレントゲンを撮る方法
・標準はあるにはあるが癖の多いSQL 全部俺　#17 その空白は許されないのか？
・標準はあるにはあるが癖の多いSQL 全部俺　#18 (+)の外部結合は方言
・標準はあるにはあるが癖の多いSQL 全部俺　#19 帰ってきた、部分文字列の扱いでも癖w
・標準はあるにはあるが癖の多いSQL 全部俺　#20 結果セットを単一列に連結するにも癖がある
・標準はあるにはあるが癖の多いSQL 全部俺　#21 演算結果にも癖がある
・標準はあるにはあるが癖の多いSQL 全部俺　#22 集合演算にも癖がある
・標準はあるにはあるが癖の多いSQL 全部俺　#23 複数行INSERTにも癖がある
・標準はあるにはあるが癖の多いSQL 全部俺　#24 乱数作るにも癖がある
・標準はあるにはあるが癖の多いSQL 全部俺　#25 SQL de Fractalsにも癖がある:)
・標準はあるにはあるが癖の多いSQL 全部俺　おまけ SQL de 湯婆婆やるにも癖がでるw
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #1 SQL de ROT13 やるにも癖が出るw
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #2 Actual Plan取得中のキャンセルでも癖が出る
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #3 オプティマイザの結合順評価テーブル数上限にも癖が出る
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #4 Optimizer Traceの取得でも癖がでる
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #5 - Optimizer Hint でも癖が多い
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #6 - Hash Joinの結合ツリーにも癖がでる
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #7 - Hash Joinの実行計画にも癖がでる
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #8 - Hash Joinさせるにも癖が出る
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #9、BOOLEAN型にも癖が出る
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #10、BOOLEAN型にも癖が出る（後編）
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #10、BOOLEAN型にも癖が出る（後編）の　おまけ　- SQL*PlusのautotraceでSQL Analysis Reportが出力される！ (23ai〜)
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #11 - 引用符にも癖がでるし、NULLのソート構文にも癖がある！（前編）
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #12 - 引用符にも癖がでるし、NULLのソート構文にも癖がある！（後編）ー 列エイリアスの扱いにも癖がある！
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #13 - コメント書くにも癖がある
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #14 - コメントを書く位置にも癖がでる (SQL Clientにも癖がある)
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #15 - 実行計画でスカラー副問合せの見せ方にも癖がでる
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #16 - FROM句のインラインビューのエイリアスにもクセがある（必須だったり、任意だったり）

 

以下、興味のある方向けのログと今回適当に作ったデータ作成スクリプトなどを載せています。以降は長いので興味のない方は飛ばしてくださいww

ーーーーーーーログーーーーーーー

Oracle Database

-- 列サイズ長め（準備）
SCOTT@localhost:1521/freepdb1> @any_value.sql
  1* DROP TABLE IF EXISTS any_value_table PURGE

表が削除されました。

経過: 00:00:00.20
  1  CREATE TABLE any_value_table
  2  (
  3    ordered_date DATE NOT NULL
  4    , order_id INTEGER NOT NULL
  5    , product_id INTEGER NOT NULL
  6    , product_name VARCHAR(2000) NOT NULL
  7    , qty INTEGER NOT NULL
  8    , CONSTRAINT pk_any_value_table PRIMARY KEY (order_id, product_id, ordered_date)
  9* )

表が作成されました。

経過: 00:00:00.04
  1  DECLARE
  2    o_date DATE := SYSDATE;
  3  BEGIN
  4    FOR i IN 1..1000000 LOOP
  5      INSERT INTO any_value_table
  6        (ordered_date
  7         , order_id
  8         , product_id
  9         , product_name
 10         , qty
 11        ) VALUES (o_date, i, 1, 'ITEM_1'||lpad('*',1600,'*'), 1);
 12      IF mod(i,100) = 0 THEN commit; END IF;
 13    END LOOP;
 14* END;

PL/SQLプロシージャが正常に完了しました。

経過: 00:02:58.25

PL/SQLプロシージャが正常に完了しました。

経過: 00:00:42.26

SEGMENT_NAME                           GB
------------------------------ ----------
ANY_VALUE_TABLE                1.96582031

経過: 00:00:00.03

...中略...

-- ２回目
非集計列がGROUP BY句に定義もされず、集計関数も利用されていない場合は、エラーになることの確認！
SCOTT@localhost:1521/freepdb1> @any_value_test
  1  SELECT
  2    product_id
  3    ,product_name
  4    ,SUM(qty) AS total
  5  FROM
  6    any_value_table
  7  GROUP BY
  8*   product_id
  ,product_name
   *
行3でエラーが発生しました。:
ORA-00979: "PRODUCT_NAME": GROUP BY句に出現するか、集計関数で使用される必要があります 
ヘルプ:
https://docs.oracle.com/error-help/db/ora-00979/

経過: 00:00:00.01
  1  SELECT /*+ MONITOR */
  2    product_id
  3    ,product_name
  4    ,SUM(qty) AS total
  5  FROM
  6    any_value_table
  7  GROUP BY
  8    product_id
  9*   , product_name

PRODUCT_ID PRODUCT_NAME　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　      TOTAL
---------- ----------------------------------------------------------------------------------------------------------- ----------
         1 ITEM_1*****************************************************************************************************    1000000
           ***********************************************************************************************************

...中略...

           ***********************************************************************************************************
           ***********************************************************************************************************
           *
           
経過: 00:00:00.58

...中略...

SQL Text
------------------------------
SELECT /*+ MONITOR */ product_id ,product_name 
,SUM(qty) AS total FROM any_value_table 
GROUP BY product_id , product_name

...中略...

Global Stats
================================================================
| Elapsed |   Cpu   |    IO    | Fetch | Buffer | Read | Read  |
| Time(s) | Time(s) | Waits(s) | Calls |  Gets  | Reqs | Bytes |
================================================================
|    0.62 |    0.53 |     0.09 |     2 |   250K | 1981 |   2GB |
================================================================

SQL Plan Monitoring Details (Plan Hash Value=3772843140)
=====================================================================================================================================================
| Id |      Operation       |      Name       |  Rows   | Cost  |   Time    | Start  | Execs |   Rows   | Read | Read  | Activity | Activity Detail |
|    |                      |                 | (Estim) |       | Active(s) | Active |       | (Actual) | Reqs | Bytes |   (%)    |   (# samples)   |
=====================================================================================================================================================
|  0 | SELECT STATEMENT     |                 |         |       |         1 |     +1 |     1 |        1 |      |       |          |                 |
|  1 |   HASH GROUP BY      |                 |       1 | 69634 |         1 |     +1 |     1 |        1 |      |       |   100.00 | Cpu (1)         |
|  2 |    TABLE ACCESS FULL | ANY_VALUE_TABLE |      1M | 69619 |         1 |     +1 |     1 |       1M | 1981 |   2GB |          |                 |
=====================================================================================================================================================

...中略...

SQL Text
------------------------------
SELECT /*+ MONITOR */ product_id ,MAX(product_name) AS product_name 
,SUM(qty) AS total FROM any_value_table 
GROUP BY product_id

...中略...

Global Stats
================================================================
| Elapsed |   Cpu   |    IO    | Fetch | Buffer | Read | Read  |
| Time(s) | Time(s) | Waits(s) | Calls |  Gets  | Reqs | Bytes |
================================================================
|    0.74 |    0.65 |     0.09 |     2 |   250K | 1981 |   2GB |
================================================================

SQL Plan Monitoring Details (Plan Hash Value=3772843140)
=====================================================================================================================================================
| Id |      Operation       |      Name       |  Rows   | Cost  |   Time    | Start  | Execs |   Rows   | Read | Read  | Activity | Activity Detail |
|    |                      |                 | (Estim) |       | Active(s) | Active |       | (Actual) | Reqs | Bytes |   (%)    |   (# samples)   |
=====================================================================================================================================================
|  0 | SELECT STATEMENT     |                 |         |       |         1 |     +0 |     1 |        1 |      |       |          |                 |
|  1 |   HASH GROUP BY      |                 |       1 | 69634 |         1 |     +0 |     1 |        1 |      |       |          |                 |
|  2 |    TABLE ACCESS FULL | ANY_VALUE_TABLE |      1M | 69619 |         1 |     +0 |     1 |       1M | 1981 |   2GB |          |                 |
=====================================================================================================================================================

...中略...

SQL Text
------------------------------
SELECT /*+ MONITOR */ product_id ,ANY_VALUE(product_name) AS product_name 
,SUM(qty) AS total FROM any_value_table 
GROUP BY product_id

...中略...

Global Stats
================================================================
| Elapsed |   Cpu   |    IO    | Fetch | Buffer | Read | Read  |
| Time(s) | Time(s) | Waits(s) | Calls |  Gets  | Reqs | Bytes |
================================================================
|    0.33 |    0.25 |     0.09 |     2 |   250K | 1981 |   2GB |
================================================================

SQL Plan Monitoring Details (Plan Hash Value=3772843140)
=====================================================================================================================================================
| Id |      Operation       |      Name       |  Rows   | Cost  |   Time    | Start  | Execs |   Rows   | Read | Read  | Activity | Activity Detail |
|    |                      |                 | (Estim) |       | Active(s) | Active |       | (Actual) | Reqs | Bytes |   (%)    |   (# samples)   |
=====================================================================================================================================================
|  0 | SELECT STATEMENT     |                 |         |       |         1 |     +1 |     1 |        1 |      |       |          |                 |
|  1 |   HASH GROUP BY      |                 |       1 | 69634 |         1 |     +1 |     1 |        1 |      |       |   100.00 | Cpu (1)         |
|  2 |    TABLE ACCESS FULL | ANY_VALUE_TABLE |      1M | 69619 |         1 |     +1 |     1 |       1M | 1981 |   2GB |          |                 |
=====================================================================================================================================================

...中略...

-- 列サイズ短めで件数が多い（準備）
SCOTT@localhost:1521/freepdb1> @any_value2.sql
  1* DROP TABLE IF EXISTS any_value_table PURGE

表が削除されました。

経過: 00:00:00.13
  1  CREATE TABLE any_value_table
  2  (
  3    ordered_date DATE NOT NULL
  4    , order_id INTEGER NOT NULL
  5    , product_id INTEGER NOT NULL
  6    , product_name VARCHAR(2000) NOT NULL
  7    , qty INTEGER NOT NULL
  8    , CONSTRAINT pk_any_value_table PRIMARY KEY (order_id, product_id, ordered_date)
  9* )

表が作成されました。

経過: 00:00:00.01
  1  DECLARE
  2    o_date DATE := SYSDATE;
  3  BEGIN
  4    FOR i IN 1..20000000 LOOP
  5      INSERT INTO any_value_table
  6        (ordered_date
  7         , order_id
  8         , product_id
  9         , product_name
 10         , qty
 11        ) VALUES (o_date, i, 1, 'ITEM_1'||LPAD('*',30,'*'), 1);
 12      IF mod(i,1000) = 0 THEN commit; END IF;
 13    END LOOP;
 14* END;

PL/SQLプロシージャが正常に完了しました。

経過: 00:12:27.16

PL/SQLプロシージャが正常に完了しました。

経過: 00:00:17.83

SEGMENT_NAME                           GB
------------------------------ ----------
ANY_VALUE_TABLE                    1.3125

経過: 00:00:00.06

...中略...

-- ２回目
SCOTT@localhost:1521/freepdb1> @any_value_test2
  1  SELECT /*+ monitor */
  2    product_id
  3    ,product_name
  4    ,SUM(qty) AS total
  5  FROM
  6    any_value_table
  7  GROUP BY
  8    product_id
  9*   , product_name

PRODUCT_ID PRODUCT_NAME                              TOTAL
---------- ------------------------------------ ----------
         1 ITEM_1******************************   20000000

経過: 00:00:01.11

...中略...

SQL Text
------------------------------
SELECT /*+ monitor */ product_id ,product_name 
,SUM(qty) AS total FROM any_value_table 
GROUP BY product_id , product_name

...中略...

Global Stats
================================================================
| Elapsed |   Cpu   |    IO    | Fetch | Buffer | Read | Read  |
| Time(s) | Time(s) | Waits(s) | Calls |  Gets  | Reqs | Bytes |
================================================================
|    1.15 |    1.10 |     0.06 |     2 |   171K | 1363 |   1GB |
================================================================

SQL Plan Monitoring Details (Plan Hash Value=3772843140)
=====================================================================================================================================================
| Id |      Operation       |      Name       |  Rows   | Cost  |   Time    | Start  | Execs |   Rows   | Read | Read  | Activity | Activity Detail |
|    |                      |                 | (Estim) |       | Active(s) | Active |       | (Actual) | Reqs | Bytes |   (%)    |   (# samples)   |
=====================================================================================================================================================
|  0 | SELECT STATEMENT     |                 |         |       |         1 |     +1 |     1 |        1 |      |       |          |                 |
|  1 |   HASH GROUP BY      |                 |       1 | 46860 |         1 |     +1 |     1 |        1 |      |       |          |                 |
|  2 |    TABLE ACCESS FULL | ANY_VALUE_TABLE |     20M | 46510 |         1 |     +1 |     1 |      20M | 1363 |   1GB |   100.00 | Cpu (1)         |
=====================================================================================================================================================

...中略...

SQL Text
------------------------------
SELECT /*+ monitor */ product_id ,MAX(product_name) AS product_name 
,SUM(qty) AS total FROM any_value_table 
GROUP BY product_id

...中略...

Global Stats
================================================================
| Elapsed |   Cpu   |    IO    | Fetch | Buffer | Read | Read  |
| Time(s) | Time(s) | Waits(s) | Calls |  Gets  | Reqs | Bytes |
================================================================
|    1.29 |    1.23 |     0.06 |     2 |   171K | 1363 |   1GB |
================================================================

SQL Plan Monitoring Details (Plan Hash Value=3772843140)
=====================================================================================================================================================
| Id |      Operation       |      Name       |  Rows   | Cost  |   Time    | Start  | Execs |   Rows   | Read | Read  | Activity | Activity Detail |
|    |                      |                 | (Estim) |       | Active(s) | Active |       | (Actual) | Reqs | Bytes |   (%)    |   (# samples)   |
=====================================================================================================================================================
|  0 | SELECT STATEMENT     |                 |         |       |         1 |     +2 |     1 |        1 |      |       |          |                 |
|  1 |   HASH GROUP BY      |                 |       1 | 46860 |         1 |     +2 |     1 |        1 |      |       |          |                 |
|  2 |    TABLE ACCESS FULL | ANY_VALUE_TABLE |     20M | 46510 |         2 |     +1 |     1 |      20M | 1363 |   1GB |   100.00 | Cpu (1)         |
=====================================================================================================================================================

...中略...

SQL Text
------------------------------
SELECT /*+ monitor */ product_id ,ANY_VALUE(product_name) AS product_name 
,SUM(qty) AS total FROM any_value_table 
GROUP BY product_id

...中略...

Global Stats
================================================================
| Elapsed |   Cpu   |    IO    | Fetch | Buffer | Read | Read  |
| Time(s) | Time(s) | Waits(s) | Calls |  Gets  | Reqs | Bytes |
================================================================
|    1.15 |    1.09 |     0.06 |     2 |   171K | 1363 |   1GB |
================================================================

SQL Plan Monitoring Details (Plan Hash Value=3772843140)
==========================================================================================================================================================
| Id |      Operation       |      Name       |  Rows   | Cost  |   Time    | Start  | Execs |   Rows   | Read | Read  | Activity |   Activity Detail    |
|    |                      |                 | (Estim) |       | Active(s) | Active |       | (Actual) | Reqs | Bytes |   (%)    |     (# samples)      |
==========================================================================================================================================================
|  0 | SELECT STATEMENT     |                 |         |       |         1 |     +1 |     1 |        1 |      |       |          |                      |
|  1 |   HASH GROUP BY      |                 |       1 | 46860 |         1 |     +1 |     1 |        1 |      |       |          |                      |
|  2 |    TABLE ACCESS FULL | ANY_VALUE_TABLE |     20M | 46510 |         2 |     +0 |     1 |      20M | 1363 |   1GB |   100.00 | direct path read (1) |
==========================================================================================================================================================

 

PostgreSQL

-- 列サイズ長め（準備）
perftestdb=> \i ./any_value.sql
Timing is on.
DROP TABLE
Time: 7.248 ms
CREATE TABLE
Time: 3.827 ms
DO
Time: 24680.064 ms (00:24.680)
ANALYZE
Time: 104.675 ms
Timing is off.

...中略...

-- ２回目
PostgreSQLでも非集計列をGROUP BYに記述しないと、エラーになりますよね。
perftestdb=> \i ./any_value_test.sql
psql:any_value_test.sql:11: ERROR:  column "any_value_table.product_name" must appear in the GROUP BY clause 
or be used in an aggregate function
LINE 3:   ,product_name
           ^


注）出力内容は見やすく加工しちゃってます
 product_id |                                             product_name                                                    |  total   
------------+-------------------------------------------------------------------------------------------------------------+--------
          1 | ITEM_1***************************************************************************************************** | 1000000
              ***********************************************************************************************************
              ***********************************************************************************************************

...中略...

              ***********************************************************************************************************
              ***********************************************************************************************************
              *
(1 row)


GROUP BYで対処したケースで、work_memセットし忘れてデフォのままだったので Temp落ちして一人負けしてました。すみません。
                                                                   QUERY PLAN                                                                    
-------------------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=2471963.84..2491963.84 rows=1000000 width=1622) (actual time=3044.982..3044.983 rows=1 loops=1)
   Output: product_id, product_name, sum(qty)
   Group Key: any_value_table.product_id, any_value_table.product_name
   Buffers: shared hit=250000, temp read=598111 written=598519
   ->  Sort  (cost=2471963.84..2474463.84 rows=1000000 width=1618) (actual time=2479.915..2867.067 rows=1000000 loops=1)
         Output: product_id, product_name, qty
         Sort Key: any_value_table.product_id, any_value_table.product_name
         Sort Method: external merge  Disk: 1595032kB
         Buffers: shared hit=250000, temp read=598111 written=598519
         ->  Seq Scan on scott.any_value_table  (cost=0.00..260000.00 rows=1000000 width=1618) (actual time=0.012..216.822 rows=1000000 loops=1)
               Output: product_id, product_name, qty
               Buffers: shared hit=250000
 Planning:
   Buffers: shared hit=2
   Memory: used=14kB  allocated=16kB
 Planning Time: 0.409 ms
 Execution Time: 3570.032 ms
(17 rows)

...中略...

                                                                QUERY PLAN                                                                 
-------------------------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=267500.00..267500.01 rows=1 width=44) (actual time=434.445..434.445 rows=1 loops=1)
   Output: product_id, max((product_name)::text), sum(qty)
   Group Key: any_value_table.product_id
   Batches: 1  Memory Usage: 24kB
   Buffers: shared hit=250000
   ->  Seq Scan on scott.any_value_table  (cost=0.00..260000.00 rows=1000000 width=1618) (actual time=0.003..122.488 rows=1000000 loops=1)
         Output: ordered_date, order_id, product_id, product_name, qty
         Buffers: shared hit=250000
 Planning:
   Memory: used=14kB  allocated=16kB
 Planning Time: 0.324 ms
 Execution Time: 434.463 ms
(12 rows)

 ...中略...
 
                                                                QUERY PLAN                                                                 
-------------------------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=267500.00..267500.01 rows=1 width=44) (actual time=444.114..444.114 rows=1 loops=1)
   Output: product_id, any_value(product_name), sum(qty)
   Group Key: any_value_table.product_id
   Batches: 1  Memory Usage: 24kB
   Buffers: shared hit=250000
   ->  Seq Scan on scott.any_value_table  (cost=0.00..260000.00 rows=1000000 width=1618) (actual time=0.003..138.239 rows=1000000 loops=1)
         Output: ordered_date, order_id, product_id, product_name, qty
         Buffers: shared hit=250000
 Planning:
   Memory: used=14kB  allocated=16kB
 Planning Time: 0.043 ms
 Execution Time: 444.132 ms
(12 rows)

...中略...

-- 列サイズ短めで件数が多い（準備）
perftestdb=> \i ./any_value2.sql
Timing is on.
DROP TABLE
Time: 126.466 ms
CREATE TABLE
Time: 6.143 ms
DO
Time: 80158.605 ms (01:20.159)
ANALYZE
Time: 136.012 ms
Timing is off.
perftestdb=> 

...中略...

-- ２回目
perftestdb=> \i ./any_value_test2.sql
SET
 product_id |             product_name             |  total   
------------+--------------------------------------+----------
          1 | ITEM_1****************************** | 20000000
(1 row)

                                                                 QUERY PLAN                                                                 
--------------------------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=556186.00..556186.01 rows=1 width=49) (actual time=4635.607..4635.608 rows=1 loops=1)
   Output: product_id, product_name, sum(qty)
   Group Key: any_value_table.product_id, any_value_table.product_name
   Batches: 1  Memory Usage: 24kB
   Buffers: shared hit=206186
   ->  Seq Scan on scott.any_value_table  (cost=0.00..406186.00 rows=20000000 width=45) (actual time=0.004..1041.109 rows=20000000 loops=1)
         Output: ordered_date, order_id, product_id, product_name, qty
         Buffers: shared hit=206186
 Planning:
   Buffers: shared hit=3
   Memory: used=14kB  allocated=16kB
 Planning Time: 0.063 ms
 Execution Time: 4635.630 ms
(13 rows)

...中略...

                                                                 QUERY PLAN                                                                 
--------------------------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=556186.00..556186.01 rows=1 width=44) (actual time=4449.572..4449.572 rows=1 loops=1)
   Output: product_id, max((product_name)::text), sum(qty)
   Group Key: any_value_table.product_id
   Batches: 1  Memory Usage: 24kB
   Buffers: shared hit=206186
   ->  Seq Scan on scott.any_value_table  (cost=0.00..406186.00 rows=20000000 width=45) (actual time=0.004..1007.065 rows=20000000 loops=1)
         Output: ordered_date, order_id, product_id, product_name, qty
         Buffers: shared hit=206186
 Planning:
   Memory: used=14kB  allocated=16kB
 Planning Time: 0.049 ms
 Execution Time: 4449.591 ms
(12 rows)

...中略...

                                                                 QUERY PLAN                                                                 
--------------------------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=556186.00..556186.01 rows=1 width=44) (actual time=4145.688..4145.688 rows=1 loops=1)
   Output: product_id, any_value(product_name), sum(qty)
   Group Key: any_value_table.product_id
   Batches: 1  Memory Usage: 24kB
   Buffers: shared hit=206186
   ->  Seq Scan on scott.any_value_table  (cost=0.00..406186.00 rows=20000000 width=45) (actual time=0.005..1006.129 rows=20000000 loops=1)
         Output: ordered_date, order_id, product_id, product_name, qty
         Buffers: shared hit=206186
 Planning:
   Memory: used=14kB  allocated=16kB
 Planning Time: 0.050 ms
 Execution Time: 4145.707 ms
(12 rows)

 

MySQL

-- 列サイズ長め（準備）
mysql> \. /home/master/any_value.sql
Query OK, 0 rows affected (0.03 sec)

Empty set (0.00 sec)

Query OK, 0 rows affected (0.05 sec)

Empty set (0.00 sec)

Query OK, 0 rows affected (0.05 sec)

Empty set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

+--------------+
| @@AUTOCOMMIT |
+--------------+
|            0 |
+--------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (2 min 38.38 sec)

Empty set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

+--------------+
| @@AUTOCOMMIT |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

+----------------------------+---------+----------+----------+
| Table                      | Op      | Msg_type | Msg_text |
+----------------------------+---------+----------+----------+
| perftestdb.any_value_table | analyze | status   | OK       |
+----------------------------+---------+----------+----------+
1 row in set (0.01 sec)

...中略...

-- ２回目
MySQLでも今のリリースでは、非集計列をGROUP BY 句に記述しないとエラーになりますよね。
mysql> \. /home/master/any_value_test.sql
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column
 'perftestdb.any_value_table.product_name' which is not functionally dependent on columns in GROUP BY clause;
 this is incompatible with sql_mode=only_full_group_by


注）出力内容は見やすく加工しちゃってます
+------------+-------------------------------------------------------------------------------------------------------------+----------+
| product_id | product_name                                                                                                | total    |
+------------+-------------------------------------------------------------------------------------------------------------+----------+
|          1 | ITEM_1***************************************************************************************************** | 20000000 |
|            | *********************************************************************************************************** |          |
|            | *********************************************************************************************************** |          |

...中略...

|            | *********************************************************************************************************** |          |
|            | *********************************************************************************************************** |          |
|            | *                                                                                                           |          |
+------------+-------------------------------------------------------------------------------------------------------------+----------+
1 row in set (13.03 sec)

+-------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                     |
+-------------------------------------------------------------------------------------------------------------+
| -> Table scan on   (actual time=12979..12979 rows=1 loops=1)
    -> Aggregate using temporary table  (actual time=12979..12979 rows=1 loops=1)
        -> Table scan on any_value_table  (cost=116731 rows=888992) (actual time=0.0124..290 rows=1e+6 loops=1)
 |
+-------------------------------------------------------------------------------------------------------------+
1 row in set (12.98 sec)

...中略...

+-------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                     |
+-------------------------------------------------------------------------------------------------------------+
| -> Table scan on   (actual time=8620..8620 rows=1 loops=1)
    -> Aggregate using temporary table  (actual time=8620..8620 rows=1 loops=1)
        -> Table scan on any_value_table  (cost=116731 rows=888992) (actual time=0.0136..288 rows=1e+6 loops=1)
 |
+-------------------------------------------------------------------------------------------------------------+
1 row in set (8.62 sec)

...中略...

+-------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                     |
+-------------------------------------------------------------------------------------------------------------+
| -> Table scan on   (actual time=492..492 rows=1 loops=1)
    -> Aggregate using temporary table  (actual time=492..492 rows=1 loops=1)
        -> Table scan on any_value_table  (cost=116731 rows=888992) (actual time=0.0163..258 rows=1e+6 loops=1)
 |
+-------------------------------------------------------------------------------------------------------------+
1 row in set (0.49 sec)

...中略...


-- 列サイズ短めで件数が多い（準備）
mysql> \. /home/master/any_value2.sql
Query OK, 0 rows affected (0.03 sec)

Empty set (0.00 sec)

Query OK, 0 rows affected (0.04 sec)

Empty set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Empty set (0.00 sec)

Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected (0.00 sec)

+--------------+
| @@AUTOCOMMIT |
+--------------+
|            0 |
+--------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (9 min 11.20 sec)

Empty set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

+--------------+
| @@AUTOCOMMIT |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

+----------------------------+---------+----------+----------+
| Table                      | Op      | Msg_type | Msg_text |
+----------------------------+---------+----------+----------+
| perftestdb.any_value_table | analyze | status   | OK       |
+----------------------------+---------+----------+----------+
1 row in set (0.03 sec)

...中略...

-- ２回目
mysql> \. /home/master/any_value_test2.sql
+------------+--------------------------------------+----------+
| product_id | product_name                         | total    |
+------------+--------------------------------------+----------+
|          1 | ITEM_1****************************** | 20000000 |
+------------+--------------------------------------+----------+
1 row in set (21.90 sec)

+-------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                     |
+-------------------------------------------------------------------------------------------------------------+
| -> Table scan on   (actual time=22964..22964 rows=1 loops=1)
    -> Aggregate using temporary table  (actual time=22964..22964 rows=1 loops=1)
        -> Table scan on any_value_table  (cost=2.01e+6 rows=19.9e+6) (actual time=0.0134..4447 rows=20e+6 loops=1)
 |
+-------------------------------------------------------------------------------------------------------------+
1 row in set (22.97 sec)

...中略...

+-------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                     |
+-------------------------------------------------------------------------------------------------------------+
| -> Table scan on   (actual time=17478..17478 rows=1 loops=1)
    -> Aggregate using temporary table  (actual time=17478..17478 rows=1 loops=1)
        -> Table scan on any_value_table  (cost=2.01e+6 rows=19.9e+6) (actual time=0.0115..4399 rows=20e+6 loops=1)
 |
+-------------------------------------------------------------------------------------------------------------+
1 row in set (17.48 sec)

...中略...

+-------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                     |
+-------------------------------------------------------------------------------------------------------------+
| -> Table scan on   (actual time=9273..9273 rows=1 loops=1)
    -> Aggregate using temporary table  (actual time=9273..9273 rows=1 loops=1)
        -> Table scan on any_value_table  (cost=2.01e+6 rows=19.9e+6) (actual time=0.0116..4208 rows=20e+6 loops=1)
 |
+-------------------------------------------------------------------------------------------------------------+
1 row in set (9.28 sec)

 

-------------------- Scripts ----------------------

Oracle Database

列長の長いテストケース準備

any_value.sql

DROP TABLE IF EXISTS any_value_table PURGE
.
l
/

CREATE TABLE any_value_table
(
  ordered_date DATE NOT NULL 
  , order_id INTEGER NOT NULL
  , product_id INTEGER NOT NULL
  , product_name VARCHAR(2000) NOT NULL
  , qty INTEGER NOT NULL
  , CONSTRAINT pk_any_value_table PRIMARY KEY (order_id, product_id, ordered_date)
)
.
l
/

DECLARE
  o_date DATE := SYSDATE;
BEGIN
  FOR i IN 1..1000000 LOOP
    INSERT INTO any_value_table 
      (ordered_date
       , order_id
       , product_id
       , product_name
       , qty
      ) VALUES (o_date, i, 1, 'ITEM_1'||lpad('*',1600,'*'), 1);
    IF mod(i,100) = 0 THEN commit; END IF;
  END LOOP;
END;
.
l
/

EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=>'SCOTT',tabname=>'any_value_table',cascade=>true,no_invalidate=>false);
select segment_name,bytes/1024/1024/1024 "GB" from user_segments where segment_name = upper('any_value_table');

 

列長の長いケースのテスト（エラーになるSQL含む）

any_value_test.sql

SET LINESIZE 300
SET PAGESIZE 1000
SET LONGCHUNK 1000
SET LONG 100000

-- error --
SELECT
  product_id
  ,product_name
  ,SUM(qty) AS total
FROM
  any_value_table
GROUP BY
  product_id
.
l
/

SELECT /*+ monitor */
  product_id
  ,product_name
  ,SUM(qty) AS total
FROM
  any_value_table
GROUP BY
  product_id
  , product_name
.
l
/
select DBMS_SQLTUNE.REPORT_SQL_MONITOR(SQL_ID=>NULL,TYPE=>'TEXT');

SELECT /*+ monitor */
  product_id
  ,MAX(product_name) AS product_name
  ,SUM(qty) AS total
FROM
  any_value_table
GROUP BY
  product_id
.
l
/
select DBMS_SQLTUNE.REPORT_SQL_MONITOR(SQL_ID=>NULL,TYPE=>'TEXT');


SELECT /*+ monitor */
  product_id
  ,ANY_VALUE(product_name) AS product_name
  ,SUM(qty) AS total
FROM
  any_value_table
GROUP BY
  product_id
.
l
/
select DBMS_SQLTUNE.REPORT_SQL_MONITOR(SQL_ID=>NULL,TYPE=>'TEXT');

 

 

列サイズ短めで件数が多いテストケースの準備

any_value2.sql 


DROP TABLE IF EXISTS any_value_table PURGE
.
l
/

CREATE TABLE any_value_table
(
  ordered_date DATE NOT NULL 
  , order_id INTEGER NOT NULL
  , product_id INTEGER NOT NULL
  , product_name VARCHAR(2000) NOT NULL
  , qty INTEGER NOT NULL
  , CONSTRAINT pk_any_value_table PRIMARY KEY (order_id, product_id, ordered_date)
)
.
l
/


DECLARE
  o_date DATE := SYSDATE;
BEGIN
  FOR i IN 1..20000000 LOOP
    INSERT INTO any_value_table 
      (ordered_date
       , order_id
       , product_id
       , product_name
       , qty
      ) VALUES (o_date, i, 1, 'ITEM_1'||LPAD('*',30,'*'), 1);
    IF mod(i,1000) = 0 THEN commit; END IF;v  END LOOP;
END;
.
l
/

col product_name for a30
EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=>'SCOTT',tabname=>'any_value_table',cascade=>true,no_invalidate=>false);
select segment_name,bytes/1024/1024/1024 "GB" from user_segments where segment_name = upper('any_value_table');

 

列サイズ短めで件数が多いテストケースの準備

any_value_test2.sql

SET LINESIZE 300
SET PAGESIZE 1000
SET LONGCHUNK 1000
SET LONG 100000


SELECT /*+ monitor */
  product_id
  ,product_name
  ,SUM(qty) AS total
FROM
  any_value_table
GROUP BY
  product_id
  , product_name
.
l
/

select DBMS_SQLTUNE.REPORT_SQL_MONITOR(SQL_ID=>NULL,TYPE=>'TEXT');

SELECT /*+ monitor */
  product_id
  ,MAX(product_name) AS product_name
  ,SUM(qty) AS total
FROM
  any_value_table
GROUP BY
  product_id
.
l
/

select DBMS_SQLTUNE.REPORT_SQL_MONITOR(SQL_ID=>NULL,TYPE=>'TEXT');

SELECT /*+ monitor */
  product_id
  ,ANY_VALUE(product_name) AS product_name
  ,SUM(qty) AS total
FROM
  any_value_table
GROUP BY
  product_id
.
l
/
select DBMS_SQLTUNE.REPORT_SQL_MONITOR(SQL_ID=>NULL,TYPE=>'TEXT');

 

PostgreSQL

 

列長の長いテストケース準備

any_value.sql

\timing
DROP TABLE IF EXISTS any_value_table;

CREATE TABLE any_value_table
(
  ordered_date DATE NOT NULL 
  , order_id INTEGER NOT NULL
  , product_id INTEGER NOT NULL
  , product_name VARCHAR(2000) NOT NULL
  , qty INTEGER NOT NULL
  , CONSTRAINT pk_any_value_table PRIMARY KEY (order_id, product_id, ordered_date)
);

DO $$
DECLARE
  o_date DATE := CURRENT_DATE;
BEGIN
    FOR i IN 1..1000000 LOOP
      INSERT INTO any_value_table 
        (ordered_date
         , order_id
         , product_id
         , product_name
         , qty
        ) VALUES (o_date, i, 1, 'ITEM_1'||lpad('*',1600,'*'), 1);
      IF mod(i,100) = 0 THEN 
        COMMIT;
      END IF;
    END LOOP;
END
$$;

analyze any_value_table;
\timing

 

列長の長いテストケース(エラーケース含む)
any_value_test.sql

-- error --
SELECT
  product_id
  ,product_name
  ,SUM(qty) AS total
FROM
  any_value_table
GROUP BY
  product_id
;

set max_parallel_workers_per_gather = 0;
SELECT 
  product_id
  ,product_name
  ,SUM(qty) AS total
FROM
  any_value_table
GROUP BY
  product_id
  , product_name
;

explain (analyze,buffers,memory,summary,verbose)
SELECT 
  product_id
  ,product_name
  ,SUM(qty) AS total
FROM
  any_value_table
GROUP BY
  product_id
  , product_name
;

SELECT 
  product_id
  ,MAX(product_name) AS product_name
  ,SUM(qty) AS total
FROM
  any_value_table
GROUP BY
  product_id
;

explain (analyze,buffers,memory,summary,verbose)
SELECT 
  product_id
  ,MAX(product_name) AS product_name
  ,SUM(qty) AS total
FROM
  any_value_table
GROUP BY
  product_id
;

SELECT 
  product_id
  ,ANY_VALUE(product_name) AS product_name
  ,SUM(qty) AS total
FROM
  any_value_table
GROUP BY
  product_id
;

explain (analyze,buffers,memory,summary,verbose)
SELECT 
  product_id
  ,ANY_VALUE(product_name) AS product_name
  ,SUM(qty) AS total
FROM
  any_value_table
GROUP BY
  product_id
;

 

列サイズ短めで件数が多いテストケースの準備

any_value2.sql

\timing
DROP TABLE IF EXISTS any_value_table;

CREATE TABLE any_value_table
(
  ordered_date DATE NOT NULL 
  , order_id INTEGER NOT NULL
  , product_id INTEGER NOT NULL
  , product_name VARCHAR(2000) NOT NULL
  , qty INTEGER NOT NULL
  , CONSTRAINT pk_any_value_table PRIMARY KEY (order_id, product_id, ordered_date)
);

DO $$
DECLARE
  o_date DATE := CURRENT_DATE;
BEGIN
  FOR i IN 1..20000000 LOOP
    INSERT INTO any_value_table
       (ordered_date
       , order_id
       , product_id
       , product_name
       , qty
      ) VALUES (o_date, i, 1, 'ITEM_1'||LPAD('*',30,'*'), 1);
    IF mod(i,1000) = 0 THEN commit; END IF;
  END LOOP;
END
$$
;

analyze any_value_table;
\timing

 

列サイズ短めで件数が多いテストケース

any_value_test2.sql

set max_parallel_workers_per_gather = 0;

SELECT  
  product_id
  ,product_name
  ,SUM(qty) AS total
FROM
  any_value_table
GROUP BY
  product_id
  , product_name;

explain (analyze,buffers,memory,summary,verbose)
SELECT 
  product_id
  ,product_name
  ,SUM(qty) AS total
FROM
  any_value_table
GROUP BY
  product_id
  , product_name;


SELECT
  product_id
  ,MAX(product_name) AS product_name
  ,SUM(qty) AS total
FROM
  any_value_table
GROUP BY
  product_id;


explain (analyze,buffers,memory,summary,verbose)
SELECT
  product_id
  ,MAX(product_name) AS product_name
  ,SUM(qty) AS total
FROM
  any_value_table
GROUP BY
  product_id;


SELECT
  product_id
  ,ANY_VALUE(product_name) AS product_name
  ,SUM(qty) AS total
FROM
  any_value_table
GROUP BY
  product_id;

explain (analyze,buffers,memory,summary,verbose)
SELECT
  product_id
  ,ANY_VALUE(product_name) AS product_name
  ,SUM(qty) AS total
FROM
  any_value_table
GROUP BY
  product_id;

 

 

MySQL

 

列長の長いテストケース準備

any_value.sql

DROP TABLE IF EXISTS any_value_table;
show warnings;

CREATE TABLE any_value_table
(
  ordered_date DATE NOT NULL 
  , order_id INTEGER NOT NULL
  , product_id INTEGER NOT NULL
  , product_name VARCHAR(2000) NOT NULL
  , qty INTEGER NOT NULL
  , CONSTRAINT pk_any_value_table PRIMARY KEY (order_id, product_id, ordered_date)
);
show warnings;

DROP PROCEDURE IF EXISTS make_any_table_data;
show warnings;

DELIMITER $$
CREATE PROCEDURE make_any_table_data()
BEGIN
  DECLARE o_date DATE DEFAULT CURRENT_DATE;
  DECLARE i INTEGER DEFAULT 1;
  DECLARE r_count INTEGER DEFAULT 1000000;
  loop1: LOOP
    INSERT INTO any_value_table
      (ordered_date
       , order_id
       , product_id
       , product_name
       , qty
      ) VALUES (o_date, i, 1, CONCAT('ITEM_1', lpad('*',1600,'*')), 1);
    IF mod(i,100) = 0 THEN commit; END IF;
    SET i = i + 1;
    IF i > r_count THEN LEAVE loop1; END IF;
  END LOOP loop1;
END
$$
DELIMITER ;

set AUTOCOMMIT=0;
select @@AUTOCOMMIT;

CALL make_any_table_data; 
show warnings;

set AUTOCOMMIT=1;
select @@AUTOCOMMIT;

analyze table any_value_table;

 

列長の長いテストケース(エラーケース含む)

any_value_test.sql

-- error --
SELECT
  product_id
  ,product_name
  ,SUM(qty) AS total
FROM
  any_value_table
GROUP BY
  product_id
;


SELECT 
  product_id
  ,product_name
  ,SUM(qty) AS total
FROM
  any_value_table
GROUP BY
  product_id
  , product_name
;

explain analyze format=tree
SELECT 
  product_id
  ,product_name
  ,SUM(qty) AS total
FROM
  any_value_table
GROUP BY
  product_id
  , product_name
;


SELECT 
  product_id
  ,MAX(product_name) AS product_name
  ,SUM(qty) AS total
FROM
  any_value_table
GROUP BY
  product_id
;

explain analyze format=tree
SELECT 
  product_id
  ,MAX(product_name) AS product_name
  ,SUM(qty) AS total
FROM
  any_value_table
GROUP BY
  product_id
;

SELECT 
  product_id
  ,ANY_VALUE(product_name) AS product_name
  ,SUM(qty) AS total
FROM
  any_value_table
GROUP BY
  product_id
;


explain analyze format=tree
SELECT 
  product_id
  ,ANY_VALUE(product_name) AS product_name
  ,SUM(qty) AS total
FROM
  any_value_table
GROUP BY
  product_id
;

 

列サイズ短めで件数が多いテストケース（準備）

any_value2.sql

DROP TABLE IF EXISTS any_value_table;
show warnings;

CREATE TABLE any_value_table
(
  ordered_date DATE NOT NULL 
  , order_id INTEGER NOT NULL
  , product_id INTEGER NOT NULL
  , product_name VARCHAR(2000) NOT NULL
  , qty INTEGER NOT NULL
  , CONSTRAINT pk_any_value_table PRIMARY KEY (order_id, product_id, ordered_date)
);
show warnings;

DROP PROCEDURE IF EXISTS make_any_table_data;
show warnings;

DELIMITER $$
CREATE PROCEDURE make_any_table_data()
BEGIN
  DECLARE o_date DATE DEFAULT CURRENT_DATE;
  DECLARE i INTEGER DEFAULT 1;
  DECLARE r_count INTEGER DEFAULT 20000000;
  loop1: LOOP
    INSERT INTO any_value_table
      (ordered_date
       , order_id
       , product_id
       , product_name
       , qty
      ) VALUES (o_date, i, 1, CONCAT('ITEM_1', LPAD('*',30,'*')), 1);
    IF mod(i,1000) = 0 THEN commit; END IF;
    SET i = i + 1;
    IF i > r_count THEN LEAVE loop1; END IF;
  END LOOP loop1;
END
$$
DELIMITER ; 

set AUTOCOMMIT=0;
select @@AUTOCOMMIT;

CALL make_any_table_data;
show warnings;

set AUTOCOMMIT=1;
select @@AUTOCOMMIT;

analyze table any_value_table;

 

列サイズ短めで件数が多いテストケース

any_value_test2.sql

SELECT  
  product_id
  ,product_name
  ,SUM(qty) AS total
FROM
  any_value_table
GROUP BY
  product_id
  , product_name;

explain analyze format=tree
SELECT 
  product_id
  ,product_name
  ,SUM(qty) AS total
FROM
  any_value_table
GROUP BY
  product_id
  , product_name;

SELECT
  product_id
  ,MAX(product_name) AS product_name
  ,SUM(qty) AS total
FROM
  any_value_table
GROUP BY
  product_id;


explain analyze format=tree
SELECT
  product_id
  ,MAX(product_name) AS product_name
  ,SUM(qty) AS total
FROM
  any_value_table
GROUP BY
  product_id;


SELECT
  product_id
  ,ANY_VALUE(product_name) AS product_name
  ,SUM(qty) AS total
FROM
  any_value_table
GROUP BY
  product_id;

explain analyze format=tree
SELECT
  product_id
  ,ANY_VALUE(product_name) AS product_name
  ,SUM(qty) AS total
FROM
  any_value_table
GROUP BY
  product_id;

 

投稿日時 2025年8月22日 (金) 19時00分 Oracle, Oracle以外のデータベース, チューニング, SQL, MySQL, PostgreSQL, パフォーマンス, Oracle Database 23ai | | | コメント (0)

2025年7月 4日 (金)

実行計画は, SQL文のレントゲン写真だ！ No.67 / AI Vector Search - VECTOR INDEX HNSW SCAN のバリエーション

Previously on Mac De Oracle
前回は、VECTOR INDEX はどこ？、見積もりサイズだとそれなりのサイズだったのに... の謎を探るべく、我々は洞窟の奥へ向かった！(完結編)でした。
今日は、再びレントゲン写真に戻り、VECTOR INDEX HNSW SCAN のバリエーションをいくつか確認しておこうと思います。
（なかなか興味深いので、一度診ておけば、いざというときに慌てなくて済むと思います）

 

いきなりってのもあれなので、先に以下のマニュアルを一読しておくと良いと思います。マニュアルでもポイントが解説されているネタなので:)　解説しないとちょっと分かりずらい点が多いからだと思いますがw

Oracle Database 23ai / Oracle AI Vector Search ユーザーズ・ガイド / HNSW ベクトルインデックスのオプティマイザプランのバリエーションを解説している章があります。なかなか興味深い。
おそらく、表には積極的に登場してこない補助表が、突然実行計画に現れることへの戸惑いと実行計画の読み方にちょっとした癖がある点の緩和と実行計画のバリエーションごとのメリデメを理解してもらうためにも解説が必要だったのだろうなぁ。と想像。
Oracle Database / Release 23 / Oracle AI Vector Search User's Guide / Optimizer Plans for HNSW Vector Indexes

INMEMORYな索引なのに、OperationにINMEMORYというキーワードが無くて、おや？　と違和感があったり、ちょっとめんどくさい癖があるなぁと。。。w

まずは、
vector_index_neighbor_graph_reloadパラメータはCDBレベルで restart に設定した ( Oracle Database 23ai 23.6以降はデフォルトが restart になっています ) ので再起動しても vector index (HNSW) はポピュレートされメモリー上に復活しているはず。。。という確認から。

[oracle@localhost ~]$ sudo service oracle stop
[sudo] oracle のパスワード:
Stopping oracle (via systemctl):  
                                                           [  OK  ]
[oracle@localhost ~]$ 
[oracle@localhost ~]$ sudo service oracle start
Starting oracle (via systemctl):  
                                                           [  OK  ]
[oracle@localhost ~]$ 
[oracle@localhost ~]$ sqlplus scott@localhost:1521/freepdb1

...略...

Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.4.0.24.05
に接続されました。
SCOTT@localhost:1521/freepdb1> @show_vector_mem_pool    

POOL                       ALLOC_BYTES USED_BYTES POPULATE_STATUS
-------------------------- ----------- ---------- --------------------------
1MB POOL                     369098752  236978176 DONE
64KB POOL                    150994944    2686976 DONE
IM POOL METADATA              16777216   16777216 DONE

経過: 00:00:00.05
SCOTT@localhost:1521/freepdb1> @show_vector_segments

       OBJ   MEMBYTES
---------- ----------
         0     131072
     80126  239534080

経過: 00:00:00.02

 

それぞれの実行計画のpros/consはマニュアルで読んでもらうとして、
VECTOR INDEX HNSW SCAN PRE-FILTER WITH JOIN BACKから診てみましょう。

 

JOIN BACKするかしないかの違いは最後にもう一度ベース表をアクセスするかどうか。（次の実行計画で言うと、Id=5でSEARCH_DATAをtable access by index rowidでアクセスしている箇所がJOIN BACK)
これらのバリエーションはデータ量とフィルタリング量との兼ね合いになるのでベクトル索引で近傍検索の実行計画をヒントで固定化するのは比較的難易度が高そう（どちらの傾向に固定した方が良いかの判断は難しい）だろうな、と思っているところ。固定できるか、したほうが良いかの見極めというか、割り切りなのかもしれないが決め打ちするだけの情報は揃えた上で決める必要はりそう。最初は経過観察なのが良いだろうと思っているところだが。。。とはいえ、覚えていて損ないかなぁ。

また、これらのバリエーションでは、VECTOR INDEX (HNSW)以外に、補助表の主役であるMAP表が登場します（Id=9の部分)。
VECTOR INDEX (HNSW)本体だけでなく補助表の存在も把握しておくことが大切な理由はここにもあります。（ベース表に比べるとサイズは小さいわけですが）

後半で別途まとめますが、この実行では内部ビューが新たに作られています。
Id=7の VW_HPJ_91CF1FF7 がそれです。内部的に作成されるビューにはそれぞれのトランスフォームに関連する短縮名が付与されるのが、これまでのOracle Databaseのオプティマイザのお約束ですね。
VW_HPJ_、 Hnsw scan Pre-filter with Join back -> HPJ になりそうですよね。 VW_HPJ_という内部ビューをみたらVECTOR_INDEX_TRANSFORM VECTOR INDEX HNSW SCAN PRE_FILTER WITH JOIN BACKが行われていると考えてよいでしょうね。

SELECT
  /*+
    GATHER_PLAN_STATISTICS
  */
  id
  , description
  , community
  , location_desc
  , district
  , TO_NUMBER( v_distance ) AS v_distance
FROM
(
  SELECT
    /*+ 
      VECTOR_INDEX_TRANSFORM(search_data search_data_hnsw_ix pre_filter_with_join_back)
    */
    id
    , description
    , community
    , location_desc
    , district
    , VECTOR_DISTANCE
      (
        vector_desc
        , VECTOR_EMBEDDING
         (
           all_minilm_l6 USING 'Incident in which someone may have been murdered' AS data
         )
      , COSINE
    ) v_distance
  FROM
    search_data
  WHERE
    community = 'AUSTIN'
  ORDER BY
    v_distance
  FETCH APPROX FIRST 20 ROWS ONLY
)
/


Plan hash value: 3994424349

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name                                                        | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                                                             |      1 |        |       |       |  6649 (100)|          |     20 |00:00:00.93 |     412 |    410 |       |       |          |
|   1 |  VIEW                                  |                                                             |      1 |      1 |   157 |       |  6649   (1)| 00:00:01 |     20 |00:00:00.93 |     412 |    410 |       |       |          |
|*  2 |   COUNT STOPKEY                        |                                                             |      1 |        |       |       |            |          |     20 |00:00:00.93 |     412 |    410 |       |       |          |
|   3 |    VIEW                                |                                                             |      1 |      1 |   157 |       |  6649   (1)| 00:00:01 |     20 |00:00:00.93 |     412 |    410 |       |       |          |
|*  4 |     SORT ORDER BY STOPKEY              |                                                             |      1 |      1 |  1665 |       |  6649   (1)| 00:00:01 |     20 |00:00:00.93 |     412 |    410 |  4096 |  4096 | 4096  (0)|
|*  5 |      TABLE ACCESS BY INDEX ROWID       | SEARCH_DATA                                                 |      1 |      1 |  1665 |       |  6648   (1)| 00:00:01 |     20 |00:00:00.94 |     412 |    410 |       |       |          |
|   6 |       VECTOR INDEX HNSW SCAN PRE-FILTER| SEARCH_DATA_HNSW_IX                                         |      1 |      1 |  1665 |       |  6648   (1)| 00:00:01 |     20 |00:00:00.93 |     392 |    390 |       |       |          |
|   7 |        VIEW                            | VW_HPJ_91CF1FF7                                             |      1 |   6235 |   152K|       |  6647   (1)| 00:00:01 |   6235 |00:00:00.06 |     392 |    390 |       |       |          |
|*  8 |         HASH JOIN RIGHT OUTER          |                                                             |      1 |   6235 |     9M|  3296K|  6647   (1)| 00:00:01 |   6235 |00:00:00.06 |     392 |    390 |  8506K|  2096K| 9004K (0)|
|   9 |          TABLE ACCESS FULL             | VECTOR$SEARCH_DATA_HNSW_IX$78074_80224_0$HNSW_ROWID_VID_MAP |      1 |    125K|  1831K|       |   102   (0)| 00:00:01 |    125K|00:00:00.01 |     373 |    371 |       |       |          |
|* 10 |          INDEX RANGE SCAN              | SEARCH_DATA_COMMNITY_IX                                     |      1 |   6235 |       |       |    23   (0)| 00:00:01 |   6235 |00:00:00.01 |      19 |     19 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$3        / "from$_subquery$_001"@"SEL$1"
   2 - SEL$3
   3 - SEL$7185E227 / "from$_subquery$_003"@"SEL$3"
   4 - SEL$7185E227
   5 - SEL$7185E227 / "SEARCH_DATA"@"SEL$2"
   6 - SEL$7185E227 / "SEARCH_DATA"@"SEL$2"
   7 - SEL$BF33016E / "VW_HPJ_91CF1FF7"@"SEL$2"
   8 - SEL$BF33016E
   9 - SEL$BF33016E / "VTIX_RIDVID"@"SEL$2"
  10 - SEL$BF33016E / "SEARCH_DATA"@"SEL$2"

...略...

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(ROWNUM<=20)
   4 - filter(ROWNUM<=20)
   5 - filter("SEARCH_DATA"."COMMUNITY"='AUSTIN')
   8 - access("SEARCH_DATA".ROWID="VTIX_RIDVID"."BASE_TABLE_ROWID")
  10 - access("SEARCH_DATA"."COMMUNITY"='AUSTIN')

...略...

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
---------------------------------------------------------------------------

  10 -  SEL$BF33016E / "SEARCH_DATA"@"SEL$2"
           -  VECTOR_INDEX_TRANSFORM(search_data search_data_hnsw_ix pre_filter_with_join_back)

 

次に、VECTOR INDEX HNSW SCAN PRE-FILTER WITHOUT JOIN BACKを診てみましょう。
違いは、JOIN BACKがないところですよね、ヒントの通りw。
実行計画のId=5でVECTOR INDEX HNSW SCAN PRE_FILTERが行われていますが、その後でベース表を再度アクセスすることはありません。
なお、今回の検索パターンだと join back したほうが多少軽めですよね。んーーー難しい。普段はオプティマイザにお任せのほうが良いかもなと思うわけです。よほどのことがない限り。はい。

ここでも、新たな内部ビュー、 VW_HPF_475999B9 が作成されています。 VW_HPF_、 Hnsw scan Pre-Filter without join back -> HPF でしょうか。ちょっとムズイw VW_HPF_内部ビューを見つけたら、VECTOR INDEX HNSW SCAN PRE-FILTER WITHOUT JOIN BACKが行われてると理解して良さそうです。

このケースでも補助表であるMAP表が登場します。なにこれ？　俺は作ってないぞ！　と驚かないようにしてくださいねw。（当ブログを読んだ方は驚くことはないはずですがw）
また、マニュアルにも記載されていますが、Hash join だけでなく Nested Loops Joinになることもあります。データ量と索引有無次第ではありますが、覚えておくと良いでしょう。（この例では Apaptive Planが選択されているため、どちらの結合方式になるかは、Join Cardinarity次第です）

SELECT
  /*+
    GATHER_PLAN_STATISTICS
  */
  id
  , description
  , community
  , location_desc
  , district
  , TO_NUMBER( v_distance ) AS v_distance
FROM
(
  SELECT
    /*+ 
      VECTOR_INDEX_TRANSFORM(search_data search_data_hnsw_ix pre_filter_without_join_back) 
    */
    id
    , description
    , community
    , location_desc
    , district
    , VECTOR_DISTANCE
      (
        vector_desc
        , VECTOR_EMBEDDING
         (
           all_minilm_l6 USING 'Incident in which someone may have been murdered' AS data
         )
      , COSINE
    ) v_distance
  FROM
    search_data
  WHERE
    community = 'AUSTIN'
  ORDER BY
    v_distance
  FETCH APPROX FIRST 20 ROWS ONLY
)
/


--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   Id  | Operation                                    | Name                                                        | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|     0 | SELECT STATEMENT                             |                                                             |      1 |        |       |  5998 (100)|          |     20 |00:00:02.77 |    5929 |   5904 |       |       |          |
|     1 |  VIEW                                        |                                                             |      1 |     20 |  3140 |  5998   (1)| 00:00:01 |     20 |00:00:02.77 |    5929 |   5904 |       |       |          |
|  *  2 |   COUNT STOPKEY                              |                                                             |      1 |        |       |            |          |     20 |00:00:02.77 |    5929 |   5904 |       |       |          |
|     3 |    VIEW                                      |                                                             |      1 |     20 |  3140 |  5998   (1)| 00:00:01 |     20 |00:00:02.77 |    5929 |   5904 |       |       |          |
|  *  4 |     SORT ORDER BY STOPKEY                    |                                                             |      1 |     20 | 35220 |  5998   (1)| 00:00:01 |     20 |00:00:02.77 |    5929 |   5904 |  4096 |  4096 | 4096  (0)|
|     5 |      VECTOR INDEX HNSW SCAN PRE-FILTER       | SEARCH_DATA_HNSW_IX                                         |      1 |     20 | 35220 |  5997   (1)| 00:00:01 |     20 |00:00:02.77 |    5929 |   5904 |  1278K|  1076K| 1175K (0)|
|     6 |       VIEW                                   | VW_HPF_475999B9                                             |      1 |   6235 |  1071K|  5996   (1)| 00:00:01 |   6235 |00:00:02.13 |    5914 |   5904 |       |       |          |
|  *  7 |        HASH JOIN OUTER                       |                                                             |      1 |   6235 |   499K|  5996   (1)| 00:00:01 |   6235 |00:00:02.13 |    5914 |   5904 |  1448K|  1287K| 1856K (0)|
|-    8 |         NESTED LOOPS OUTER                   |                                                             |      1 |   6235 |   499K|  5996   (1)| 00:00:01 |   6235 |00:00:10.37 |    5540 |   5533 |       |       |          |
|-    9 |          STATISTICS COLLECTOR                |                                                             |      1 |        |       |            |          |   6235 |00:00:10.36 |    5540 |   5533 |       |       |          |
|    10 |           TABLE ACCESS BY INDEX ROWID BATCHED| SEARCH_DATA                                                 |      1 |   6235 |   407K|  5893   (1)| 00:00:01 |   6235 |00:00:10.36 |    5540 |   5533 |       |       |          |
|  * 11 |            INDEX RANGE SCAN                  | SEARCH_DATA_COMMNITY_IX                                     |      1 |   6235 |       |    23   (0)| 00:00:01 |   6235 |00:00:00.01 |      19 |     19 |       |       |          |
|-   12 |          TABLE ACCESS BY INDEX ROWID         | VECTOR$SEARCH_DATA_HNSW_IX$78074_80224_0$HNSW_ROWID_VID_MAP |      0 |      1 |    15 |   102   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|- * 13 |           INDEX UNIQUE SCAN                  | SYS_C0013920                                                |      0 |        |       |            |          |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|    14 |         TABLE ACCESS FULL                    | VECTOR$SEARCH_DATA_HNSW_IX$78074_80224_0$HNSW_ROWID_VID_MAP |      1 |    125K|  1831K|   102   (0)| 00:00:01 |    125K|00:00:00.03 |     374 |    371 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$3        / "from$_subquery$_001"@"SEL$1"
   2 - SEL$3
   3 - SEL$2D1A9934 / "from$_subquery$_003"@"SEL$3"
   4 - SEL$2D1A9934
   5 - SEL$2D1A9934 / "SEARCH_DATA"@"SEL$2"
   6 - SEL$6D23FDEA / "VW_HPF_475999B9"@"SEL$475999B9"
   7 - SEL$6D23FDEA
   8 - SEL$6D23FDEA
  10 - SEL$6D23FDEA / "SEARCH_DATA"@"SEL$2"
  11 - SEL$6D23FDEA / "SEARCH_DATA"@"SEL$2"
  12 - SEL$6D23FDEA / "VTIX_RIDVID"@"SEL$EBB9871C"
  13 - SEL$6D23FDEA / "VTIX_RIDVID"@"SEL$EBB9871C"
  14 - SEL$6D23FDEA / "VTIX_RIDVID"@"SEL$EBB9871C"

...略...

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(ROWNUM<=20)
   4 - filter(ROWNUM<=20)
   7 - access("SEARCH_DATA".ROWID="VTIX_RIDVID"."BASE_TABLE_ROWID")
  11 - access("SEARCH_DATA"."COMMUNITY"='AUSTIN')
  13 - access("SEARCH_DATA".ROWID="VTIX_RIDVID"."BASE_TABLE_ROWID")

...略...

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
---------------------------------------------------------------------------

  10 -  SEL$6D23FDEA / "SEARCH_DATA"@"SEL$2"
           -  VECTOR_INDEX_TRANSFORM(search_data search_data_hnsw_ix pre_filter_without_join_back)

Note
-----
   - this is an adaptive plan (rows marked '-' are inactive)

 

次は、VECTOR INDEX TRANSFORM IN-FILTER WITH JOIN BACK これまでの PRE-FILTERではなく、IN-FILTER + JOIN BACKをヒントで強制しています

なお、マニュアルにも記載がありますが、In-filterの実行計画の読み方は少々癖があります！

Id=8の元表のアクセスを見てください！。いきなり、TABLE ACCESS BY USER ROWID で SEARCH_DATA 表をアクセスしています！！！
この実行計画の開始ポイントは、Id=8ではなく、Id=6の VECTOR INDEX HNSW SCAN IN-FILTER で、VECTOR INDEX (HNSW)である SEARCH_DATA_HNSW_IX 索引をトラバースしている部分です:)
Id=6で識別されたベクトルごとに、Id=8の元表に対応するrowidのフィルタが適用され、関連する列が抽出されます!!!!!!!

VECTOR INDEX TRANSFORMの時の実行計画の読み方は正しく覚えないと軽くハマりそうですねw 少々癖があるので覚えるしかないですよ！w（ここも試験にでるよ！！w しらんけど）

最後に、Id=5でJOIN BACKして、COUNT STOPKEYの操作へ遷移していきます！！！！

また、ここでも内部生成の新たなビューが登場しています。
VW_HIJ_ ですね。 HIJ -> Hnsw scan In-filter with Join back ということでしょうね。想像するに。　

SELECT
  /*+
    GATHER_PLAN_STATISTICS
  */
  id
  , description
  , community
  , location_desc
  , district
  , TO_NUMBER( v_distance ) AS v_distance
FROM
(
  SELECT
    /*+ 
      VECTOR_INDEX_TRANSFORM(search_data search_data_hnsw_ix in_filter_with_join_back) 
    */
    id
    , description
    , community
    , location_desc
    , district
    , VECTOR_DISTANCE
      (
        vector_desc
        , VECTOR_EMBEDDING
         (
           all_minilm_l6 USING 'Incident in which someone may have been murdered' AS data
         )
      , COSINE
    ) v_distance
  FROM
    search_data
  WHERE
    community = 'AUSTIN'
  ORDER BY
    v_distance
  FETCH APPROX FIRST 20 ROWS ONLY
)
/


-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                     |      1 |        |       |     3 (100)|          |     20 |00:00:02.28 |    4725 |   4431 |       |       |          |
|   1 |  VIEW                                 |                     |      1 |      1 |   157 |     3  (34)| 00:00:01 |     20 |00:00:02.28 |    4725 |   4431 |       |       |          |
|*  2 |   COUNT STOPKEY                       |                     |      1 |        |       |            |          |     20 |00:00:02.28 |    4725 |   4431 |       |       |          |
|   3 |    VIEW                               |                     |      1 |      1 |   157 |     3  (34)| 00:00:01 |     20 |00:00:02.28 |    4725 |   4431 |       |       |          |
|*  4 |     SORT ORDER BY STOPKEY             |                     |      1 |      1 |  1652 |     3  (34)| 00:00:01 |     20 |00:00:02.28 |    4725 |   4431 |  4096 |  4096 | 4096  (0)|
|*  5 |      TABLE ACCESS BY INDEX ROWID      | SEARCH_DATA         |      1 |      1 |  1652 |     2   (0)| 00:00:01 |     20 |00:00:02.28 |    4725 |   4431 |       |       |          |
|   6 |       VECTOR INDEX HNSW SCAN IN-FILTER| SEARCH_DATA_HNSW_IX |      1 |      1 |  1652 |     2   (0)| 00:00:01 |     20 |00:00:02.28 |    4705 |   4423 |       |       |          |
|   7 |        VIEW                           | VW_HIJ_475999B9     |   4773 |      1 |       |     1   (0)| 00:00:01 |    265 |00:00:01.59 |    4705 |   4423 |       |       |          |
|*  8 |         TABLE ACCESS BY USER ROWID    | SEARCH_DATA         |   4773 |      1 |  1652 |     1   (0)| 00:00:01 |    265 |00:00:01.59 |    4705 |   4423 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$3        / "from$_subquery$_001"@"SEL$1"
   2 - SEL$3
   3 - SEL$79710E8E / "from$_subquery$_003"@"SEL$3"
   4 - SEL$79710E8E
   5 - SEL$79710E8E / "SEARCH_DATA"@"SEL$2"
   6 - SEL$79710E8E / "SEARCH_DATA"@"SEL$2"
   7 - SEL$860F096D / "VW_HIJ_475999B9"@"SEL$2"
   8 - SEL$860F096D / "SEARCH_DATA"@"SEL$2"

...略...

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(ROWNUM<=20)
   4 - filter(ROWNUM<=20)
   5 - filter("SEARCH_DATA"."COMMUNITY"='AUSTIN')
   8 - filter("SEARCH_DATA"."COMMUNITY"='AUSTIN')

...略...

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
---------------------------------------------------------------------------

   8 -  SEL$860F096D / "SEARCH_DATA"@"SEL$2"
           -  VECTOR_INDEX_TRANSFORM(search_data search_data_hnsw_ix in_filter_with_join_back)

 

最後に、VECTOR INDEX TRANSFORM IN-FILTER WITHOUT JOIN BACK In-filtewrでJoin Backなしというタイプです。

このタイプがもっとも無駄がなさそうですね。今回の例で使っている検索パターンだと。。。:)

これもマニュアルの記述されているとおり、実行計画の開始位置にクセがあります。
Id=5の VECTOR INDEX HNSW SCAN IN-FILTER で、VECTOR INDEX (HNSW) をトラバースするところがスタートです。
次に、Id=7のベース表をVECTOR INDEXから取得したrowidでアクセス。
その後、COUNT STOPKEYの操作へ入ります。

そして、ここでも新顔の内部ビュー、 VW_HIF_ -> Hnsw scan In-Filter with join back ということで、 HIFになっていると思われます:)

SELECT
  /*+
    GATHER_PLAN_STATISTICS
  */
  id
  , description
  , community
  , location_desc
  , district
  , TO_NUMBER( v_distance ) AS v_distance
FROM
(
  SELECT
    /*+ 
      VECTOR_INDEX_TRANSFORM(search_data search_data_hnsw_ix in_filter_without_join_back) 
    */
    id
    , description
    , community
    , location_desc
    , district
    , VECTOR_DISTANCE
      (
        vector_desc
        , VECTOR_EMBEDDING
         (
           all_minilm_l6 USING 'Incident in which someone may have been murdered' AS data
         )
      , COSINE
    ) v_distance
  FROM
    search_data
  WHERE
    community = 'AUSTIN'
  ORDER BY
    v_distance
  FETCH APPROX FIRST 20 ROWS ONLY
 )
/


------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                     |      1 |        |       |     3 (100)|          |     20 |00:00:00.52 |    4705 |    733 |       |       |          |
|   1 |  VIEW                                |                     |      1 |     20 |  3140 |     3  (34)| 00:00:01 |     20 |00:00:00.52 |    4705 |    733 |       |       |          |
|*  2 |   COUNT STOPKEY                      |                     |      1 |        |       |            |          |     20 |00:00:00.52 |    4705 |    733 |       |       |          |
|   3 |    VIEW                              |                     |      1 |     20 |  3140 |     3  (34)| 00:00:01 |     20 |00:00:00.52 |    4705 |    733 |       |       |          |
|*  4 |     SORT ORDER BY STOPKEY            |                     |      1 |     20 | 34960 |     3  (34)| 00:00:01 |     20 |00:00:00.52 |    4705 |    733 |  4096 |  4096 | 4096  (0)|
|   5 |      VECTOR INDEX HNSW SCAN IN-FILTER| SEARCH_DATA_HNSW_IX |      1 |     20 | 34960 |     2   (0)| 00:00:01 |     20 |00:00:00.52 |    4705 |    733 |   835K|   835K|  543K (0)|
|   6 |       VIEW                           | VW_HIF_475999B9     |   4773 |      1 |   151 |     1   (0)| 00:00:01 |    265 |00:00:00.02 |    4705 |    733 |       |       |          |
|*  7 |        TABLE ACCESS BY USER ROWID    | SEARCH_DATA         |   4773 |      1 |    67 |     1   (0)| 00:00:01 |    265 |00:00:00.02 |    4705 |    733 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$3        / "from$_subquery$_001"@"SEL$1"
   2 - SEL$3
   3 - SEL$81BAFB36 / "from$_subquery$_003"@"SEL$3"
   4 - SEL$81BAFB36
   5 - SEL$81BAFB36 / "SEARCH_DATA"@"SEL$2"
   6 - SEL$066A4CD4 / "VW_HIF_475999B9"@"SEL$475999B9"
   7 - SEL$066A4CD4 / "SEARCH_DATA"@"SEL$2"

...略...

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(ROWNUM<=20)
   4 - filter(ROWNUM<=20)
   7 - filter("SEARCH_DATA"."COMMUNITY"='AUSTIN')

...略...

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
---------------------------------------------------------------------------

   7 -  SEL$066A4CD4 / "SEARCH_DATA"@"SEL$2"
           -  VECTOR_INDEX_TRANSFORM(search_data search_data_hnsw_ix in_filter_without_join_back)

 

まとめ VECTOR INDEX TRANSFORM の特徴

・VECTOR INDEX (HNSW)以外に、補助表であるMAP表が使われる（場合によってはその索引も）
・In-filter時の実行計画開始の開始ポイントに癖があるので要注意
・内部で生成されるビューがバリエーション分増加した

 

最後に、今回新たに登場した内部ビュー名と、これまでに把握されている内部ビューのまとめ。

VECTOR INDEX TRANSFORM

VW_HPJ_ / Hnsw scan Pre-filter with Join back
VW_HPF_ / Hnsw scan Pre-Filter with join back
VW_HIJ_ / Hnsw scan In-filter with Join back
VW_HIF_ / Hnsw scan In-Filter with join back

 

上記に加え以前からいくつかメジャーな内部生成ビューがまとめられていいます。覚えておくとなにが行われているか分かり易いと思いますよ。
Internal Views / Oracle Scratchpad / Jonathan Lewis

 

では、また！

 

Enjoin Execution Plans, SQLs, and AI Vector Search!

 

 

Related article on Mac De Oracle
実行計画は, SQL文のレントゲン写真だ！

・実行計画は, SQL文のレントゲン写真だ！　Oracle Database編 (全部俺）Advent Calendar 2019 - Day 1 / TABLE FULL SCAN
・実行計画は, SQL文のレントゲン写真だ！　Oracle Database編 (全部俺）Advent Calendar 2019 - Day 2 / INDEX UNIQUE SCAN
・実行計画は, SQL文のレントゲン写真だ！　Oracle Database編 (全部俺）Advent Calendar 2019 - Day 3 / INDEX RANGE SCAN, Index Only Scan
・実行計画は, SQL文のレントゲン写真だ！　Oracle Database編 (全部俺）Advent Calendar 2019 - Day 4 / INDEX RANGE SCAN
・実行計画は, SQL文のレントゲン写真だ！　Oracle Database編 (全部俺）Advent Calendar 2019 - Day 5 / INDEX RANGE SCAN, INLIST ITERATOR
・実行計画は, SQL文のレントゲン写真だ！　Oracle Database編 (全部俺）Advent Calendar 2019 - Day 6 / INDEX FAST SCAN, Index Only Scan
・実行計画は, SQL文のレントゲン写真だ！　Oracle Database編 (全部俺）Advent Calendar 2019 - Day 7 / INDEX FULL SCAN,Index Only Scan
・実行計画は, SQL文のレントゲン写真だ！　Oracle Database編 (全部俺）Advent Calendar 2019 - Day 8 / INDEX SKIP SCAN
・実行計画は, SQL文のレントゲン写真だ！　Oracle Database編 (全部俺）Advent Calendar 2019 - Day 9 / TABLE ACCESS INMEMORY FULL
・実行計画は, SQL文のレントゲン写真だ！　Oracle Database編 (全部俺）Advent Calendar 2019 - Day 10 / NESTED LOOP JOIN
・実行計画は, SQL文のレントゲン写真だ！　Oracle Database編 (全部俺）Advent Calendar 2019 - Day 11 / MERGE JOIN
・実行計画は, SQL文のレントゲン写真だ！　Oracle Database編 (全部俺）Advent Calendar 2019 - Day 12 / HASH JOIN
・実行計画は, SQL文のレントゲン写真だ！　Oracle Database編 (全部俺）Advent Calendar 2019 - Day 13 / HASH JOIN OUTER
・実行計画は, SQL文のレントゲン写真だ！　Oracle Database編 (全部俺）Advent Calendar 2019 - Day 14 / HASH JOIN FULL OUTER
・実行計画は, SQL文のレントゲン写真だ！　Oracle Database編 (全部俺）Advent Calendar 2019 - Day 15 / PX, TABLE ACCESS FULL
・実行計画は, SQL文のレントゲン写真だ！　Oracle Database編 (全部俺）Advent Calendar 2019 - Day 16 / CONCATENATION
・実行計画は, SQL文のレントゲン写真だ！　Oracle Database編 (全部俺）Advent Calendar 2019 - Day 17 / SORT UNIQUE, UNION-ALL = UNION
・実行計画は, SQL文のレントゲン写真だ！　Oracle Database編 (全部俺）Advent Calendar 2019 - Day 18 / UNION-ALL
・実行計画は, SQL文のレントゲン写真だ！　Oracle Database編 (全部俺）Advent Calendar 2019 - Day 19 / INTERSECTION
・実行計画は, SQL文のレントゲン写真だ！　Oracle Database編 (全部俺）Advent Calendar 2019 - Day 20 / MINUS
・実行計画は, SQL文のレントゲン写真だ！　Oracle Database編 (全部俺）Advent Calendar 2019 - Day 21 / WINDOW NOSORT STOPKEY
・実行計画は, SQL文のレントゲン写真だ！　Oracle Database編 (全部俺）Advent Calendar 2019 - Day 22 / COUNT STOPKEY
・実行計画は, SQL文のレントゲン写真だ！　Oracle Database編 (全部俺）Advent Calendar 2019 - Day 23 / HASH JOIN - LEFT-DEEP JOIN vs RIGHT-DEEP JOIN
・実行計画は, SQL文のレントゲン写真だ！　Oracle Database編 (全部俺）Advent Calendar 2019 - Day 24 / CONNECT BY NO FILTERING WITH START-WITH
・実行計画は, SQL文のレントゲン写真だ！　Oracle Database編 (全部俺）Advent Calendar 2019 - Day 25 / UNION ALL (RECURSIVE WITH) DEPTH FIRST, RECURSIVE WITH PUMP
・実行計画は, SQL文のレントゲン写真だ！　Oracle Database編 (全部俺）Advent Calendar 2019 - おまけ#1 / STAR TRANSFORM, VECTOR TRANSFORM (DWH向け)
・実行計画は, SQL文のレントゲン写真だ！　Oracle Database編 (全部俺）Advent Calendar 2019 - おまけ#2 / MERGE (UPSERT)
・実行計画は, SQL文のレントゲン写真だ！　Oracle Database編 (全部俺）Advent Calendar 2019 - おまけ#3 / RDFView
・実行計画は, SQL文のレントゲン写真だ！　Oracle Database編 (全部俺）Advent Calendar 2019 - おまけ#4 / INDEX FULL SCAN (MIN/MAX) - Index Only Scan
・実行計画は, SQL文のレントゲン写真だ！ No.30 - LOAD TABLE CONVENTIONAL vs. LOAD AS SELECT
・実行計画は, SQL文のレントゲン写真だ！ No.31 - TEMP TABLE TRANSFORMATION LOAD AS SELECT (CURSOR DURATION MEMORY)
・実行計画は, SQL文のレントゲン写真だ！ No.32 - EXTERNAL TABLE ACCESS FULL / INMEMORY FULL
・実行計画は, SQL文のレントゲン写真だ！ No.33 - BITMAP CONVERSION TO ROWIDS
・実行計画は, SQL文のレントゲン写真だ！ No.34 - 似て非なるもの　USE_CONCAT　と　OR_EXPAND ヒント と　手書きSQLのレントゲンの見分け方
・実行計画は, SQL文のレントゲン写真だ！ No.35 - 似て非なるもの USE_CONCAT と OR_EXPANDヒントとパラレルクエリー
・実行計画は, SQL文のレントゲン写真だ！　Oracle Database (全部俺）Advent Calendar 2022 Day 1　/ No.36 / INTERSECT ALL
・実行計画は, SQL文のレントゲン写真だ！　Oracle Database (全部俺）Advent Calendar 2022 Day 2　/ No.37 / MINUS ALL
・実行計画は, SQL文のレントゲン写真だ！　Oracle Database (全部俺）Advent Calendar 2022 Day 3　/ No.38 / EXCEPT and EXCEPT ALL
・実行計画は, SQL文のレントゲン写真だ！　Oracle Database (全部俺）Advent Calendar 2022 Day 4 / No.39 / In-Memory Hybrid Scans
・実行計画は, SQL文のレントゲン写真だ！　Oracle Database (全部俺）Advent Calendar 2022 Day 5 / No.40 / PIVOT and UNPIVOT
・実行計画は, SQL文のレントゲン写真だ！　Oracle Database (全部俺）Advent Calendar 2022 Day 6 / No.41 / In-Memory Vectorized Join
・実行計画は, SQL文のレントゲン写真だ！　Oracle Database (全部俺）Advent Calendar 2022 Day 7 / No.42 / INDEX RANGE SCAN (MULTI VALUE)
・実行計画は, SQL文のレントゲン写真だ！　Oracle Database (全部俺）Advent Calendar 2022 Day 8 / No.43 / TABLE ACCESS BY INDEX ROWID BATCHED
・実行計画は, SQL文のレントゲン写真だ！　Oracle Database (全部俺）Advent Calendar 2022 Day 9 / No.44 / COLLECTION ITERATOR PICKLER FETCH
・実行計画は, SQL文のレントゲン写真だ！　Oracle Database (全部俺）Advent Calendar 2022 Day 10 / No.45 / MAT_VIEW REWRITE ACCESS FULL
・実行計画は, SQL文のレントゲン写真だ！　Oracle Database (全部俺）Advent Calendar 2022 Day 11 / No.46 / GROUPING SETS, ROLLUP, CUBE
・実行計画は, SQL文のレントゲン写真だ！　Oracle Database (全部俺）Advent Calendar 2022 Day 12 / No.47 /　TEMP TABLE TRANSFORMATION
・実行計画は, SQL文のレントゲン写真だ！　Oracle Database (全部俺）Advent Calendar 2022 Day 13 / No.48 / MULTI-TABLE INSERT
・実行計画は, SQL文のレントゲン写真だ！　Oracle Database (全部俺）Advent Calendar 2022 Day 14 / No.49 / the DUAL Table
・実行計画は, SQL文のレントゲン写真だ！　Oracle Database (全部俺）Advent Calendar 2022 Day 15 / No.50 / REMOTE
・実行計画は, SQL文のレントゲン写真だ！　Oracle Database (全部俺）Advent Calendar 2022 Day 16 / No.51 / Concurrent Execution of Union All and Union
・実行計画は, SQL文のレントゲン写真だ！　Oracle Database (全部俺）Advent Calendar 2022 Day 17 / No.52 / Order by Elimination
・実行計画は, SQL文のレントゲン写真だ！　Oracle Database (全部俺）Advent Calendar 2022 Day 18 / No.53 / Join Elimination
・実行計画は, SQL文のレントゲン写真だ！　Oracle Database (全部俺）Advent Calendar 2022 Day 19 / No.54 / Group by Elimination
・実行計画は, SQL文のレントゲン写真だ！　Oracle Database (全部俺）Advent Calendar 2022 Day 20 / No.55 / DISTINCT Elimination
・実行計画は, SQL文のレントゲン写真だ！　Oracle Database (全部俺）Advent Calendar 2022 Day 21 / No.56 / INLIST ITERATOR と Sub Query と STATISTICS COLLECTOR
・実行計画は, SQL文のレントゲン写真だ！　Oracle Database (全部俺）Advent Calendar 2022 Day 22 / No.57 / Subquery Unnesting
・実行計画は, SQL文のレントゲン写真だ！　Oracle Database (全部俺）Advent Calendar 2022 Day 23 / No.58 / ANTI JOIN
・実行計画は, SQL文のレントゲン写真だ！　Oracle Database (全部俺）Advent Calendar 2022 Day 24 / No.59 / SQL MACRO (19.7〜)
・実行計画は, SQL文のレントゲン写真だ！　Oracle Database (全部俺）Advent Calendar 2022 Day 25 / No.60 / ANSI JOIN
・実行計画は, SQL文のレントゲン写真だ！　Oracle Database (全部俺）Advent Calendar 2022 / No.60 / ANSI JOINのおまけ
・実行計画は, SQL文のレントゲン写真だ！　Oracle Database (全部俺）Advent Calendar 2022 / No.61 / ANSI JOINのおまけのおまけ
・実行計画は, SQL文のレントゲン写真だ！ No.62 / ORDBMS機能であるコレクション型の列をアクセスする実行計画ってどうなるの？
・実行計画は, SQL文のレントゲン写真だ！ No.63 / Join Elimination （再び）その1
・実行計画は, SQL文のレントゲン写真だ！ No.63 / Join Elimination （再び）その2
・実行計画は, SQL文のレントゲン写真だ！ No.63 / Join Elimination （再び）その3
・実行計画は, SQL文のレントゲン写真だ！ No.64 / 先生、私のLEFT OUTER JOINが無いんです！！（Join Elimination番外編）
・実行計画は, SQL文のレントゲン写真だ！ No.65 / 忘れ去られたオプティマイザーヒントとTABLE ACCESS BY USER ROWID
・実行計画は, SQL文のレントゲン写真だ！ No.66 / AI Vector Search - VECTOR INDEX HNSW SCAN

 

投稿日時 2025年7月 4日 (金) 12時48分 Oracle, チューニング, FAQ, SQL, パフォーマンス, in-memory, VECTOR, AI, Oracle Database 23ai | | | コメント (0)