帰ってきた！ 標準はあるにはあるが癖の多いSQL #17 - ANY_VALUE() ってなかなかいいじゃん、癖無さそう！ Tweet
さて、
今回は久々に、標準はあるにはあるが癖の多い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;
| 固定リンク | 0
コメント