martes, 30 de septiembre de 2008

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".

3 comentarios:

Nikolodeon dijo...

Que onda Hugo!

ciertamente, al ver que las operaciones distinct y group-by hacen uso de la misma función hash, queda claro que el cambio de performance al utilizar una agrupación es debido a que oracle precalculó los registros a los que se les va a aplicar la función antes de hacerlo, por que primero resuelve las tuplas que va a recojer antes de ejecutar las funciones, ya que estas no afectan al discriminante, pero el GROUP BY si.

Sobre el uso de las funciones deterministicas, estuve revisando en AskTom, y se menciona que las funciones deterministicas solo deben utilizarse en funciones basadas en índices y en vistas materializadas que usen el Query Rewrite, por los ejemplos que se manejan, y la problemática que leí, asumo que Oracle no puede asumir que una funcion es deterministica si dentro de esa función, se tocan los registros con operaciones DML, ¿que tan cierto es eso?.

Otra cosa, para el caso de funciones donde retornen valores mas complejos (como un CURSOR de PL/SQL, o un TYPE) ¿Oracle sigue "cacheando" los resultados aunque el caché pueda ser grande?

Saludos!

Hugo E. Contreras Gamiño dijo...

Hola Nikolodeon, gracias por el comentario, así es, los índices de función, sólo deben de tener funciones determinísticas, si no fuera así, que caso tendría indexar para invalidar.

Definitivamente queda mucho por probar, y sobre todo con el "Result cache" de 11g, pero eso será en otra ocasión. Haré las pruebas que regresan un tipo y pondré los resultados.

Anónimo dijo...

te quiero Papi espero que te valla muy bien en tu trabajo y que tu puedas ser un gran papi siendo compañero mio y espero que te sientas bien con migo porque yo se que tu me quieres y yo a ti ,de tantas alegrias que hemos pasado juntos y espero que te valla bien en tu trabajo sabes que te quiero
Ana Isabel ,tu hija