Oracle 12c新特性之：APPROX_COUNT_DISTINCT 函式

在Oracle 11g中，已經新增APPROX_COUNT_DISTINCT函式，但相關文件中沒有體現，用於提高使用DBMS_STATS包收集統計資訊時計算不同值（NDV）數量的速度。 Oracle資料庫12c（12.1.0.2）中，文件中已經包含了此函式，因此我們可以在應用程式中將其作為支援的SQL函式使用。

基本用法

在以前的資料庫版本中，如果我們想要執行不同值的計數，我們可能會做如下。

SELECT COUNT(DISTINCT object_name) AS obj_count
FROM   all_objects;

 OBJ_COUNT
----------
     47171

1 row selected.

SQL>

此查詢為我們提供了基於Oracle的讀一致性模型的不同值的確切計數。 因此，我們可以看到所有提交的資料，以及當前會話提交的任何未提交的更改。

相比之下，新的APPROX_COUNT_DISTINCT函式不提供準確的結果，但應該給出“可以忽略不計的精確結果”。

SELECT APPROX_COUNT_DISTINCT(object_name) AS obj_count
FROM   all_objects;

 OBJ_COUNT
----------
     46788

1 row selected.

SQL>

該函式也可以用作組查詢的一部分。

SELECT tablespace_name, APPROX_COUNT_DISTINCT(table_name) AS tab_count
FROM   all_tables
GROUP BY tablespace_name
ORDER BY tablespace_name;


TABLESPACE_NAME                 TAB_COUNT
------------------------------ ----------
SYSAUX                                 73
SYSTEM                                 36
USERS                                   7
                                       44

4 rows selected.

SQL>

效能

即使在下面的簡單示例中，我們可以看到兩種方法的速度的可重複的差異，但是效能差異似乎不太引人注目。

SET TIMING ON

SELECT COUNT(DISTINCT object_name) AS obj_count
FROM   all_objects;

 OBJ_COUNT
----------
     47171

1 row selected.

Elapsed: 00:00:02.39
SQL>


SELECT APPROX_COUNT_DISTINCT(object_name) AS obj_count
FROM   all_objects;

 OBJ_COUNT
----------
     46788

1 row selected.

Elapsed: 00:00:02.00
SQL>

實際上，APPROX_COUNT_DISTINCT函式旨在處理比這更大的工作負載，因此我們可以建立一個更大的表來測試。

DROP TABLE t1 PURGE;

CREATE TABLE t1 AS
SELECT level AS  data
FROM   dual
CONNECT BY level <= 10000;

INSERT /*+ APPEND */ INTO t1
SELECT a.data FROM t1 a
CROSS JOIN t1 b;

COMMIT;

EXEC DBMS_STATS.gather_table_stats(USER,'T1');

現在我們有一個表，有一個超過1億行和10,000不同的值。 現在我們可以看到兩種方法的效能差異更為顯著。

SET TIMING ON

SELECT COUNT(DISTINCT data) AS data_count
FROM   t1;

DATA_COUNT
----------
     10000

1 row selected.

Elapsed: 00:00:19.66
SQL>


SELECT APPROX_COUNT_DISTINCT(data) AS data_count
FROM   t1;

DATA_COUNT
----------
     10050

1 row selected.

Elapsed: 00:00:10.46
SQL>