What Is APPROX_COUNT_DISTINCT Function

Today someone ask me about approx_count_distinct function, I haven’t work with this function but I heard from it. Reading the doc it says that estimate the number of differents values based on statistics, I think that statistics must to be accuraty for a good estimate. The other way and exact is ‘select count (distinct (colname)) from …’. A simple example for a simple table created as select from dba_objects views is like show below;

 

 
database_12c> create table anr123 as select * from dba_objects ;

Tabla creada.

REM
REM get the execution plan
REM 
database_12c> explain plan for
  2  select approx_count_distinct (object_type) from anr123;

Explicado.

REM
REM Take a look at the execution plan
REM 
database_12c> sta ver_plan

Plan de Ejecución
__________________________________________________________
Plan hash value: 2137789089

-----------------------------------------------------
| Id  | Operation                         | Name    |
-----------------------------------------------------
|   0 | SELECT STATEMENT                  |         |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| DISPLAY |
-----------------------------------------------------

REM
REM get the execution plan
REM 
database_12c> explain plan for
  2  select count (distinct (object_type)) from anr123;

Explicado.

REM
REM Take a look at the execution plan, it is the same
REM 
database_12c> sta ver_plan

Plan de Ejecución
__________________________________________________________
Plan hash value: 2137789089

-----------------------------------------------------
| Id  | Operation                         | Name    |
-----------------------------------------------------
|   0 | SELECT STATEMENT                  |         |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| DISPLAY |
-----------------------------------------------------

REM
REM Executing with approx_count_disctinct function
REM
database_12c> select approx_count_distinct (object_type) from anr123;

APPROX_COUNT_DISTINCT(OBJECT_TYPE)
__________________________________
                                42

REM
REM Executing with ISO 9075/ANSI SQL compliance 
REM  
database_12c> select count (distinct (object_type)) from anr123;

COUNT(DISTINCT(OBJECT_TYPE))
____________________________
                          42

In this case not differences found, of course it a very little table, miles of rows only. Now do you want a estimate value of differents value or usally you want the exact value?

HTH – Antonio NAVARRO

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s