Funciones determinísticas y Group by vs. Distinct(2)

Recientemente un comentario sobre la entrada de Group by vs. Distinct, me hizo dudar un poco sobre lo que había escrito en relación a group by vs. distinct, pero a la vez me obligó a investigar un poco más, y estas son mis observaciones.

Intentando plasmar lo que se tiene en el comentario, tenemos lo siguiente:

Comenzamos creando una tabla

SQL> CREATE TABLE valores(valor NUMBER(1))
  2  TABLESPACE users;

Tabla creada.

Una vez que se tiene la tabla, insertamos 100 registros con sólo 10 valores distintos

SQL> INSERT
  2  INTO valores
  3  SELECT MOD(rownum,   10)
  4  FROM dba_objects
  5  WHERE rownum < 101;

100 filas creadas.

Cramos una función "tardada" para probar los ejemplos

CREATE OR REPLACE FUNCTION tardada(numero IN NUMBER) 
RETURN NUMBER IS salida NUMBER;
BEGIN
  SELECT COUNT(1)
  INTO salida
  FROM dba_tables
  WHERE MOD(rownum,   10) = numero;
  RETURN salida;
END;
/

Primero vamos a evaluar el tiempo y estadísticas sin agrupar la información

SQL> set autot traceonly stat
SQL> set timing on

SQL> SELECT tardada(valor)
  2  FROM valores;

100 filas seleccionadas.

Transcurrido: 00:00:05.67

Estadísticas
----------------------------------------------------------
        100  recursive calls
     246414  consistent gets
        100  sorts (memory)
        100  rows processed

Se procesó la información en más de 5 segundos consumiendo 246,000 bloques. Se procesan 100 registros, y se hacen 100 sorts

Si lo hacemos con un distinct, estas son las estadísticas

SQL> SELECT DISTINCT tardada(valor)
  2  FROM valores;

Transcurrido: 00:00:05.68

Estadísticas
----------------------------------------------------------
        100  recursive calls
     246407  consistent gets
        100  sorts (memory)
          2  rows processed

Como vemos, se usan prácticamente los mismos recursos, pero al final nos regresa sólamente 2 registros. La función se ejecuta 100 veces.

Ahora lo intentamos hacer con un group by:

SQL> SELECT tardada(valor)
  2  FROM valores
  3  GROUP BY tardada(valor);
  
Transcurrido: 00:00:05.76

Estadísticas
----------------------------------------------------------
        100  recursive calls
     246407  consistent gets
        100  sorts (memory)
          2  rows processed

En este caso son las mismas estadísticas que con el distinct. En el siguiente ejemplo, que fue el que me llegó a confundir, se tiene lo siguiente:

SQL> SELECT tardada(valor)
  2  FROM valores
  3  GROUP BY valor;

10 filas seleccionadas.

Transcurrido: 00:00:00.65

Estadísticas
----------------------------------------------------------
         10  recursive calls
      24647  consistent gets
         10  sorts (memory)
         10  rows processed

El tiempo de ejecución fue menor a un segundo, la lectura de bloques se redujo a un 10% (24,000) bloques. El resultado nos puede llevar a la conclusión de que de esta forma, sólo se evalúa la función 10 veces debido a que sólo hay 10 valores distintos y no las 100 veces que son los registros totales de la tabla. Esto es correcto de cierta forma, es decir, la función se evalúa 10 veces porque al agrupar por "valor", nos quedamos con 10 registros a evaluar en la función. Aparentemente no hay forma de reescribir de forma simple este ejemplo de group by con un distinct, pero creo que se podría hacer de la siguiente forma:

SQL> SELECT tardada(valor)
  2  FROM
  3    (SELECT DISTINCT valor valor
  4     FROM valores)
  5  ;

10 filas seleccionadas.

Transcurrido: 00:00:00.64

Estadísticas
----------------------------------------------------------
         10  recursive calls
      24647  consistent gets
         10  sorts (memory)
         10  rows processed

Como se puede ver, se tiene el mismo consumo de recursos que en la primera situación, sin embargo creo que son dos cosas distintas los primeros ejemplos a estos últimos, ya que los últimos ejemplos, primero agrupan los registros y luego evalúan la función, es por eso que regresan 10 registros; los primeros ejemplos, evalúan la función y después agrupan, por esto, sólo regresan 2 registros.

Para poder comprobar lo que digo, voy a generar una función que siempre regrese un valor distinto:

SQL> CREATE sequence secuencia START WITH 1;

Secuencia creada.

SQL> CREATE OR REPLACE FUNCTION rapida(numero IN NUMBER) RETURN NUMBER IS salida NUMBER;
  2  BEGIN
  3    SELECT secuencia.nextval
  4    INTO salida
  5    FROM dual;
  6    RETURN salida;
  7  END;
  8  /

Función creada.

Si nosotros ejecutamos los primeros ejemplos del distinct o del group by, las respuestas serán similares a la siguiente:

SQL> SELECT DISTINCT rapida(valor)
  2  FROM valores;

RAPIDA(VALOR)
-------------
            1
           22
           25
           30
           34
           42
           43
           51
           54
           57
          ...
           82
           92
           98

100 filas seleccionadas.

SQL> DROP sequence secuencia;

Secuencia borrada.

SQL> CREATE sequence secuencia START WITH 1;

Secuencia creada.

SQL> SELECT rapida(valor)
  2  FROM valores
  3  GROUP BY rapida(valor);

RAPIDA(VALOR)
-------------
            1
           22
           25
           30
           34
           42
           43
           51
           54
           57
          ...
           82
           92
           98

100 filas seleccionadas.

Con esto, vemos que se comportan igual el distinct y el group by de mis primeros ejemplos, e incluso podemos observar que la función hash que se utiliza para evaluar duplicados es la misma, ya que regresa en el mismo orden los registros.

Ahora vamos con el ejemplo que ejecuta solamente 10 veces la función:

SQL> DROP sequence secuencia;

Secuencia borrada.

SQL> CREATE sequence secuencia START WITH 1;

Secuencia creada.

SQL> SELECT rapida(valor)
  2  FROM valores
  3  GROUP BY valor;

RAPIDA(VALOR)
-------------
            1
            2
            3
            4
            5
            6
            7
            8
            9
           10

10 filas seleccionadas.

Esto nos ayuda a entender que la agrupación se realiza antes de entrar a la función hash del group by o del distinct, y pudiera ser que no obtengamos el resultado deseado (aunque es un resultado muy válido, simplemente hay que cuidar el sentido).

Ahora, esto nos pone a pensar en que si tenemos una función que siempre que se introduzca un valor se obtendrá el mismo resultado, oracle debería de ser capaz de optimizar nuestro query para ejecutar menos veces la función.

Es aquí donde se tiene la función determinística:

SQL> CREATE OR REPLACE FUNCTION tardada2(numero IN NUMBER) RETURN NUMBER
  2  DETERMINISTIC
  3  IS salida NUMBER;
  4  BEGIN
  5    SELECT COUNT(1)
  6    INTO salida
  7    FROM dba_tables
  8    WHERE MOD(rownum,   10) = numero;
  9    RETURN salida;
 10  END;
 11  /

Función creada.

Y ahora probamos nuestros ejemplos iniciales

SQL> SELECT DISTINCT tardada2(valor)
  2  FROM valores;

Transcurrido: 00:00:00.64

Estadísticas
----------------------------------------------------------
         10  recursive calls
      24647  consistent gets
         10  sorts (memory)
          2  rows processed

SQL> SELECT tardada2(valor)
  2  FROM valores
  3  GROUP BY tardada2(valor);

Transcurrido: 00:00:00.64

Estadísticas
----------------------------------------------------------
         10  recursive calls
      24647  consistent gets
         10  sorts (memory)
          2  rows processed

Como se puede observar, tanto en el distinct como en el group by, oracle puede hacer uso de un "cache" de resultados de funciones.

Ya como último ejemplo, si una función no es determinística, Oracle puede usar un caché. Es algo que alguna vez vi en un simposio de de Hotsos. Desconozco en su totalidad la forma de evaluar si usa o no un cache, pero el ejemplo es el siguiente:

SQL> SELECT DISTINCT
  2    (SELECT tardada(valor)
  3     FROM dual)
  4  FROM valores;

Transcurrido: 00:00:00.64

Estadísticas
----------------------------------------------------------
         58  recursive calls
      24680  consistent gets
         10  sorts (memory)
          2  rows processed

Como se puede ver, la llamada a la función se mete dentro de un query, y con eso, oracle puede hacer uso del cache. Hay un poco más de llamadas recursivas, quizá porque oracle debe determinar si puede o no hacer uso del cache de funciones.

Así que de todo esto, me quedo con la misma impresión que en el fondo, group by y distinct son prácticamente iguales, pero lo que sí es importante, es el definir correctamente nuestras funciones, si sabemos que es una función determinística, crearla como tal, en caso de que no, pues simplemente omitir "DETERMINISTIC".