概要
SQL では SELECT した結果を INSERT できます（本記事では SELECT-INSERT と呼称します）。また、SELECT-INSERT の SELECT には ORDER BY を指定することもできます。リレーショナルモデルは集合を扱い、行の順序を意識しないはずです。なのになぜ SELECT-INSERT の SELECT で ORDER BY できるのか…という疑問と検証を書き留めています。
仮説
リレーショナル・データベースはリレーショナルモデルを基にしています。
リレーショナル・データベースの例として Oracle Database や PostgreSQL 等があります。
リレーショナルモデルは集合の概念と結びついています。集合の要素には順序付けがありません。数学の集合が {a,b,c} と {c,a,b} を同じものとして扱うことは、リレーショナルモデルにおいても当てはまります。
リレーショナルモデルが順序を意識しないことは ORDER BY を指定しない SELECT 結果の行の順序が不定であることにも表れています。PostgreSQL のマニュアルから該当する記載を引用します。
ある問い合わせが1つの出力テーブルを生成した後（選択リストの処理が完了した後）、並べ替えることができます。並べ替えが選ばれなかった場合、行は無規則な順序で返されます。そのような場合、実際の順序は、スキャンや結合計画の種類や、ディスク上に格納されている順序に依存します。しかし、当てにしてはいけません。明示的に並べ替え手続きを選択した場合にのみ、特定の出力順序は保証されます。
7.5. 行の並べ替え
リレーショナルモデルが順序を意識しないとすると、INSERT 時の行の順序も意味がないことになります（自動採番においては意味を持ちますが、ここでは考えないことにします）。とすれば、SELECT-INSERT の ORDER BY が意味を持たない場合はリレーショナル・データベースによって最適化され、クエリの実行時に無視されるのではないでしょうか？
検証
検証手段
「SELECT-INSERT の ORDER BY が意味を持たない場合はクエリの実行時に無視される」という仮説を検証します。
仮説を検証するため、Oracle Database 19.3 と PostgreSQL 14.1 において、実行計画を取得しました。クエリの文法として SELECT-INSERT の ORDER BY を記述できたとしても、それが意味を持たないのであればクエリの実行時に無視されるはずです。
tab1 の値を降順 (desc) でソートし、その結果を tab2 に INSERT する、という SELECT-INSERT で検証します。
Oracle Database 19.3
実行計画の取得方法については下記の記事を参考にしました。
Oracle SQL実行計画の読み方 | コーソルDatabaseエンジニアのBlog
実行したクエリは以下の内容です。
CREATE TABLE tab1(c1 INTEGER); CREATE TABLE tab2(c1 INTEGER); INSERT INTO tab1 SELECT rownum FROM (SELECT level FROM dual CONNECT BY level <= 1e+6); SET LINESIZE 200; SET PAGESIZE 0; SET SERVEROUTPUT OFF; ALTER SESSION SET statistics_level=ALL; INSERT INTO tab2(c1) SELECT c1 FROM tab1 ORDER BY c1 desc; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format=>'ALLSTATS LAST'));
得られた実行計画を示します。
---------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ---------------------------------------------------------------------------------------------------------------------- | 0 | INSERT STATEMENT | | 1 | | 0 |00:00:00.68 | 16307 | | | | | 1 | LOAD TABLE CONVENTIONAL | TAB2 | 1 | | 0 |00:00:00.68 | 16307 | | | | | 2 | SORT ORDER BY | | 1 | 1033K| 1000K|00:00:00.17 | 1522 | 24M| 1794K| 21M (0)| | 3 | TABLE ACCESS FULL | TAB1 | 1 | 1033K| 1000K|00:00:00.01 | 1522 | | | | ----------------------------------------------------------------------------------------------------------------------
プランに "SORT ORDER BY" が現れており、ORDER BY のソート処理に時間を費やしたことが読み取れます。
PostgreSQL 14.1
実行したクエリは以下の内容です。
CREATE TABLE tab1(c1 INTEGER); CREATE TABLE tab2(c1 INTEGER); INSERT INTO tab1 SELECT generate_series(1, 1e+6); EXPLAIN ANALYZE INSERT INTO tab2(c1) SELECT c1 FROM tab1 ORDER BY c1 desc;
得られた実行計画を示します。
Insert on tab2 (cost=144571.25..158675.93 rows=0 width=0) (actual time=1113.012..1113.014 rows=0 loops=1)
-> Sort (cost=144571.25..147392.18 rows=1128375 width=4) (actual time=222.429..325.495 rows=1000000 loops=1)
Sort Key: tab1.c1 DESC
Sort Method: external merge Disk: 13800kB
-> Seq Scan on tab1 (cost=0.00..15708.75 rows=1128375 width=4) (actual time=0.031..71.985 rows=1000000 loops=1)
Planning Time: 7.509 ms
Execution Time: 1113.883 msプランに "Sort" が現れており、ORDER BY のソート処理に時間を費やしたことが読み取れます。
考察
Oracle Database 19.3 と PostgreSQL 14.1 共に ORDER BY のソート処理で時間を費やしていました。よって「SELECT-INSERT の ORDER BY が意味を持たない場合はクエリの実行時に無視される」という仮説は否定されたことになります。
不要と思える ORDER BY のソート処理を実行する設計（実装）となっている理由は分かりませんでした。
データをデータベースとして組織化した目的は、アプリケーションプログラムをデータの諸元（特に、レコードの物理的格納順、インデックスの有無、データへのアクセスパスなど）から切り離し、それらの改変と無関係とする、即ちアプリケーションプログラムのデータ独立性を達成するためであった。
リレーショナルデータベース入門―データモデル・SQL・管理システム・NoSQL (Information & Computing)
6.4 3層スキーマの意義
この記述にある通り、データベースはアプリケーションプログラムに「レコードの物理的格納順」を意識させません。また、リレーショナルモデルとしても順序を意識しないことを考えると、INSERT の順序を決めるような ORDER BY は不要な処理として最適化されるのが妥当に思えるのですが…。
今後、新たな知見を得られた際に再度調査します。
