oracle12c approx_count_distinct

此函数提供一个近似值，优点是快速，其他的函数类似：

• APPROX_COUNT_DISTINCT_AGG
• APPROX_COUNT_DISTINCT_DETAIL
• APPROX_MEDIAN
• APPROX_PERCENTILE
• APPROX_PERCENTILE_AGG
• APPROX_PERCENTILE_DETAIL
• TO_APPROX_COUNT_DISTINCT
• TO_APPROX_PERCENTILE

SQL> alter session set container=pdb1;

会话已更改。

SQL> create table t1 as select level as data from dual connect by level<=10000;

表已创建。

SQL> insert /*+ APPEND */ into t1 select a.data from t1 a cross join t1 b;

已创建 100000000 行。

SQL> commit;

提交完成。

SQL> exec dbms_stats.gather_table_stats(user,'T1');

PL/SQL 过程已成功完成。

SQL> set timing on
SQL> select ocunt(distinct data) as data_c from t1;
select ocunt(distinct data) as data_c from t1
       *
第 1 行出现错误:
ORA-00904: "OCUNT": 标识符无效


已用时间:  00: 00: 00.00
SQL> select count(distinct data) as data_c from t1;

    DATA_C
----------
     10000

已用时间:  00: 00: 09.40
SQL> select approx_count_distinct(data) as data_c from t1;

    DATA_C
----------
     10050

已用时间:  00: 00: 04.57