Oracle Database 19c APPROX_COUNT_DISTINCT Vs COUNT(DISTINCT exp)

APPROX_COUNT_DISTINCT es una función que aparece por primera vez en la versión Oracle Database 12c Release 1 ( 12.1.0 ) que es capaz de devolver el número aproximado de filas que contienen valores distintos de expresión.

Esta función proporciona una alternativa a la función COUNT (DISTINCT expresión), que devuelve el número exacto de filas que contienen valores distintos de la expresión indicada.

APPROX_COUNT_DISTINCT procesa grandes cantidades de datos significativamente más rápido que COUNT, con una desviación insignificante del resultado exacto.

Para la expresión, puede especificar una columna de cualquier tipo de datos escalar que no sea BFILE, BLOB, CLOB, LONG, LONG RAW o NCLOB.

APPROX_COUNT_DISTINCT ignora las filas que contienen un valor nulo para expresión al igual que la función COUNT().

A continuación podrás encontrar un pequeño laboratorio de como funciona la función en la más reciente versión de base de datos 19c.

A manera de inicio de lectura, la función APPROX_COUNT_DISTINCT es más rápida en cualquiera de las situaciones evaluadas.

Con los datos en memoria, es 3 centésimas más rápido. Pero sin los datos en memoria es casi un 80% más eficiente que contar las diferencias con la función COUNT( DISTINCT ).

Al examinar el plan de ejecución de las sentencias utilizando dichas funciones, nos damos cuenta que en el caso de la función APPROX_COUNT_DISTINCT ocupa menos CPU que COUNT y adicionalmente le basta con el FTS sobre la tabla para realizar el conteo de los valores distintos.

Mientras que en el plan de ejecución de COUNT(DISTINCT ) encontramos que la base de datos primero genera un vista dinámica con los valores obtenidos del DISTINCT para luego determinar el número de elementos.

SQL>  select BANNER_FULL from v$version;

BANNER_FULL
-----------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0


SQL> create user testing_user identified by oracle;

User created.

SQL> grant create session to testing_user;

Grant succeeded.

SQL> grant create table to testing_user;

Grant succeeded.

SQL> alter user testing_user quota unlimited on users;

User altered.

SQL> connect sys@pdb as sysdba
Enter password:
Connected.
SQL> grant select on dba_objects to testing_user;

Grant succeeded.

SQL> connect testing_user/oracle@pdb
Connected.
SQL> create table objetos as select object_name, owner, object_type from dba_objects;

Table created.

SQL> select count(*) from objetos;

  COUNT(*)
----------
     72359

SQL> select APPROX_COUNT_DISTINCT(owner) from objetos;

APPROX_COUNT_DISTINCT(OWNER)
----------------------------
                          24

SQL> select count(distinct owner) from objetos;

COUNT(DISTINCTOWNER)
--------------------
                  24

SQL> set timing on
SQL>  select APPROX_COUNT_DISTINCT(owner) from objetos;

APPROX_COUNT_DISTINCT(OWNER)
----------------------------
                          24

Elapsed: 00:00:00.17
SQL> select count(distinct owner) from objetos;

COUNT(DISTINCTOWNER)
--------------------
                  24

Elapsed: 00:00:00.20
SQL> connect sys@pdb as sysdba
Enter password:
Connected.

Elapsed: 00:00:00.06
SQL> alter system flush shared_pool;

System altered.

Elapsed: 00:00:02.30
SQL> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:02.51
SQL> connect testing_user/oracle@pdb
Connected.
SQL> select APPROX_COUNT_DISTINCT(owner) from objetos;

APPROX_COUNT_DISTINCT(OWNER)
----------------------------
                          24

Elapsed: 00:00:00.70
SQL> select count(distinct owner) from objetos;

COUNT(DISTINCTOWNER)
--------------------
                  24

Elapsed: 00:00:01.29
SQL> explain plan for
  2  select APPROX_COUNT_DISTINCT(owner) from objetos;

Explained.

Elapsed: 00:00:00.38
SQL> set linesize 200
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------
Plan hash value: 1430740140
--------------------------------------------------------------------------------
| Id  | Operation             | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |         |     1 |     5 |   153   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE APPROX|         |     1 |     5 |            |          |
|   2 |   TABLE ACCESS FULL   | OBJETOS | 72359 |   353K|   153   (1)| 00:00:01 |
---------------------------------------------------------------------------------

9 rows selected.

Elapsed: 00:00:03.71
SQL> explain plan for
  2  select count(distinct owner) from objetos;

Explained.

Elapsed: 00:00:00.10
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------
Plan hash value: 276252159
---------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |     1 |    66 |   155   (2)| 00:00:01 |
|   1 |  SORT AGGREGATE      |          |     1 |    66 |            |          |
|   2 |   VIEW               | VW_DAG_0 |    24 |  1584 |   155   (2)| 00:00:01 |
|   3 |    HASH GROUP BY     |          |    24 |   120 |   155   (2)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| OBJETOS  | 72359 |   353K|   153   (1)| 00:00:01 |
---------------------------------------------------------------------------------

11 rows selected.

Elapsed: 00:00:00.17
SQL>

SQL> create table diferencias ( datos varchar2(20));

Table created.

Elapsed: 00:00:01.26
SQL> insert into diferencias values ('&valor');
Enter value for valor: COSTA RICA
old   1: insert into diferencias values ('&valor')
new   1: insert into diferencias values ('COSTA RICA')

1 row created.

Elapsed: 00:00:00.29
SQL> /
Enter value for valor: GUATEMALA
old   1: insert into diferencias values ('&valor')
new   1: insert into diferencias values ('GUATEMALA')

1 row created.

Elapsed: 00:00:00.02
SQL> /
Enter value for valor: PANAMA
old   1: insert into diferencias values ('&valor')
new   1: insert into diferencias values ('PANAMA')

1 row created.

Elapsed: 00:00:00.12
SQL> /
Enter value for valor: COLOMBIA
old   1: insert into diferencias values ('&valor')
new   1: insert into diferencias values ('COLOMBIA')

1 row created.

Elapsed: 00:00:00.02
SQL> /
Enter value for valor: ARGENTINA
old   1: insert into diferencias values ('&valor')
new   1: insert into diferencias values ('ARGENTINA')

1 row created.

Elapsed: 00:00:00.04
SQL> /
Enter value for valor: PARAGUAY
old   1: insert into diferencias values ('&valor')
new   1: insert into diferencias values ('PARAGUAY')

1 row created.

Elapsed: 00:00:00.10
SQL> /
Enter value for valor: ECUADOR
old   1: insert into diferencias values ('&valor')
new   1: insert into diferencias values ('ECUADOR')

1 row created.

Elapsed: 00:00:00.04
SQL> /
Enter value for valor: MEXICO
old   1: insert into diferencias values ('&valor')
new   1: insert into diferencias values ('MEXICO')

1 row created.

Elapsed: 00:00:00.51
SQL> /
Enter value for valor:
old   1: insert into diferencias values ('&valor')
new   1: insert into diferencias values ('')

1 row created.

Elapsed: 00:00:00.63
SQL> commit;

Commit complete.

Elapsed: 00:00:00.06
SQL> select count(*) from diferencias;

  COUNT(*)
----------
         9

Elapsed: 00:00:00.06
SQL> select * from diferencias;

DATOS
--------------------
COSTA RICA
GUATEMALA
PANAMA
COLOMBIA
ARGENTINA
PARAGUAY
ECUADOR
MEXICO


9 rows selected.

Elapsed: 00:00:00.36
SQL> select count(distinct datos ) from diferencias;

COUNT(DISTINCTDATOS)
--------------------
                   8

Elapsed: 00:00:00.11
SQL> select APPROX_COUNT_DISTINCT(datos) from diferencias;

APPROX_COUNT_DISTINCT(DATOS)
----------------------------
                           8

Elapsed: 00:00:00.06
SQL> select count(*) from diferencias;

  COUNT(*)
----------
         9

Elapsed: 00:00:00.03

SQL>