在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>