長年、Oracle Databaseのパフォーマンス分野を代表に活躍されました津島博士は、2025年12月に惜しまれつつ退職されました。在職中に、津島博士に幻の第80回の原稿を預かり、公開の了承も得ておりますので、記事を投稿いたします。次回までごきげんよう、で締められておりますが、津島博士の記事は今回で最後となります。一部理解が難しいと感じた部分に補足説明をいれております。
皆さんこんにちは、図などが見づらくなっているところが多いためリニューアルすることになり、また新しい内容を載せていくことにしました。まずは、以前に書いて載せていなかった内容を、書き直して載せてみました。
今回は、Oracle Database 21c（Oracle21c）からのSQLファンクションの機能拡張について説明します。後半に、集合演算子の機能拡張とSQLマクロについても説明しますので、参考にしてください。
1. ファンクションの機能拡張
まずは、Oracle21c（正確には20cプレビュー版）から強化された以下のファンクションについて説明します。
・分析ファンクション
・ANY_VALUEファンクション
・COUNT DISTINCT操作ファンクション
（１）分析ファンクション
第56回の分析ファンクション（ウィンドウ関数）の続きとして、Orcale21cからの機能について説明します。以下の機能がOracle21cから強化されて、より使いやすくなりました（これで、ANSI準拠のWINDOW句が使用できるようになりました）。
・WINDOWING句のEXCLUDEオプション
・WINDOWING句にキーワードGROUPSの指定
・SELECT文のquery_blockにWINDOW句のサポート
(a)WINDOWING句のEXCLUDEオプション
分析ファンクションのWINDOWING句にANSI準拠のEXCLUDEオプションが指定できるようになりました。EXCLUDEは、含まない値を指定するオプションで、以下の指定が可能になります。
・EXCLUDE CURRENT ROW（行グループの現在行を除外する）
現在の行がウィンドウ・フレームから削除されます。
・EXCLUDE TIES（行グループの重複値を除外する）
現在の行のピア（同一データ）がウィンドウ・フレームから削除されます。
・EXCLUDE GROUP（EXCLUDE CURRENT ROW + EXCLUDE TIES）
現在の行とその行のピア（同一データ）がウィンドウ・フレームから削除されます。
・EXCLUDE NO OTHERS（デフォルト）
ウィンドウ・フレームから行は除外されません。
これも第47回で実行したデータに対して以下のSQLを使用して説明します。
例えば、CURRENT ROWは、現在行と平均値を比較する場合に、平均値に現在行（自身の値）を含めたくない場合などに使用できます。
(b)WINDOWING句のキーワードGROUPSの指定
Oracle21cからWINDOWING句にGROUPSも指定できるようになりました。キーワードGROUPSは、ROWSとRANGEの両方の特性を持ちます（つまり、ROWSと同じで、かつ重複値は同じ行として扱います）。
違いが分かりやすいように、第47回で実行した以下のSQLを使用して説明します。重複値は同じ行として扱い、値が飛んでいても次のデータとして扱います（c3=104がそうです）。
(c)SELECT文のquery_blockにWINDOW句のサポート
Oracle21cからのSELECT文のquery_blockに、WINDOW句をサポートするようになりました（これにより、ANSI準拠のWINDOW句が実装されました）。以下のように問合せブロックの最後(ORDER BY句の前)にWINDOW句を指定できるようになり、同じWINDOW句を共通化できるようになります。
これで分析ファンクションが「OVER <WINDOW名>」だけの指定になるので、ウィンドウ関数を使用したSQLが簡略化できるようになります。以下がWINDOW句を指定したサンプルSQLになります。
（２）ANY_VALUEファンクション
次に、Oracle21cからのANY_VALUEファンクションについて説明します（Oracle21cから新機能に追加されましたが、Oracle19cでも使用できます）。
ANY_VALUEファンクションは、GROUP BY句を持つ問合せに対してグループ内で最初の値を返すように最適化されます。例えば、以下のようにcust_idとcust_last_nameをGROUP BY句に指定する場合です（cust_last_nameは、GROUP BY句に指定する必要はありませんが、出力したいために指定しています）。
以下のようにMINやMAXを使用することもできます。
このような場合、以下のようにANY_VALUEを使用することで、最初の値を返すようにして値を比較しないため、GROUP BY問合せのMINやMAXよりも迅速に値を返します（件数が多ければ多いほど効果が高いです）。
参考のため、簡単なSQL（4000万件のデータを100件に集約する）で、それぞれの実行時間を載せておきます。
（３）COUNT DISTINCT操作ファンクション
最後に、COUNT DISTINCT操作を高速化できる以下のファンクションについて説明します。
この機能は、Oracle21cからではありませんが、まだ扱っていませんでしたので、ここで説明したいと思います。
・ビットマップ・ベースのCOUNTファンクション（Oracle19cから）
・APPROX_COUNT_DISTINCTファンクション（Oracle12.2から）
(a)ビットマップ・ベースのCOUNTファンクション
※ビットマップ・ベースのCOUNTファンクションについて補足説明を津島博士の記事の後に追記しております
まずは、ビットマップ・ベースのCOUNTファンクションについて説明します。
COUNT DISTINCT操作のパフォーマンスは、マテリアライズド・ビュー（MView）で改善しようとすると、ロールアップ（上位レベルのグルーピング）ができないので、集約レベルで多くのMViewを作成する必要があります。そのため、Oracle19cから、以下の5つの新しいファンクションでビットマップを使用してロールアップできるようになりました（ビットマップ上のビットで数値1,2,3,…を先頭から順番に管理することでサイズを削減します）。
・BITMAP_BUCKET_NUMBER
数値が属しているビットマップのバケット番号を返します。ビットマップには16,000個の数字が入るので（MAX_STRING_SIZE=EXTENDEDにすると32,760個）、以下のように値が16,001でバケット番号が2になります。
・BITMAP_BIT_POSITION
数値に対するビットマップ内のビット位置を返します（入力パラメータはNUMBER型でなければなりません）。
・BITMAP_CONSTRUCT_AGG
BITMAP_BIT_POSITIONのビット位置を入力として、値に対応する位置のビットがビットマップ配列に設定する集計ファンクションです（これで各値がビット管理されます）
・BITMAP_OR_AGGBITMAP_CONSTRUCT_AGG
結果を入力として、複数のビットマップ配列を集約するので、ロールアップするときに使用します。
・BITMAP_COUNT
BITMAP_CONSTRUCT_AGGまたはBITMAP_OR_AGG結果を入力として、ビットマップ配列で”1″に設定されているビットを数えます（一意な値を数えます）。
COUNT DISTINCT操作を、以下のようにビットマップ・ファンクションを使用して行うことができます。
一意な数字が多いとビットマップをオーバーするので、以下のようにBITMAP_BUCKET_NUMBERでGROUP BYしてからSUM関数を実行します。数字の範囲が大きいとビットマップのメモリ使用量が多くなるので注意が必定です（1から1,000より1,000から2,000の方がビットマップを多く使用します）。
以下のようなMViewを使用することで、クエリ・リライトさせることができます（BITMAP_BUCKET_NUMBERファンクションをグルーピング列として使用する必要があります）。
内部的に以下のSQLが実行されます（ロールアップしているのでBITMAP_OR_AGGファンクションを使用します）。
(b)APPROX_COUNT_DISTINCTファンクション
次に、APPROX_COUNT_DISTINCTファンクションについて説明します。
COUNT DISTINCT操作を高速に行うには、Oracle12.2からのAPPROX_COUNT_DISTINCTファンクションもありますが、5%程度の誤差があるので、それが許容できる方はこちらの使用を検討してください。初期化パラメータAPPROX_FOR_COUNT_DISTINCT=TRUE(デファルトFALSE)を設定するとCOUNT (DISTINCT expr)問合せを自動的にAPPROX_COUNT_DISTINCT問合せに置き換えることが可能になります。APPROX_FOR_PERCENT=ALL(デフォルトNONE)でPERCENTILE_CONTとPERCENTILE_DISCを自動的にAPPROX_PERCENTILEに置き換えます（APPROX_FOR_AGGREGATION=TRUEはAPPROX_FOR_COUNT_DISTINCT=TRUEとAPPROX_FOR_PERCENT=ALLと同じです）。
参考のため、COUNT(DISTINCT)とAPPROX_COUNT_DISTINCTの実行結果と実行時間を載せておきます。
 |  |
2. その他のSQL強化
ここでは、その他でOracle21c（正確には20cプレビュー版）から強化された以下の機能について説明します。
・集合演算子の機能拡張
・SQLマクロ
（１）集合演算子の機能拡張
第59回の続きとして、集合演算子のOracle21cから拡張された機能について説明します。
以下が指定ができるようになりました（EXCEPTはMINUSと同じ意味でANSI-SQL92準拠の構文です）。
・MINUS ALL
・INTERSECT ALL
・EXCEPT [ALL]
これまで指定できなかったALL指定が、UNION以外でもできるようになりました。これで必要ない重複値排除（SORT UNIQUEやHASH UNIQUE）を実行する必要がなくなります。以下がそれぞれの実行計画になります（EXCEPTはMINUSになっています）。MINUSやINTERSECTの実行計画も改善されています（第59回のようなSORT UNIQUEがなくなっています）。
 |  |
（２）SQLマクロ
最後に、Oracle19cから追加されOracle21cで強化されたSQLマクロについて説明します。
SQLマクロは、通常の関数とは異なり、結果は完全なクエリ・テキストを返します。Oracle19cから表用SQLマクロ（FROM句で使用）が追加され、Oracle21cでさらにスカラー式用SQLマクロ（SELECTリスト、WHERE句、HAVING句で使用）が強化されています。
通常のユーザー定義ファンクションとは異なり、SQLを実行するファンクションである必要があります（WITH句のINLINEヒント指定を事前登録する機能のようなものです）。SQLを簡略化して、ストアード・ファンクションを呼び出すオーバーヘッドを削減する機能になります（以下の例は、表用SQLマクロになります）。
DBMS_UTILITY.expand_sql_textプロシージャは、ビューを使用したSQLなどを展開するOracle12cからの機能です。これをSQLマクロの展開にも利用できるので、以下のように行うことでSQLマクロのデバックが可能です。
3. おわりに
今回は、Oracle Database 21cのSQL強化について説明しましたが、少しは参考になりましたでしょうか。機会があれば、これからも新しい内容を載せるようにしたいと思います。
それでは、次回まで、ごきげんよう。
（※）補足説明：ビットマップ・ベースのCOUNTファンクション
まず、なぜ通常の COUNT(DISTINCT) はロールアップできないのか、ですが、以下例をあげます。
たとえば、売上表にこんなデータがあるとします。
4月1日: 顧客ID 1, 顧客ID 2
4月2日: 顧客ID 2, 顧客ID 3
日別の COUNT(DISTINCT customer_id) は、
4月1日 = 2
4月2日 = 2
です。
このように、日別のCOUNT(DISTINCT)のMViewに保存されているのが 「2」と「2」だけだと、4月の月別のユニーク顧客数は、足しても 4 になってしまい、正しく求められません。正しくは、顧客ID 1,2,3 の3人 です。
通常の COUNT(DISTINCT) は、件数のみの情報となり、誰を数えたのかという情報が失われるので、月毎のような上位集計へ単純に足し上げができません。これがロールアップができない、ということになります。
このため、日毎、月毎と欲しい場合は、集計粒度ごとに別々のMViewを作ることが必要になっていました。これを解決するのがビットマップ方式です。
ビットマップ方式では、どの顧客が含まれていたか をビットで持ちます。
先ほどの売上表の顧客ID 1,2,3の例では、
顧客ID1 -> 1番目のビット
顧客ID2-> 2番目のビット
顧客ID3 -> 3番目のビット
すると各日の集合はこう表せます。
顧客ID1 2 3
-----
4/1: 1 1 0 = 110 (顧客ID 1,2)
4/2: 0 1 1 = 011 (顧客ID 2,3)これを月単位での集計にするには、ビットのOR計算をします。
110 + 011 = 111
これは、以下のようになります。ビットの数を数えれば、合計3本で正しい結果となります。
顧客ID1 2 3
-----
4月: 1 1 1 = 111 (顧客ID 1,2,3)このようにして日毎のビットで表したデータをOR計算することで、月単位のCOUNT(DISTINCT)のロールアップ集計が得られます。
また、各顧客がビットの何番目か対応づけられるように、ビットマップ対応の本機能は、顧客IDのようにNUMBER型である必要があります。
以上の知識をもとに、本編の(a)ビットマップ・ベースのCOUNTファンクション を読んでいただければ知識が深まるかと思います。