Cuidado con usar funciones min y max en la misma consulta

El optimizador de Oracle es una pieza de software muy compleja e inteligente, pero ciertas veces hace cosas raras y poco intuitivas. Hace poco ví un caso de una consulta que tomaba el máximo y el mínino valor de una columna X sobre una cierta tabla y la almacenaba en variables para realizar cierta logica procedural. El código implementaba la siguiente sentencia para obtener dichos valores:

..
select min(x),max(x)
into l_min,l_max
from t
..

A simple vista se ve como una forma compacta de obtener el min y max con solo una consulta.La columna X tenía definido un índice btree y por lo tanto, sabiendo que la estructura de los indices es de tipo arbol con un nivel o altura generalmente de 3, sin pensar demasiado imaginamos que el optimizador necesitará acceder 3 bloques (bajar por las ramas hasta la hoja de mas a la izquierda) para obtener el valor mínimo y otro 3 bloques (bajar por las ramas hasta la hoja de mas a la derecha) para obtener el valor máximo. Por lo tanto imaginamos que aprovechando el indice Oracle necesitara acceder solo 6 bloques para retornar el resultado. Sin embargo veremos que el optimizador no usa el path adecuado cuando pretendemos obtener dichos valores utilizando una sola sentencia.

Para probar, voy a crear una tabla con 2 campos, el campo X de tipo entero y un campo Y de tipo CHAR(500) utilizado de relleno para generar muchos bloques y así desalentar al optimizador a elegir full scan sobre la tabla.

rop@DESA10G> create table t (x int,y char(500));

Tabla creada.

rop@DESA10G> insert into t select rownum,'blabla' from dual connect by rownum <= 1000000;

1000000 filas creadas.

Chequeamos que se hayan creado una cantidad considerable de bloques:

rop@DESA10G> select blocks,bytes from user_segments where segment_name = 'T';

    BLOCKS      BYTES
---------- ----------
     72704  595591168

Creamos el indice por la columna X y recolectamos estadíticas para la tabla y el índice

rop@DESA10G> create index t_idx on t(x);

Índice creado.

rop@DESA10G> begin
             dbms_stats.gather_table_stats(ownname=>user,tabname=>'T',cascade=>true);
             end;

Ya tenemos armado el escenario para comenzar a probar. Vemos que hace Oracle para obtener el max y min de una vez:

rop@DESA10G>  select min(x),max(x) from t;

    MIN(X)     MAX(X)
---------- ----------
         1    1000000


Plan de Ejecución
----------------------------------------------------------
Plan hash value: 1842905362

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     5 | 16000   (1)| 00:05:49 |
|   1 |  SORT AGGREGATE    |      |     1 |     5 |            |          |
|   2 |   TABLE ACCESS FULL| T    |  1003K|  4899K| 16000   (1)| 00:05:49 |
---------------------------------------------------------------------------


Estadísticas
----------------------------------------------------------
        178  recursive calls
          0  db block gets
      71601  consistent gets
      71109  physical reads
          0  redo size
        272  bytes sent via SQL*Net to client
        236  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          5  sorts (memory)
          0  sorts (disk)
          1  rows processed

rop@DESA10G> 

El plan armado requiere recorrer todos los bloques de la tabla (full scan) que requeriran acceso fisico al disco con la consiguiente desventaja que todos sabemos involucra una excesiva i/o y una mayor demora en obtener el resultado.
Vemos que pasa al obtener el max y el min de a una sentencia por vez:

rop@DESA10G> select min(x) from t;

    MIN(X)
----------
         1


Plan de Ejecución
----------------------------------------------------------
Plan hash value: 1344832317

------------------------------------------------------------------------------------
| Id  | Operation                  | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |       |     1 |     5 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE            |       |     1 |     5 |            |          |
|   2 |   INDEX FULL SCAN (MIN/MAX)| T_IDX |  1003K|  4899K|     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------


Estadísticas
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        240  bytes sent via SQL*Net to client
        236  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

rop@DESA10G> select max(x) from t;

    MAX(X)
----------
   1000000


Plan de Ejecución
----------------------------------------------------------
Plan hash value: 1344832317

------------------------------------------------------------------------------------
| Id  | Operation                  | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |       |     1 |     5 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE            |       |     1 |     5 |            |          |
|   2 |   INDEX FULL SCAN (MIN/MAX)| T_IDX |  1003K|  4899K|     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------


Estadísticas
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          3  consistent gets
          1  physical reads
          0  redo size
        240  bytes sent via SQL*Net to client
        236  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Se puede constatar que al obtener tanto el min como el max se utilizo el path "INDEX FULL SCAN (MIN/MAX)" que existe desde por lo menos 9i (no recuerdo exactamente en que versión se introdujo) y por lo tanto solo se necesitaron acceder 3 bloques por cada ejecución de las sentencias.
Ahora veamos que sucede si definimos a la columna como not null. Recordemos que los valores null no se almacenan en los indices btree. Declarando explicitamente que la columna no puede contener nulos se le da mayor información al optimizador y asi le ayudamos a que evalue otros posibles paths de acceso a los datos.

rop@MOVI10D> alter table t modify x not null;

Tabla modificada.

rop@MOVI10d> 

rop@DESA10G> select min(x),max(x) from t;

    MIN(X)     MAX(X)
---------- ----------
         1    1000000


Plan de Ejecución
----------------------------------------------------------
Plan hash value: 1799443504

-------------------------------------------------------------------------------
| Id  | Operation             | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |       |     1 |     5 |   506   (2)| 00:00:12 |
|   1 |  SORT AGGREGATE       |       |     1 |     5 |            |          |
|   2 |   INDEX FAST FULL SCAN| T_IDX |  1003K|  4899K|   506   (2)| 00:00:12 |
-------------------------------------------------------------------------------


Estadísticas
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       2241  consistent gets
          2  physical reads
          0  redo size
        271  bytes sent via SQL*Net to client
        236  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Ahora se necesitaron consultar 2241 bloques de memoria y 0 accesos a disco ya que cambio el plan a un "INDEX FAST FULL SCAN" con lo cual no se necesitó visitar los bloques de la tabla y el resultado se pudo obtener solo recorriendo los bloques del índice. De todas formas, si bien esto es mucho mejor que el full scan de la tabla, dista bastante de ser el camino óptimo. Para eso debemos lograr que el optimizador utilice el path "INDEX FULL SCAN (MIN/MAX)". Reescribiendo un poco la consulta original logramos obtener el min y max consultando solo 6 bloques consultados:

rop@DESA10G> select min_x,max_x
  2  from (select min(x) min_x from t),
  3       (select max(x) max_x from t);

     MIN_X      MAX_X
---------- ----------
         1    1000000


Plan de Ejecución
----------------------------------------------------------
Plan hash value: 2194354140

--------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |     1 |    26 |     6   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |       |     1 |    26 |     6   (0)| 00:00:01 |
|   2 |   VIEW                       |       |     1 |    13 |     3   (0)| 00:00:01 |
|   3 |    SORT AGGREGATE            |       |     1 |     5 |            |          |
|   4 |     INDEX FULL SCAN (MIN/MAX)| T_IDX |  1003K|  4899K|     3   (0)| 00:00:01 |
|   5 |   VIEW                       |       |     1 |    13 |     3   (0)| 00:00:01 |
|   6 |    SORT AGGREGATE            |       |     1 |     5 |            |          |
|   7 |     INDEX FULL SCAN (MIN/MAX)| T_IDX |  1003K|  4899K|     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------


Estadísticas
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          6  consistent gets
          0  physical reads
          0  redo size
        269  bytes sent via SQL*Net to client
        236  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed