SAP SQL Anywhere で RowGenerator を使用する Part 2 （過去のブログより）

このページは、以下の英語ページの抄訳です。最新の情報については、英語ページを参照してください。
https://blogs.sap.com/2014/07/16/using-rowgenerator-part-deux/

この記事のオリジナルは、Glenn Paulley が sybase.com に 2009 年 10 月に掲載したものです。その中で、Glenn は 標準 SQL を使用して、SQL Anywhere の RowGenerator テーブルと sa_rowgenerator(…) 関数の動作をシミュレーションする方法について解説しています。

RowGenerator システムテーブルの使用に関するブログを掲載した後、私は IBM の Jan-Eike Michels 氏からありがたい e-mail を受け取りました。彼は私と同様 IBM の標準 SQL 策定の代表として INCITS の DM32.2 委員会に出席している人です。

Hi Glenn,

たまたま、あなたの RowGenerator のブログを読みました。SQL Anywhere でWITH 句をサポートしているのかは知りませんが、（標準SQLではサポートしているので）、それを使うことはできないのでしょうか ( sa_rowgenerator プロシージャーの代用として)？

彼が送って来たコードは以下のようなものでした。

with dummy (counter) as
    (select counter from table(values (1)) as x(counter) union all
    select counter + 1 from dummy where counter < 1000 ) select counter from dummy



would return 1000 rows.

私は、Jan-Eike 氏の意見を歓迎します。なぜならば、彼の指摘の通り、標準 SQL の共通テーブル表現構文を使用して識別子のセットを再帰的に生成した方が直感的です。この場合は、再起 UNION 構造を使用することになります。

SQL Anywhere でも Jan-Eike 氏の例をほぼそのまま使用することができますが、Jan-Eike 氏の SQL クエリの問題は

• SQL Anywhere では、再起クエリを特定する場合にはRECURSIVE キーワードを含める必要があります。
• SQL Anywhere サーバーは、TABLE キーワードを認識しません。
• • SQL Anywhere には、すでに (リアルな) テーブル、つまり単一のローと単一のカラムの結果セットを生成するダミーが含まれています。

そのため、SQL Anywhere で Jan-Eike 氏の例である 1 と 10 の間の値を生成するには、以下のようになります。

WITH RECURSIVE foo(counter) AS
  ( SELECT 1 FROM DUMMY
    UNION ALL
  SELECT counter + 1 FROM foo WHERE counter < 10 )
SELECT * FROM foo

これで、共通のテーブル表現 “foo” (“ダミー”ではなく) を定義し、特定の値を生成します。このクエリのグラフィカルなプランは、以下のようになります。

いくつか述べておくべき点を挙げると

• より大きい数の値を指定する – そして、それゆえにより深い再帰が発生します – この場合には MAX_RECURSIVE_ITERATIONS 接続オプションを設定する必要がある可能性があります。
• Jan-Eike 氏の例では、シーケンシャルな値のセットを生成します。これは、RowGenerator システムテーブルまたは sa_rowgenerator() システムプロシージャーが生成するものと同等です。しかしながら、共通テーブル表現内のSELECT リスト表現をリライトしてシンプル化し、このクエリを修正して、シーケンシャルではないあらゆる値の並びを生成することもできます。
• 最後に、この再帰バージョンは有用ですが、RowGenerator システムテーブルの方がより良いアプローチであると考えます。RowGenerator の利点は、これが (静的) ベースのテーブルであるため、共通テーブル表現が使用されている場合よりも複雑なクエリ内で RowGenerator が使用されている場合でも、クエリオプティマイザーが、カーディナリティの中間結果をよりうまく評価することができるからです。