Question: What
does 12c approx_count_distinct do, and how do I use
approx_count_distinct to get better schema statistics?
Are there other uses for this new function?
Answer: The approx_count_distinct
is a 12c new feature to reduces the time required to get the
number of distinct values (NDV) for a table column.
This approx_count_distinct was added in 11g (but not
documented), as an aid in speeding-up dbms_stats collection
of column distinct values.
exec
dbms_stats.gather_table_stats(‘my_schema',’my_tab’);
Tests indicate the approx_count_distinct is more than
eight times faster than the traditional count distinct, and
the speed performances are even greater for massive data
warehouse tables.
Statistically, the approx_count_distinct approximations
provide a statistically insignificant difference from count
distinct, so the approximation is statistically valid.
In a traditional count distinct, Oracle's read
consistency mechanism is invoked, causing a large time lag
when counting the number of distinct values in a very large
table. Also,
as the number of distinct values increase, the elapsed time
and memory usage of the count distinct increases
drastically.
In contrast, the approx_count_distinct bypasses the read
consistency mechanism and give a fast and relatively
accurate approximation of the number of distinct values in a
table column.
The approx_count_distinct can also be used to quickly
count the number of tables.
alter
system set approx_for_count_distinct = TRUE;
select
tablespace_name,
APPROX_COUNT_DISTINCT(table_name)
as tab_count
from
dba_tables
group by
tablespace_name
order by
tablespace_name;
|
|
|
|
|
Oracle Training from Don Burleson
The best on site
"Oracle
training classes" are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop!
|
|
|
|
| |
|
Burleson is the American Team
Note:
This Oracle
documentation was created as a support and Oracle training reference for use by our
DBA performance tuning consulting professionals.
Feel free to ask questions on our
Oracle forum.
Verify
experience!
Anyone
considering using the services of an Oracle support expert should
independently investigate their credentials and experience, and not rely on
advertisements and self-proclaimed expertise. All legitimate Oracle experts
publish
their Oracle
qualifications.
Errata?
Oracle technology is changing and we
strive to update our BC Oracle support information. If you find an error
or have a suggestion for improving our content, we would appreciate your
feedback. Just
e-mail:
 and include the URL for the page.
Copyright © 1996 - 2020
All rights reserved by
Burleson
Oracle ®
is the registered trademark of Oracle Corporation.
|
|
