oracle 12c month函数,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

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在数据量越来越大时，消耗的时间和资源基本不变。