Oracle12c中效能最佳化新特性之新增APPROX_COUNT_DISTINCT 快速唯一值計數函式

Oracle11g中，為了改善DBMS_STATS包收集統計資訊時的唯一值計數功能，增加了 APPROX_COUNT_DISTINCT函式，但文件中未記載。Oracle12c文件中包括了該函式，因此，我們現在可以在應用中隨意使用它。
1.    基本用法
先前的資料庫版本中，如果我們想進行唯一值計數，我們可能會這麼做。
SELECT COUNT(DISTINCT c_name) AS nm_cnt
FROM   test;
 
 NM_CNT
----------
     58172
 
1 row selected.
 
SQL>
該查詢會基於Oracle的讀一致模型得出精確的唯一值結果。即，我們會看到已提交的資料，及當前會話做的未提交修改。
相反，新函式APPROX_COUNT_DISTINCT不會給出精確結果，但會和精確結果有所偏差。
SELECT APPROX_COUNT_DISTINCT(c_name) AS nm_cnt
FROM   test;
 
 NM_CNT
----------
     56789
 
1 row selected.
 
SQL>
該函式能用於分組查詢中。
SELECT tablespace_name,APPROX_COUNT_DISTINCT(table_name) AS tab_count
FROM   user_tables
GROUP BY tablespace_name
ORDER BY tablespace_name;
 
TABLESPACE_NAME                 TAB_COUNT
------------------------------ ----------
SYSAUX                                 78
SYSTEM                                 22
USERS                                   7
                                       48
 
4 rows selected.
 
SQL>
2.    效能
下例中，我們會看到兩種方法效能的差別，但似乎不是特別大。
SET TIMING ON
 
SELECT COUNT(DISTINCT c_name) AS nm_cnt
FROM   test;
 
 NM_CNT
----------
     58172
 
1 row selected.
 
Elapsed: 00:00:02.39
SQL>
 
 
SELECT APPROX_COUNT_DISTINCT(c_name) AS nm_cnt
FROM   test;
 
 NM_CNT
----------
     56789
 
1 row selected.
 
Elapsed: 00:00:02.00
SQL>
事實上，APPROX_COUNT_DISTINCT函式被用來處理大得多的負載，下面，我們建立一個大得多的表。
DROP TABLE test PURGE;
 
CREATE TABLE test AS
SELECT level AS  data
FROM  dual
CONNECT BY level <= 10000;
 
INSERT /*+ APPEND */ INTO test
SELECT a.data FROM test a
CROSS JOIN test b;
 
COMMIT;
 
EXEC DBMS_STATS.gather_table_stats(‘Test’,'Test');
現在表中有100多萬資料，1萬個唯一值。我們會看到兩種方法的效能差別比較大。
SET TIMING ON
 
SELECT COUNT(DISTINCT data) AS data_count
FROM  test;
 
DATA_COUNT
----------
    10000
 
1 row selected.
 
Elapsed: 00:00:19.66
SQL>
 
 
SELECT APPROX_COUNT_DISTINCT(data) ASdata_count
FROM  test;
 
DATA_COUNT
----------
     10030
 
1 row selected.
 
Elapsed: 00:00:10.46
SQL>
透過測試會發現，之前的方法，當資料量越來越大時，消耗的時間和資源也會越來越大，而新函式APPROX_COUNT_DISTINCT在資料量越來越大時，消耗的時間和資源基本不變。