近似値を返すための初期化パラメーターを試す (Oracle Database 12c Release 2新機能)

Oracle Database 12c Release 2で追加された近似値計算に関する新機能の説明です。

初期化パラメーターの追加

近似値計算に関係する以下の３つの初期化パラメーターが追加されました。

1. APPROX_FOR_AGGREGATION このパラメーターをTrueに設定すると、COUNT (DISTINCT expr) 関数の実行が近似値になります。ただしどの集計関数に対して近似値が利用されるのかについてはマニュアルに記述がありません。
2. APPROX_FOR_COUNT_DISTINCT このパラメーターをTrueに設定すると、COUNT (DISTINCT expr) 関数の実行が近似値になります。
3. APPROX_FOR_PERCENTILE このパラメーターにNONE以外の値に設定すると、PERCENTILE_* 関数の実行が近似値になります。

従来のバージョンでは近似値を計算するためにはサンプリングを行う必要がありました（FROM table SAMPLE句）。しかし、SAMPLE句を利用するためにはSQL文を修正する必要があります。この機能を使うことでSQL文はそのままで、自動的に近似値を計算してくれるようになります。

実行計画を確認

パラメーターの変更による実行計画の変化を確認します。まず、デフォルト状態で確認します。

SQL> ALTER SESSION SET APPROX_FOR_COUNT_DISTINCT = false ;

Session altered.

SQL> SELECT COUNT(DISTINCT L_SUPPKEY) FROM lineitem ;

COUNT(DISTINCTL_SUPPKEY)
------------------------
                   10000

Elapsed: 00:00:02.93
SQL>
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor()) ;

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  2kgsfb390q63y, child number 1
-------------------------------------
SELECT COUNT(DISTINCT L_SUPPKEY) FROM lineitem

Plan hash value: 1987383118
--------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time
|   0 | SELECT STATEMENT     |          |       |       | 29667 (100)|
|   1 |  SORT AGGREGATE      |          |     1 |    13 |            |
|   2 |   VIEW               | VW_DAG_0 | 10000 |   126K| 29667   (1)| 00:00:02
|   3 |    HASH GROUP BY     |          | 10000 | 40000 | 29667   (1)| 00:00:02
|   4 |     TABLE ACCESS FULL| LINEITEM |  6001K|    22M| 29512   (1)| 00:00:02

次に初期化パラメーターAPPROX_FOR_COUNT_DISTINCTを実行します。

SQL> ALTER SESSION SET APPROX_FOR_COUNT_DISTINCT = true ;

Session altered.

SQL> SELECT COUNT(DISTINCT L_SUPPKEY) FROM lineitem ;

COUNT(DISTINCTL_SUPPKEY)
------------------------
                   10050

Elapsed: 00:00:02.86
SQL>
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor()) ;

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  2kgsfb390q63y, child number 0
-------------------------------------
SELECT COUNT(DISTINCT L_SUPPKEY) FROM lineitem

Plan hash value: 2287326370
--------------------------------------------------------------------------------
| Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time
|   0 | SELECT STATEMENT      |          |       |       | 29512 (100)|
|   1 |  SORT AGGREGATE APPROX|          |     1 |     4 |            |
|   2 |   TABLE ACCESS FULL   | LINEITEM |  6001K|    22M| 29512   (1)| 00:00:02

実行しているSQL文はそのままですが、実行計画がSORT AGGREGATE APPROXに変化していることがわかります。