Aggregation Filter を試す（Oracle AI Database 26ai）

Aggregation Filter とは？

本記事は Oracle AI Database 26ai の新機能である Aggregation Filter 構文について解説しています。Aggregation Filter は SUM や COUNT 等でおなじみの集計関数と Window 関数に FILTER 句を指定する構文です。

Aggregation Filter の例

SQL> SELECT SUM(col1) FILTER (WHERE col1 < 100) FROM data1;

SUM(COL1)FILTER(WHERECOL1<100)
------------------------------
                          4950

FILTER 句には WHERE 句を記述し、集計関数に作用する条件を記述することができます。

従来の記述との違い

FILTER 句の利点は SQL 文をシンプルに記述できる点です。従来は CASE 句を使った冗長な SQL 文が必要であり、FILTER 句は直感的でわかりやすい記述になります。

従来の記述

CASE 句

SQL> SELECT 
        COUNT(CASE MOD(col1, 2) WHEN 0 THEN 1 ELSE NULL END) AS even, 
        COUNT(CASE MOD(col1, 2) WHEN 1 THEN 1 ELSE NULL END) AS odd 
    FROM data1;

      EVEN        ODD
---------- ----------
    500000     500000

FILTER 句の記述

FILTER 句

SQL> SELECT 
        COUNT(*) FILTER (WHERE MOD(col1, 2) = 0) AS even,
        COUNT(*) FILTER (WHERE MOD(col1, 2) = 1) AS odd 
    FROM data1;

      EVEN        ODD
---------- ----------
    500000     500000

実行計画を確認

CASE 句の記述と、FILTER 句の記述で実行計画を比較しました。実行計画には変化は見られず単純に記述の差異だけであると思われます。

CASE 句の実行計画

SQL> SET AUTOTRACE ON
SQL> SELECT 
        COUNT(CASE MOD(col1, 2) WHEN 0 THEN 1 ELSE NULL END) AS even, 
        COUNT(CASE MOD(col1, 2) WHEN 1 THEN 1 ELSE NULL END) AS odd 
    FROM data1;

      EVEN        ODD
---------- ----------
    500000     500000

Execution Plan
----------------------------------------------------------
Plan hash value: 265469878

-------------------------------------------------------------------------------------
| Id  | Operation             | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |             |     1 |     5 |   548   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE       |             |     1 |     5 |            |          |
|   2 |   INDEX FAST FULL SCAN| SYS_C008556 |  1000K|  4882K|   548   (1)| 00:00:01 |
-------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1936  consistent gets
          0  physical reads
          0  redo size
        676  bytes sent via SQL*Net to client
        108  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

FILTER 句の実行計画

SQL> SELECT 
        COUNT(*) FILTER (WHERE MOD(col1, 2) = 0) AS even,
        COUNT(*) FILTER (WHERE MOD(col1, 2) = 1) AS odd 
    FROM data1;

      EVEN        ODD
---------- ----------
    500000     500000

Execution Plan
----------------------------------------------------------
Plan hash value: 265469878

-------------------------------------------------------------------------------------
| Id  | Operation             | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |             |     1 |     5 |   548   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE       |             |     1 |     5 |            |          |
|   2 |   INDEX FAST FULL SCAN| SYS_C008556 |  1000K|  4882K|   548   (1)| 00:00:01 |
-------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       1937  consistent gets
          0  physical reads
          0  redo size
        676  bytes sent via SQL*Net to client
        108  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

内部的な動作

トレースを取得すると、内部的には Query Transformation 機能を使って SQL 文の書き換えを行っていることが分かります。

トレース・ファイルの確認

SQL> SELECT value FROM v$diag_info WHERE name = 'Default Trace File';

VALUE
--------------------------------------------------------------------
/opt/oracle/diag/rdbms/orclcdb/ORCLCDB/trace/ORCLCDB_ora_23532.trc

FILTER 句を使った SQL 文のトレースを取得します。

トレース取得

SQL> ALTER SESSION SET EVENTS '10053 trace name context forever';

Session altered.

SQL> SELECT
        COUNT(*) FILTER (WHERE MOD(col1, 2) = 0) AS even,
        COUNT(*) FILTER (WHERE MOD(col1, 2) = 1) AS odd
    FROM data1;

      EVEN        ODD
---------- ----------
    500000     500000

SQL> ALTER SESSION SET EVENTS '10053 trace name context off';

Session altered.

トレースファイルを確認すると、CASE 句に書き換えられていることを確認できます。

Final Query

Final query after transformations: qb SEL$1 (#0):******* UNPARSED QUERY IS *******
SELECT COUNT(CASE MOD("DATA1"."COL1",2) WHEN 0 THEN 1 ELSE NULL END ) "EVEN",COUNT(CASE MOD("DATA1"."COL1",2) WHEN 1 THEN 1 ELSE NULL END ) "ODD" FROM "SCOTT"."DATA1" "DATA1"
kkoqbc: optimizing query block SEL$1 (#0)

Author: Noriyoshi Shinoda / Date: August 4, 2026