近似値を返す関数

Oracle Database 18cには集計時に近似値を返す関数が提供されました。おおよその値を使用することで計算速度を向上させることを目的としています。
本記事では APPROX_COUNT_DISTINCTを試します。APPROX_COUNT_DISTINCT関数は、COUNT(DISTINCT expr) と同じ意味で、入力値の一意な個数の概算値を返します。

例として100万レコードが格納され、c1列の一意な数が50万のテーブル data1 に対して実行してみます。

SQL> SELECT COUNT(DISTINCT c1) FROM data1; 

COUNT(DISTINCTC1)
-----------------
           500000

SQL> SELECT APPROX_COUNT_DISTINCT(c1) FROM data1;

APPROX_COUNT_DISTINCT(C1)
-------------------------
                   479121

実際よりもやや少ない値が返りました。

実行計画を見てみる

実行計画を確認してみます。通常の COUNT(DISTINCT) を実行すると、SORT AGGREGATE / VIEW / HASH GROUP BY などのオペレーションが行われていることがわかります。また WHERE 句を指定していないので、テーブル全体が読み込まれています（TABLE ACCESS FULL)。

SQL> SELECT /*+ gather_plan_statistics */ COUNT(DISTINCT c1) FROM data1;

COUNT(DISTINCTC1)
-----------------
           500000

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(format=> 'all allstats last'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  cuzvwr210a31m, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ COUNT(DISTINCT c1) FROM data1

Plan hash value: 2027879751

--------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name     | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |      1 |        |       |       |  2926 (100)|          |      1 |00:00:00.07 |    5108 |       |       |          |
|   1 |  SORT AGGREGATE      |          |      1 |      1 |    13 |       |            |          |      1 |00:00:00.07 |    5108 |       |       |          |
|   2 |   VIEW               | VW_DAG_0 |      1 |    500K|  6347K|       |  2926   (1)| 00:00:01 |    500K|00:00:00.07 |    5108 |       |       |          |
|   3 |    HASH GROUP BY     |          |      1 |    500K|  2441K|  5896K|  2926   (1)| 00:00:01 |    500K|00:00:00.07 |    5108 |    23M|  5498K|   24M (0)|
|   4 |     TABLE ACCESS FULL| DATA1    |      1 |    500K|  2441K|       |  1402   (1)| 00:00:01 |    500K|00:00:00.01 |    5108 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$C33C846D
   2 - SEL$5771D262 / VW_DAG_0@SEL$C33C846D
   3 - SEL$5771D262
   4 - SEL$5771D262 / DATA1@SEL$1

Column Projection Information (identified by operation id):
-----------------------------------------------------------
   1 - (#keys=0) COUNT("ITEM_1")[22]
   2 - (rowset=256) "ITEM_1"[NUMBER,22]
   3 - (rowset=256) "C1"[NUMBER,22]
   4 - (rowset=256) "C1"[NUMBER,22]


32 rows selected.

次に APPROX_COUNT_DISTINCT 関数の実行計画を確認します。HASH GROUP BY などが無く、SORT AGGREGATE APPROX のみになっています。
WHERE 句が無いのは同じなので、TABLE ACCESS FULL の部分は変わりません。
出力されるデータから Buffers 部分がどちらも同じ（5108）なので、バッファ・キャッシュに読み込む量は変化が無いことがわかります。
このことから、近似値を返す関数はI/O量は削減できず、ソート処理や計算部分を簡略化することで高速化していることが予想されます。

SQL> SELECT /*+ gather_plan_statistics */ APPROX_COUNT_DISTINCT(c1) FROM data1;

APPROX_COUNT_DISTINCT(C1)
-------------------------
                   479121

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(format=> 'all allstats last'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  4prnb22pwmsw1, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ APPROX_COUNT_DISTINCT(c1) FROM data1

Plan hash value: 1629554946

-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name  | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |       |      1 |        |       |  1402 (100)|          |      1 |00:00:00.04 |    5108 |
|   1 |  SORT AGGREGATE APPROX|       |      1 |      1 |     5 |            |          |      1 |00:00:00.04 |    5108 |
|   2 |   TABLE ACCESS FULL   | DATA1 |      1 |    500K|  2441K|  1402   (1)| 00:00:01 |    500K|00:00:00.01 |    5108 |
-------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - SEL$1 / DATA1@SEL$1

Column Projection Information (identified by operation id):
   1 - (#keys=0) APPROX_COUNT_DISTINCT("C1")[22]
   2 - (rowset=256) "C1"[NUMBER,22]

27 rows selected.

中央値

COUNT(DISTINCT) 以外に中央値を計算する MEDIAN 関数の概算も試してみました。

SQL> SELECT MEDIAN(c1) FROM data1;

MEDIAN(C1)
----------
    500000

SQL> SELECT APPROX_MEDIAN(c1) FROM data1;

APPROX_MEDIAN(C1)
-----------------
           498745

APPROX_MEDIAN 関数にはいくつか追加パラメーターが提供されています。DETERMINISTIC を指定すると、近似値計算に「決定的アルゴリズム」を使用します。デフォルトでは「非決定的アルゴリズム」が使用されます。

SQL> SELECT APPROX_MEDIAN(c1 DETERMINISTIC) FROM data1;

APPROX_MEDIAN(C1DETERMINISTIC)
------------------------------
                        502443

中央値ではなく、近似値計算のエラー率を返す場合は、'ERROR_RATE' パラメーターを指定します。

SQL> SELECT APPROX_MEDIAN(c1, 'ERROR_RATE') FROM data1;

APPROX_MEDIAN(C1,'ERROR_RATE')
------------------------------
                           .02

エラー率の信頼水準を返す場合には 'CONFIDENCE' を指定します。

SQL> SELECT APPROX_MEDIAN(c1, 'CONFIDENCE') FROM data1;

APPROX_MEDIAN(C1,'CONFIDENCE')
------------------------------
                           .99

DETERMINISTIC や、'ERROR_RATE', 'CONFIDENCE' パラメーターは APPROX_PERCENTILE 関数にも使用できます。

近似値を計算する関数を試す（Oracle Database 18c)

近似値を返す関数

実行計画を見てみる

中央値