帰ってきた！ 標準はあるにはあるが癖の多いSQL #20 - Table Value Constructer （TVC） Tweet
やってまいりました。年末恒例のアドベントカレンダー。
本エントリーは、以下アドベントカレンダーの12日目のクロスポストとなっています。
JPOUG Advent Calendar 2025 - Oracle Database
PostgreSQL Advent Calendar 2025
MySQL Advent Calendar 2025
11日目の窓は、それぞれ、
Oracle Databaseでマルチレイアウトのテーブルを作る方法その1 - HiroyukiNakaie さん / JPOUG Advent Calendar 2025 - Oracle Database
セキュリティ対策としての PostgreSQL マイナーバージョンアップ (PGCON2025 発表資料) - jri_narita さん / PostgreSQL Advent Calendar 2025
今年勉強会などで MySQL / HeatWave に関して話したことの振り返り＋α - hmatsu47 さん / MySQL Advent Calendar 2025
でした。
今回のお題は、
帰ってきた！ 標準はあるにはあるが癖の多いSQL #19 - c_alias の癖（おまけ） で
ネタフリしていた、TVC です。と言っても、この曲じゃありません！！！！（この曲、知ってる人どれぐらいいるだろうw）
TVC15 / David Bowie
TVC = Table Value Constructor / 表値コンストラクタには、どのような癖があるのか、否か、、、確認しておきたいと思います。
ということで本題。
PostgreSQLではかなり前から実装されていた表値コンストラクタですが、MySQLではMySQL 8.0.19以降、Oracle Databaseでも、その流れで？！（どういう流れだw）、サポートされた感じがしますw（個人の感想です）
この表値コンストラクタ、注意点としては複数のマニュアルに記載されているので気づきやすいと思いますが、大量の行を生成することを意図したものではないという点のようですね。
メモリ消費量や最適化によっては、内部的に一時表などが利用されそうなの記述もありますね。
ということで、表値コンストラクタの癖探しの旅へw
まず、Oracle Database/MySQL/PostgreSQL、それぞれのマニュアルに目を通しておきましょう。
Oracle Database / Release 23 / values_clause::=
https://docs.oracle.com/cd/G11854_01/sqlrf/SELECT.html#GUID-CFA006CA-6FF1-4972-821E-6996142A51C6__GUID-27159C8E-617B-4ECE-AA4C-1800287F0C9D
Oracle Database / Release 23 / values_clause
https://docs.oracle.com/cd/G11854_01/sqlrf/SELECT.html#GUID-CFA006CA-6FF1-4972-821E-6996142A51C6__SECTION_UMB_QGC_FWB
values_clause::= 、および、expression_list::=
Oracle Databaseの場合、シンタックスを見る限り、value_clauseに含めることができる Expression_listの制限が、TVCで指定できる最大行数になりそうですよね。わかりにくいですが。。この点は今回確認しておきましょう。
https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/IN-Condition.html#SQLRF-GUID-C7961CB3-8F60-47E0-96EB-BDCF5DB1317C
MySQL 8.0 リファレンスマニュアル / SQL ステートメント / データ操作ステートメント / VALUES ステートメント
https://dev.mysql.com/doc/refman/8.0/ja/values.html
PostgreSQL 17.5文書 / SQLコマンド / VALUES
https://www.postgresql.jp/document/17/html/sql-values.html
いきなり癖、発見！www 癖多そう！！！！
これらのマニュアルを斜め読みしただけも癖のあることに気づきます。
Oracle Databaseは、VALUES句のみサポートされています、MySQL/PostgreSQLはVALUESコマンドとしても使える！。それ使う場面あるのか？！ と思ったり。MySQLでは、ROW() 行値コンストラクタが必要であることなどがあります。（MySQLのINSERT文では行値コンストラクタは必須ではなさそうなので、SELECT文でも同様に扱って欲しいきがします）
その他、TVCは少量のデータを想定していると記載されているものの、最大行数制限となりそうな記述は、Oracle Databaseぐらいですし、メモリ消費もそれなりに高めなので、やりたい放題って状況は避けるべきでしょうね。
なお、今回準備した環境制限ですが、Oracle Database 23ai FREE on VirtualBOXはメモリサイズが2GBに制限されているため、メモリサイズ(PGA含め)に依存しそうな上限確認のテストでは少々厳しめでした。
ログが多めなので、最初にTVCの癖の数々をサマっておきます！
- TVCで生成できる行数の上限
- Oracle Database : 65534行。マニュアル上は、65535行に読めるのだが、ここまで使うこともないはずw
- MySQL/PostgreSQL : 明示的な制限なし
- なお、少量データを想定した機能と記載されているので、大量のデータを生成するのは避けた方が無難。他の方法があるので。
- TVCの表エイリアス記述
- Oracle Database : 必須
- MySQL : 必須
- PostgreSQL : 任意
- TVCの列エイリアス記述
- Oracle Database : 必須。ただし、列値の個数と列エイリアスの個数は同一であること。
- MySQL : 任意。ただし、列エイリアスを記述する場合は、列値の個数と列エイリアスの個数は同一であること。
- e.g. SELECT * FROM (VALUES ROW(1,2)) t01; の場合、column_0 , column_1 という列エイリアスが付与される
- PostgreSQL : 任意。列値の個数と列エイリアスの個数は一致する必要はない。列エイリアスのない列値には、デフォルトの列エイリアスが付与される。
- e.g. SELECT * FROM (VALUES ROW(1,2)) t01; の場合、column1 , column2 という列エイリアスが付与される
- e.g. SELECT * FROM (VALUES ROW(1,2)) t01 (c1); の場合、c1 , column2 という列エイリアスが付与される
- 通常はコーディング規約で縛って、表エイリアスと列エイリアスの記述を必須にることがほとんどだと思われる。PostgreSQLはかなり緩め。MySQLは少々トリッキー、書き漏らした場合、気づくのが遅れることが多そうなので要注意。
- 行値コンストラクが必要
- Oracle Database : 行値コンストラクタ不要
- MySQL : 行値コンストラクタ ROW() 必須
- PostgreSQL : 行値コンストラクタ不要
- VALUESコマンドのサポート
- Oracle Database : サポートしていない
- MySQL : サポートしている
- PostgreSQL : サポートしている
- コマンドとし単体で使えるのって嬉しいのかよくわからないのだが、どうなんだろう。
- 実行計画
- Oracle Database : VALUES SCAN として現れる。
- MySQL : TREE形式の実行計画を見る限り、TVCが利用されていることを識別することはできない（8.4.7より後ではどうなるか、わからないが。）
- PostgreSQL : Values Scan on "*VALUES*" として現れる（Oracle Databaseが後発なので、PostgreSQLの表示に近い表現にしたのかもしれない）
では、いろいろ動かして前述した癖の挙動を見ていきましょう。
後半で、大量生成しないことが推奨されているTVCで大量の値を生成したらどうなっちゃうのか。。。というあたりまで見ておきますw
そういうことやっちゃう方々は出てくるかもなーと予想しつつw。。。
e.g. IN句に仕様の限界まで値を詰めて、さらに OR条件でさらに繰り返しちゃう。。。とか、稀によく見ますし。w
TVCも無邪気に大量の行を生成させちゃうと。。。いろいろ副作用が強そうな部分もありw（今回はそこまで試しませんが。。。）
PostgreSQL (17.6)
マニュアルのバージョンを遡るとサポートされ始めたのは3種の中では最も古く、PostgreSQL 8.2.6文書 VALUESにあるように Ver. 8.2（2006年リリース）のころにはあったようですね。
Mac De OracleでPostgreSQL/MySQLも含めたネタが2005年12月のMac De Oracle Heterogeneous! #1で、PostgreSQL7.4.9/MySQL4.1.13a/MySQL4.0.25なので、そんな前だったか〜と、遠い目をしているところw。。。。。。
version
-------------------------------------------------------------------------------
PostgreSQL 17.6 on aarch64-unknown-linux-gnu,
compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-26), 64-bit
perftestdb=> VALUES (1, 'one'), (2, 'two'), (3, 'three');
column1 | column2
---------+---------
1 | one
2 | two
3 | three
TVCの行数が増加するとExecution Timeもそうですが、Plannningで消費するメモリサイズが増加しそうなのでExplain時にmemoryオプションも付加しています。
perftestdb=> explain (memory, buffers, analyze, verbose) VALUES (1, 'one'), (2, 'two'), (3, 'three');
QUERY PLAN
--------------------------------------------------------------------------------------------------------
Values Scan on "*VALUES*" (cost=0.00..0.04 rows=3 width=36) (actual time=0.002..0.003 rows=3 loops=1)
Output: column1, column2
Planning:
Memory: used=7kB allocated=8kB
Planning Time: 0.023 ms
Execution Time: 0.008 ms
MySQL (8.4.7)
(PostgreSQL同様、ARM版です)
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.4.7 |
+-----------+
1 row in set (0.01 sec)
PostgreSQLに似ているようで似てない癖もあるようです。少々脱線してますが、INSERT文と組み合わせる場合は、全列で列値コンストラクタROW()を使うか、全く使わないかのどちらか、というトリッキーな仕様もあるようです。
mysql> VALUES (1, 'one'), (2, 'two'), (3, 'three');
ERROR 1064 (42000): You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server version for the right syntax to use near '(1, 'one'), (2, 'two'), (3, 'three')' at line 1
mysql>
mysql>
mysql> VALUES ROW(1, 'one'), ROW(2, 'two'), ROW(3, 'three');
+----------+----------+
| column_0 | column_1 |
+----------+----------+
| 1 | one |
| 2 | two |
| 3 | three |
+----------+----------+
3 rows in set (0.00 sec)
mysql> create table hoge (id integer);
Query OK, 0 rows affected (0.02 sec)
mysql> insert into hoge(id) values (1),(2),(3),(4);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> insert into hoge(id) values ROW(5),ROW(6),ROW(7),ROW(8);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> insert into hoge(id) values ROW(9),ROW(10),(11),(12);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(11),(12)' at line 1
PostgreSQLとは異なり、実行計画上（TREEフォーマット)、TVCを利用しているということは読み取れないですね。実行計画からTVCを利用していると読み取れると判別しやすくて良いのではないだろうか。。。どう思います？
mysql> explain analyze format=tree VALUES ROW(1, 'one'), ROW(2, 'two'), ROW(3, 'three');
+---------------------------------------------------------------------------------------------------+
| EXPLAIN |
+---------------------------------------------------------------------------------------------------+
| -> Rows fetched before execution (cost=0..0 rows=3) (actual time=167e-6..209e-6 rows=3 loops=1)
|
+---------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
Oracle Database (23.8)
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
試すまでもないわけですがw、Oracle Databaseでは、PostgreSQL/MySQLのVALUESステートメントはどちらもエラー。
SCOTT@localhost:1521/freepdb1> VALUES (1, 'one'), (2, 'two'), (3, 'three');
SP2-0734: "VALUES (1,..."で開始するコマンドが不明です - 残りの行は無視されました。
ヘルプ: https://docs.oracle.com/error-help/db/sp2-0734/
SCOTT@localhost:1521/freepdb1> VALUES ROW(1, 'one'), ROW(2, 'two'), ROW(3, 'three');
SP2-0734: "VALUES ROW..."で開始するコマンドが不明です - 残りの行は無視されました。
ヘルプ: https://docs.oracle.com/error-help/db/sp2-0734/
つづいて、Oracle DatabaseでもサポートされているTVCの癖。SELECT文やWITH句で利用するケースです。
インラインビューの形で書いて、表エイリアスと列エイリアスも合わせて記述しています。
表エイリアスと列エイリアスの指定が必須か否か、など癖が多い（後述）
PostgreSQL (17.6)
perftestdb=> SELECT *
perftestdb-> FROM
perftestdb-> (
perftestdb(> VALUES
perftestdb(> (1, 'SCOTT')
perftestdb(> ,(2, 'SMITH')
perftestdb(> ,(3, 'JOHN' )
perftestdb(> ) t1 (
perftestdb(> employee_id
perftestdb(> , first_name
perftestdb(> );
employee_id | first_name
-------------+------------
1 | SCOTT
2 | SMITH
3 | JOHN
(3 rows)
perftestdb=> explain (memory, buffers, analyze, verbose)
perftestdb-> SELECT *
perftestdb-> FROM
perftestdb-> (
perftestdb(> VALUES
perftestdb(> (1, 'SCOTT')
perftestdb(> ,(2, 'SMITH')
perftestdb(> ,(3, 'JOHN' )
perftestdb(> ) t1 (
perftestdb(> employee_id
perftestdb(> , first_name
perftestdb(> );
QUERY PLAN
--------------------------------------------------------------------------------------------------------
Values Scan on "*VALUES*" (cost=0.00..0.04 rows=3 width=36) (actual time=0.010..0.013 rows=3 loops=1)
Output: "*VALUES*".column1, "*VALUES*".column2
Planning:
Memory: used=11kB allocated=16kB
Planning Time: 0.174 ms
Execution Time: 0.050 ms
(6 rows)
Oracle Database (23.8)
PostgreSQLと同一シンタックスでOKです。
SCOTT@localhost:1521/freepdb1> l
1 SELECT /*+ MONITOR */ *
2 FROM
3 (
4 VALUES
5 (1, 'SCOTT')
6 ,(2, 'SMITH')
7 ,(3, 'JOHN' )
8 ) t1 (
9 employee_id
10 , first_name
11* )
SCOTT@localhost:1521/freepdb1> /
EMPLOYEE_ID FIRST
----------- -----
1 SCOTT
2 SMITH
3 JOHN
経過: 00:00:00.00
実行計画もPostgreSQLのようにVALUES SCANとして現れます。VIEWとあるようにインラインビューとして認識されている点も読み取れますよね
SCOTT@localhost:1521/freepdb1> @show_sqlmonitor
DBMS_SQLTUNE.REPORT_SQL_MONITOR(SQL_ID=>'',TYPE=>'TEXT')
-------------------------------------------------------------------------------------------------------------------
SQL Monitoring Report
SQL Text
------------------------------
SELECT /*+ MONITOR */ * FROM ( VALUES (1, 'SCOTT') ,(2, 'SMITH') ,(3, 'JOHN' ) ) t1 ( employee_id , first_name )
...略...
Global Stats
=============================
| Elapsed | Cpu | Fetch |
| Time(s) | Time(s) | Calls |
=============================
| 0.00 | 0.00 | 2 |
=============================
SQL Plan Monitoring Details (Plan Hash Value=1233125608)
======================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | (%) | (# samples) |
======================================================================================================================
| 0 | SELECT STATEMENT | | | | 1 | +0 | 1 | 3 | | |
| 1 | VIEW | | 3 | 6 | 1 | +0 | 1 | 3 | | |
| 2 | VALUES SCAN | | 3 | 6 | 1 | +0 | 1 | 3 | | |
======================================================================================================================
MySQL (8.4)
MySQLはすでに癖があることは解説済みですが、SELECT文で使う場合も行値コンストラクタが必要です。
mysql> SELECT *
-> FROM
-> (
-> VALUES
-> (1, 'SCOTT')
-> ,(2, 'SMITH')
-> ,(3, 'JOHN' )
-> ) t1 (
-> employee_id
-> , first_name
-> );
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds
to your MySQL server version for the right syntax to use near '(1, 'SCOTT')
,(2, 'SMITH')
,(3, 'JOHN' )
) t1 (
employee_id
, first' at line 5
mysql>
mysql> SELECT *
-> FROM
-> (
-> VALUES
-> ROW(1, 'SCOTT')
-> ,ROW(2, 'SMITH')
-> ,ROW(3, 'JOHN' )
-> ) t1 (
-> employee_id
-> , first_name
-> );
+-------------+------------+
| employee_id | first_name |
+-------------+------------+
| 1 | SCOTT |
| 2 | SMITH |
| 3 | JOHN |
+-------------+------------+
3 rows in set (0.00 sec)
MySQLの場合、実行計画だけでTVCが利用されているということは判断できないのはVALUESコマンドと同様。
（小さい癖ですが。SQL文を合わせて見るようにしないと見落としてしまう可能性はありますね。実行計画だけ見るってこと自体があまり無いとは思いますが、そういう方も中にはいるので。）
mysql> explain analyze format=tree
-> SELECT *
-> FROM
-> (
-> VALUES
-> ROW(1, 'SCOTT')
-> ,ROW(2, 'SMITH')
-> ,ROW(3, 'JOHN' )
-> ) t1 (
-> employee_id
-> , first_name
-> );
+---------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+---------------------------------------------------------------------------------------------------------+
| -> Table scan on t1 (cost=1.15..2.84 rows=3) (actual time=0.078..0.0795 rows=3 loops=1)
-> Materialize (cost=0.3..0.3 rows=3) (actual time=0.0713..0.0713 rows=3 loops=1)
-> Rows fetched before execution (cost=0..0 rows=3) (actual time=993e-6..0.00162 rows=3 loops=1)
|
+---------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
すでに特徴的な癖がいくつかありますが、次は、TVCに関わる表エイリアスと列エイリアスの指定に有無に関わる癖の違いの確認。
三者三様の癖があります。試験に出るので覚えておきましょう！（ないないw
1) TVCインラインビューで、表エイリアスと列エイリアスを記述しなかった場合
Oracle DatabaseとMySQLでは表エイリアスは必須なのでエラーなのですが、PostgreSQLは許容範囲広いっすね！
PostgreSQL (17.6)
perftestdb=> select * from (values (1),(2));
column1
---------
1
2
(2 rows)
MySQL (8.4.7)
mysql> select * from (values row(1),row(2));
ERROR 1248 (42000): Every derived table must have its own alias
Oracle Database (23.8)
SCOTT@localhost:1521/freepdb1> select * from (values (1),(2));
select * from (values (1),(2))
*
行1でエラーが発生しました。:
ORA-00931: 識別子がありません。
ヘルプ:
https://docs.oracle.com/error-help/db/ora-00931/
2) TVCインラインビューで、表エイリアス記述した場合
1)でエラーとなったMySQLはシンタックスエラーなし=表エイリアスの記述は必須！。列エイリアスは任意？！っぽい。
しかし、Oracle Databaseはエラーのままです、列エイリアスも必要！！！！！。
PostgreSQL (17.6)
perftestdb=> select * from (values (1),(2)) t01;
column1
---------
1
2
(2 rows)
MySQL (8.4.7)
mysql> select * from (values row(1),row(2)) t01;
+----------+
| column_0 |
+----------+
| 1 |
| 2 |
+----------+
2 rows in set (0.01 sec)
Oracle Database (23.8)
SCOTT@localhost:1521/freepdb1> select * from (values (1),(2)) t01;
select * from (values (1),(2)) t01
*
行1でエラーが発生しました。:
ORA-63814: 表値コンストラクタの別名に列名を指定する必要があります。
ヘルプ:
https://docs.oracle.com/error-help/db/ora-63814/
3) TVCインラインビューで、表エイリアスと列エイリアスを記述した場合
やっと、全て正常に実行された！！！
MySQL/PostgreSQLでは任意とは言え、実際に利用する場合にはSQLコーディングルールで縛るでしょうね。絶対。
そういう意味では、Oracle Databaseのように必須にしちゃったほうがSQL各側にとっては楽なのではないだろうか。ミスるとエラーにしてくれし。
PostgreSQL (17.6)
perftestdb=> select * from (values (1),(2)) t01(id);
id
----
1
2
(2 rows)
MySQL (8.4.7)
mysql> select * from (values row(1),row(2)) t01(id);
+----+
| id |
+----+
| 1 |
| 2 |
+----+
2 rows in set (0.00 sec)
Oracle Database (23.8)
SCOTT@localhost:1521/freepdb1> select * from (values (1),(2)) t01(id);
ID
----------
1
2
4) TVCインラインビューで、表エイリアスと列エイリアスを記述したが、記述した列エイリアス数と列数不一致の場合
PostgreSQLとOracle Databaseは予想通りの挙動でしたが、MySQLは想定の斜め上の挙動！
PostgreSQLは列エイリアスは任意だし、列値の個数と一致しなくても、Whatever！
Oracle Database、列エイリアスは必須だし、列値の個数と一致してないと、ダメ、絶対！
MySQL、列エイリアスは任意だけど、指定するなら列値の個数と一致してないと、ダメ！
個性派揃いですね！！！！w
ところで、PostgreSQL付与の列エイリアスって、列順なのね。2列目の列エイリアスを記述しないと、column2 が付与される。
PostgreSQL (17.6)
perftestdb=> select * from (values (1,1),(2,2)) t01(id);
id | column2
----+---------
1 | 1
2 | 2
(2 rows)
MySQL (8.4.7)
mysql> select * from (values row(1,1),row(2,2)) t01(id);
ERROR 1353 (HY000): In definition of view, derived table or common table expression,
SELECT list and column names list have different column counts
Oracle Database (23.8)
SCOTT@localhost:1521/freepdb1> select * from (values (1,1),(2,2)) t01(id);
select * from (values (1,1),(2,2)) t01(id)
*
行1でエラーが発生しました。:
ORA-63815: 列名の数は、表値コンストラクタの値の数と一致する必要があります。
ヘルプ:
https://docs.oracle.com/error-help/db/ora-63815/
5) TVCインラインビューで、表エイリアスと列値の数と同数の列エイリアスを指定した場合
こう書けば何も問題ないよーっ。
PostgreSQL (17.6)
perftestdb=> select * from (values (1,1),(2,2)) t01(id,seq1);
id | seq1
----+------
1 | 1
2 | 2
(2 rows)
MySQL (8.4.7)
mysql> select * from (values row(1,1),row(2,2)) t01(id,seq1);
+----+------+
| id | seq1 |
+----+------+
| 1 | 1 |
| 2 | 2 |
+----+------+
2 rows in set (0.00 sec)
Oracle Database (23.8)
SCOTT@localhost:1521/freepdb1> select * from (values (1,1),(2,2)) t01(id,seq1);
ID SEQ1
---------- ----------
1 1
2 2
WITH句で使うこともできます！（細かい挙動までは追わないが）
PostgreSQL (17.6)
perftestdb=> WITH
perftestdb-> t01 AS
perftestdb-> (
perftestdb(> SELECT *
perftestdb(> FROM
perftestdb(> (
perftestdb(> VALUES
perftestdb(> (1, 'SCOTT')
perftestdb(> ,(2, 'SMITH')
perftestdb(> ,(3, 'JOHN' )
perftestdb(> ) x01 (
perftestdb(> employee_id
perftestdb(> , first_name
perftestdb(> )
perftestdb(> )
perftestdb-> SELECT * FROM t01;
employee_id | first_name
-------------+------------
1 | SCOTT
2 | SMITH
3 | JOHN
(3 rows)
perftestdb=> explain (memory, buffers, analyze, verbose)
perftestdb-> WITH
perftestdb-> t01 AS
perftestdb-> (
perftestdb(> SELECT *
perftestdb(> FROM
perftestdb(> (
perftestdb(> VALUES
perftestdb(> (1, 'SCOTT')
perftestdb(> ,(2, 'SMITH')
perftestdb(> ,(3, 'JOHN' )
perftestdb(> ) x01 (
perftestdb(> employee_id
perftestdb(> , first_name
perftestdb(> )
perftestdb(> )
perftestdb-> SELECT * FROM t01;
QUERY PLAN
--------------------------------------------------------------------------------------------------------
Values Scan on "*VALUES*" (cost=0.00..0.04 rows=3 width=36) (actual time=0.002..0.003 rows=3 loops=1)
Output: "*VALUES*".column1, "*VALUES*".column2
Planning:
Memory: used=22kB allocated=32kB
Planning Time: 0.039 ms
Execution Time: 0.011 ms
(6 rows)
Oracle Database (23.8)
SCOTT@localhost:1521/freepdb1> l
1 WITH
2 t01 AS
3 (
4 SELECT *
5 FROM
6 (
7 VALUES
8 (1, 'SCOTT')
9 ,(2, 'SMITH')
10 ,(3, 'JOHN' )
11 ) x01 (
12 employee_id
13 , first_name
14 )
15 )
16* SELECT /*+ MONITOR */ * FROM t01
SCOTT@localhost:1521/freepdb1> /
EMPLOYEE_ID FIRST
----------- -----
1 SCOTT
2 SMITH
3 JOHN
SCOTT@localhost:1521/freepdb1> @show_sqlmonitor
...略...
Global Stats
========================================
| Elapsed | Cpu | Other | Fetch |
| Time(s) | Time(s) | Waits(s) | Calls |
========================================
| 0.00 | 0.00 | 0.00 | 2 |
========================================
SQL Plan Monitoring Details (Plan Hash Value=1233125608)
======================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | (%) | (# samples) |
======================================================================================================================
| 0 | SELECT STATEMENT | | | | 1 | +0 | 1 | 3 | | |
| 1 | VIEW | | 3 | 6 | 1 | +0 | 1 | 3 | | |
| 2 | VALUES SCAN | | 3 | 6 | 1 | +0 | 1 | 3 | | |
======================================================================================================================
Oracle Databaseの場合、通常はインラインビューへリライトされるケースでも、マテリアライズすることができるので、実行計画がどう変化するかも見ておきましょう！
一時表としてマテリアライズされ、CURSOR DURATION MEMORYによりPGA上に一時的に保持されています。すべてPGAに乗る程度のサイズなら繰り返し参照されるケースでは有利なのは自明です。このケースでは無駄ですがw
SCOTT@localhost:1521/freepdb1> l
1 WITH
2 t01 AS
3 (
4 SELECT /*+ MATERIALIZE */ *
5 FROM
6 (
7 VALUES
8 (1, 'SCOTT')
9 ,(2, 'SMITH')
10 ,(3, 'JOHN' )
11 ) x01 (
12 employee_id
13 , first_name
14 )
15 )
16* SELECT /*+ MONITOR */ * FROM t01
SCOTT@localhost:1521/freepdb1> /
EMPLOYEE_ID FIRST
----------- -----
1 SCOTT
2 SMITH
3 JOHN
経過: 00:00:00.00
SCOTT@localhost:1521/freepdb1> @show_sqlmonitor
...略...
Global Stats
=================================================
| Elapsed | Cpu | Other | Fetch | Buffer |
| Time(s) | Time(s) | Waits(s) | Calls | Gets |
=================================================
| 0.00 | 0.00 | 0.00 | 2 | 2 |
=================================================
SQL Plan Monitoring Details (Plan Hash Value=1856684117)
=============================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | (Max) | (%) | (# samples) |
=============================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 1 | +0 | 1 | 3 | . | | |
| 1 | TEMP TABLE TRANSFORMATION | | | | 1 | +0 | 1 | 3 | . | | |
| 2 | LOAD AS SELECT (CURSOR DURATION MEMORY) | SYS_TEMP_0FD9D660A_6FD25E | | | 1 | +0 | 1 | 1 | 1024 | | |
| 3 | VIEW | | 3 | 6 | 1 | +0 | 1 | 3 | . | | |
| 4 | VALUES SCAN | | 3 | | 1 | +0 | 1 | 3 | . | | |
| 5 | VIEW | | 3 | 2 | 1 | +0 | 1 | 3 | . | | |
| 6 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660A_6FD25E | 3 | 2 | 1 | +0 | 1 | 3 | . | | |
=============================================================================================================================================================================
MySQL (8.4.7)
mysql> WITH
-> t01 AS
-> (
-> SELECT *
-> FROM
-> (
-> VALUES
-> ROW(1, 'SCOTT')
-> ,ROW(2, 'SMITH')
-> ,ROW(3, 'JOHN' )
-> ) x01 (
-> employee_id
-> , first_name
-> )
-> )
-> SELECT * FROM t01;
+-------------+------------+
| employee_id | first_name |
+-------------+------------+
| 1 | SCOTT |
| 2 | SMITH |
| 3 | JOHN |
+-------------+------------+
3 rows in set, 0 warning (0.00 sec)
mysql> explain analyze format=tree
-> WITH
-> t01 AS
-> (
-> SELECT *
-> FROM
-> (
-> VALUES
-> ROW(1, 'SCOTT')
-> ,ROW(2, 'SMITH')
-> ,ROW(3, 'JOHN' )
-> ) x01 (
-> employee_id
-> , first_name
-> )
-> )
-> SELECT * FROM t01;
+-------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+-------------------------------------------------------------------------------------------------------+
| -> Table scan on x01 (cost=1.15..2.84 rows=3) (actual time=0.0102..0.0105 rows=3 loops=1)
-> Materialize (cost=0.3..0.3 rows=3) (actual time=0.00871..0.00871 rows=3 loops=1)
-> Rows fetched before execution (cost=0..0 rows=3) (actual time=208e-6..417e-6 rows=3 loops=1)
|
+-------------------------------------------------------------------------------------------------------+
1 row in set, 0 warning (0.00 sec)
では、最後に、もう一つだけ確認。
再掲
Oracle Database / Release 23 / values_clause
https://docs.oracle.com/cd/G11854_01/sqlrf/SELECT.html#GUID-CFA006CA-6FF1-4972-821E-6996142A51C6__SECTION_UMB_QGC_FWB
values_clause::= 、および、expression_list::=
Oracle Databaseの場合、シンタックスを見る限り、value_clauseに含めることができる Expression_listの制限が、TVCで指定できる最大行数になりそうですよね。わかりにくいですが。。この点は今回確認しておきましょう。
https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/IN-Condition.html#SQLRF-GUID-C7961CB3-8F60-47E0-96EB-BDCF5DB1317C
Oracle Database (23.8)のTVCでは、マニュアルのシンタックス等から推測するに、生成できる行数に制限があるように読み取れるのだが、MySQL/PostgreSQLともにそれに類する記載は見つけられなかった。
ただ、いずれもメモリはそれなりに消費するようなので、メモリ消費量はそれなりの影響はありそうではある。。。
（明示されている箇所があればマニュアルのURLを教えていただけるとありがたい）
Oracle Database (23.8)
ということで、Oracle Database (23.8)の上限と思われる。 65535行前後程度までTVCで生成し挙動だけ（上限でエラーになるのか？）を確認しておく。
なお、Oracle Database 23ai FREEはインスタンスが利用できるメモリサイズ上限自体が2GBなので、検証する前にメモリ関連エラーになる可能性はある。。どうなりますか。。。
コード生成 Oracle 無名PL/SQL (Oracle DatabaseでMySQL向けSQLも生成しちゃいますw)。コードは後述。
マニュアルだと、65535行まではできそうだったが、65534行までがただしいようだ。いずれにしても実際に使うとなると1000行以下だとおもうけど。
65535行を生成するTVCはORA-63805: 表値コンストラクタのタプルの最大数を超えました となりました。あれ？
SCOTT@localhost:1521/freepdb1> @make_tvc_sql0.sql 65535 oracle
SCOTT@localhost:1521/freepdb1> set autot traceonly
SCOTT@localhost:1521/freepdb1> @sql_oracle_65535
SELECT * FROM ( VALUES
*
行1でエラーが発生しました。:
ORA-63805: 表値コンストラクタのタプルの最大数を超えました
ヘルプ:
https://docs.oracle.com/error-help/db/ora-63805/
経過: 00:00:00.05
ということで、65534行生成するTVCにすると実行できました。とは言ってもtopで眺めてみるとメモリ消費は激しいなという状況。。。その辺り別の機会に。
ちなみに、Oracle Database 23ai FREEって2GBっていうメモリの制限があったりするので、このケースだと explain plan for や autotrace expとかで実行計画も取得しようとするとメモリがらみのエラーが発生した（FREEのメモリサイズ制限2GBまでなので増やせない罠）ので、実行統計だけにしてあります。:)
この手の限界テストしようとするとFREEのメモリ制限ってキツいですよねw
SCOTT@localhost:1521/freepdb1> @make_tvc_sql0 65534 oracle
SCOTT@localhost:1521/freepdb1> set autot on stat
SCOTT@localhost:1521/freepdb1> @sql_oracle_65534
ID
----------
1
2
3
...略...
65532
65533
65534
65534行が選択されました。
経過: 00:53:31.31
統計
----------------------------------------------------------
1 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
1269854 bytes sent via SQL*Net to client
680591 bytes received via SQL*Net from client
4370 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
65534 rows processed
PostgreSQL (17.6)
TVCで生成する行数制限はなさそうですが、Planningのメモリサイズは1行生成の単純なものと比べるとかなり増えてますね。
perftestdb=> \i /var/lib/pgsql/sql_postgresql_65534.sql
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
Values Scan on "*VALUES*" (cost=0.00..819.18 rows=65534 width=4) (actual time=0.009..5.500 rows=65534 loops=1)
Output: "*VALUES*".column1
Planning:
Buffers: shared hit=3
Memory: used=19977kB allocated=26113kB
Planning Time: 10.755 ms
Execution Time: 7.745 ms
(7 rows)
perftestdb=> \i /var/lib/pgsql/sql_postgresql_65535.sql
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
Values Scan on "*VALUES*" (cost=0.00..819.19 rows=65535 width=4) (actual time=0.008..5.561 rows=65535 loops=1)
Output: "*VALUES*".column1
Planning:
Buffers: shared hit=3
Memory: used=19977kB allocated=26113kB
Planning Time: 10.969 ms
Execution Time: 7.825 ms
(7 rows)
MySQL (8.4.7)
MySQLも何事もなく実行できちゃいますね。マニュアルにはTVCの行数制限はないですが、多分、でかくするとメモリ消費は激しくなるんだろなぁ。と、想像しています。PostgreSQLもPlannerのメモリ使用量がかなり大きくなっていたので。。
mysql> \. sql_mysql_65535.sql
+-------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+-------------------------------------------------------------------------------------------------------------+
| -> Table scan on t1 (cost=6554..7375 rows=65535) (actual time=15.9..20.9 rows=65535 loops=1)
-> Materialize (cost=6554..6554 rows=65535) (actual time=15.9..15.9 rows=65535 loops=1)
-> Rows fetched before execution (cost=0..0 rows=65535) (actual time=360e-6..11.2 rows=65535 loops=1)
|
+-------------------------------------------------------------------------------------------------------------+
1 row in set (0.09 sec)
mysql> \. sql_mysql_65534.sql
+-------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+-------------------------------------------------------------------------------------------------------------+
| -> Table scan on t1 (cost=6553..7375 rows=65534) (actual time=13.4..18.5 rows=65534 loops=1)
-> Materialize (cost=6553..6553 rows=65534) (actual time=13.4..13.4 rows=65534 loops=1)
-> Rows fetched before execution (cost=0..0 rows=65534) (actual time=452e-6..9.02 rows=65534 loops=1)
|
+-------------------------------------------------------------------------------------------------------------+
1 row in set (0.08 sec)
ということで、今年のアドベントカレンダーネタ、TVCの癖! はここまで。
あす、13番目の窓は、それぞれ。
Kenji Hirano さんのターン / JPOUG Advent Calendar 2025
yuya_yoshida_forcia さんのターン / PostgreSQL Advent Calendar 2025
mita2 さんのターン / MySQL Advent Calendar 2025
です。おたのしみに〜。
私のターンdone. では、また。
Enjoy SQLs and SQLの癖!
テスト用SQL生成スクリプト（Oracle Database 23ai)
このスクリプトでMySQL、PostgreSQL、Oracle Databaseそれぞれのテストスクリプトを出力する無名PL/SQLブロック
Oracle向けtvc確認SELECT文生成(65534行を生成する例)
e.g.
SQL> @make_tvc_sql0 65534 oracle
SQL> @sql_oracle_65534
make_tvc_sql0.sql
set feed off
set timi off
set head off
set termout off
set veri off
set trimspool on
set linesize 400
set pagesize 1000
SET SERVEROUTPUT ON
spool sql_&2._&1..sql
DECLARE
c_max_rows CONSTANT NUMBER := &1;
c_rvc_text_mysql CONSTANT CHAR(3) := 'ROW';
c_type_mysql CONSTANT CHAR(5) := 'MYSQL';
c_type CONSTANT VARCHAR2(10) := UPPER('&2');
BEGIN
DBMS_OUTPUT.PUT_LINE('SELECT * FROM ( VALUES');
FOR i IN 1..c_max_rows LOOP
DBMS_OUTPUT.PUT_LINE(
CASE WHEN i > 1 THEN ',' END
|| CASE WHEN c_type = c_type_mysql THEN c_rvc_text_mysql END
|| '(' || TO_CHAR(i)
|| ')'
);
END LOOP;
DBMS_OUTPUT.PUT_LINE(') t1 ( id );');
END;
/
spool off
SET SERVEROUTPUT OFF
UNDEFINE 1
UNDEFINE 2
set head on
set termout on
set feed on
set veri on
set timi on
set trimspool off
関連エントリー
・標準はあるにはあるが癖の多い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 にも癖が出る
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #19 - c_alias の癖（おまけ）
| 固定リンク | 0
コメント