DUALだけで多行を生成するSQLクエリをいろいろ挙げてみる(Oracle)

はじめに

行をその場で生成するとき、少ない行数ならUNION、ある程度多くなると階層や再帰クエリを使用するのが一般的かとおもわれますが、それ以外にもいろいろと方法はあります。一部はあまり実用的ではありませんが、オラクルのクエリには行を作れる機能がこんなにあるんだよーという参考としてどうぞ。

ここでは例としてそれぞれ10行のみ生成しています。10行あればファクタリング(CTE)して直積すれば大きな行数にも対応できますからね。というか、下手に大きな行数を直接取り出すよりも直積のほうが明らかに早いです。なんといってもデータベースですから。

直積例

WITH t as (select level from dual connect by level <= 10)
SELECT COUNT(*) FROM t,t,t,t,t,t;

        COUNT(*)
----------------
      10,000,000

いろいろな行生成方法

それではいってみましょう。
以下、数値を指定して直接任意の行数を生成することができるのは、「階層」「モデル」「再帰」「XML」です。

UNION

一番ベーシックな方法です。

UNION

select rownum from (
  SELECT 1 FROM dual UNION ALL SELECT 1 FROM dual UNION ALL
  SELECT 1 FROM dual UNION ALL SELECT 1 FROM dual UNION ALL
  SELECT 1 FROM dual UNION ALL SELECT 1 FROM dual UNION ALL
  SELECT 1 FROM dual UNION ALL SELECT 1 FROM dual UNION ALL
  SELECT 1 FROM dual UNION ALL SELECT 1 FROM dual);

階層

階層クエリはもっともシンプルで一般的な方法です。様々な場面で使われています。LEVELで条件指定すれば必要な行数を簡単に取り出すことができます。しかし厳密には正しい用法ではないみたいです（マニュアルによると CONNECT BY句には PRIORが必須）。

階層

SELECT level FROM dual CONNECT BY level <= 10;

モデル (FOR)

モデルのDIMENSIONのFORループです。デフォルトはUPSERTなので存在しない行は作られます。

モデル

SELECT d FROM dual 
model dimension BY (1 d) measures (1 m) rules (m[FOR d FROM 1 TO 10 increment 1] = 1);

モデル (ITERATE)

モデルのITERATEはルール全体を繰返します。同じくUPSERTで行が作られます。

モデル

SELECT d FROM dual 
model dimension BY (1 d) measures (1 m) rules iterate (10) (m[iteration_number + 1] = 1);

再帰

再帰は２つのSELECTをUNION ALLでつないでループします。

再帰

WITH t(n) AS (SELECT 1 FROM dual UNION ALL SELECT n + 1 FROM t WHERE n < 10)
SELECT n FROM t;

UNPIVOT

かなり無理矢理感ありますが、UNPIVOTはカラムを行にを変換します。

UNPIVOT

SELECT n FROM  (SELECT 1 a,2 b,3 c,4 d,5 e,6 f,7 g,8 h,9 i,10 j FROM dual) 
unpivot (n FOR s IN (a,b,c,d,e,f,g,h,i,j));

グループ

CUBEでは引数の数だけ 2のn乗した数の行がつくられます。RollupやGrouping setsでも可能です。

グループ

SELECT rownum from (SELECT 1 FROM dual GROUP BY cube(1,1,1,1)) where rownum <= 10;

SELECT row_number() over (order by null) FROM dual GROUP BY rollup(1,1,1,1,1,1,1,1,1);

SELECT row_number() over (order by null) FROM dual GROUP BY grouping sets(1,1,1,1,1,1,1,1,1,1);

コレクション

パブリックコレクションを使います。有名なodcinumberlistはNUMBER型のVARRAYです。TABLE表現で行に変換します。DUAL使ってないけどね。

コレクション

SELECT * FROM TABLE(sys.odcinumberlist(1,2,3,4,5,6,7,8,9,10));

XML

なんというか、XML拡張はいろいろチートだよなぁ、、、。

SELECT * FROM XMLTABLE('1,2,3,4,5,6,7,8,9,10');

SELECT * FROM XMLTABLE('1 to 10');

SELECT * FROM XMLTABLE('for $i in 1 to 10 return $i');

SELECT rownum FROM XMLTABLE('/S/R' passing xmltype(rpad('<S>', 43, '<R/>')  || '</S>'));

おわりに

他にもありそうな気がしますが、、、いまのところ以上です。