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.
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
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
as the number of distinct values increase, the elapsed time
and memory usage of the count distinct increases
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
The approx_count_distinct can also be used to quickly
count the number of tables.
system set approx_for_count_distinct = TRUE;
Oracle Training from Don Burleson
The best on site
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
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
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
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
and include the URL for the page.
Copyright © 1996 - 2020
All rights reserved by
is the registered trademark of Oracle Corporation.