lunes, 10 de septiembre de 2007

Buscar el valor MAX o MIN de un tabla de forma eficiente

Muchas veces tenemos que buscar el valor máximo o mínimo actual de cierta columna en una tabla, pero suele ser una consulta ineficiente. Generalmente se leen muchos bloques de datos para encontrar el valor MAX/MIN y ésto hace que cuando se ejecute la consulta en nuestro ambiente OLTP bajo gran cantidad de usuarios concurrentes, tengamos graves problemas de performance.
Como es sabido, más del 90% del trabajo de tuning es sobre la aplicación y no necesariamente sobre la base de datos. Este caso es un problema de la aplicación, particularmente cómo se escribe la consulta para obtener el MAX/MIN.

Supongamos el siguiente ejemplo:

SQL_9iR2> CREATE TABLE test AS
2 SELECT level id, 'nom_'||level nombre
3 FROM dual
4 CONNECT BY level <= 1000000 ;

Table created.

SQL_9iR2> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'TEST') ;

PL/SQL procedure successfully completed.

Tenemos una tabla cargada con 1 millón de registros. Supongamos que queremos obtener el MAX de la columna ID de esta tabla. Escribiríamos la siguiente consulta...

SQL_9iR2> explain plan for
2 SELECT MAX(id)
3 FROM test ;

Explained.

SQL_9iR2> @explains

--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 279 |
| 1 | SORT AGGREGATE | | 1 | 5 | |
| 2 | TABLE ACCESS FULL | TEST | 1000K| 4882K| 279 |
--------------------------------------------------------------------

SQL_9iR2> SET AUTOTRACE TRACEONLY STATISTICS

Statistics
---------------------------------------------------
0 recursive calls
0 db block gets
2889 consistent gets
773 physical reads

0 redo size
329 bytes sent via SQL*Net to client
495 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

En el Explain Plan vemos que se está realizando un Full Scan de la tabla TEST y en las estadísticas del Autotrace observamos que se leen 2889 bloques de datos desde memoria y 773 bloques desde disco.
Lo ideal es reducir lo mayor posible la cantidad de lecturas lógicas. Cómo podemos optimizar ésta consulta? Qué sucede si creamos un índice único sobre la columna ID?

SQL_9iR2> CREATE UNIQUE INDEX test_id_idx ON test(id) ;

Index created.

SQL_9iR2> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'TEST', cascade => true) ;

PL/SQL procedure successfully completed.

SQL_9iR2> explain plan for
2 SELECT MAX(id)
3 FROM test ;

Explained.

SQL_9iR2> @explains

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 279 |
| 1 | SORT AGGREGATE | | 1 | 5 | |
| 2 | INDEX FULL SCAN (MIN/MAX)| TEST_ID_IDX | 1000K| 4882K| 279 |
---------------------------------------------------------------------------

SQL_9iR2> SET AUTOTRACE TRACEONLY STATISTICS

Statistics
---------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads

0 redo size
329 bytes sent via SQL*Net to client
495 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL_9iR2> explain plan for
2 SELECT MIN(id)
3 FROM test ;

Explained.

SQL_9iR2> @explains

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 279 |
| 1 | SORT AGGREGATE | | 1 | 5 | |
| 2 | INDEX FULL SCAN (MIN/MAX)| TEST_ID_IDX | 1000K| 4882K| 279 |
---------------------------------------------------------------------------

SQL_9iR2> SET AUTOTRACE TRACEONLY STATISTICS

Statistics
---------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads

0 redo size
329 bytes sent via SQL*Net to client
495 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

Vemos que tanto para sacar el MAX o el MIN, se están leyendo solamente 3 bloques de datos desde memoria y ninguno desde disco! Esto no es ninguna magia. Se debe a que se está utilizando el método de acceso INDEX FULL SCAN (MIN/MAX).
Cuando se utiliza el acceso INDEX FULL SCAN, Oracle no lee todos los bloques del índice (no lee todos los bloques que conforman la estructura del árbol del índice), sino que lee solamente los Leaf Blocks del índice (son los bloques del índice que contiene los datos. Son los bloques que se encuentran en la raíz del árbol del índice). Cuando se accede a través del INDEX FULL SCAN (MIN/MAX), Oracle solamente accede al primer Leaf Block en caso de que busquemos el MIN, o al último Leaf Block en caso de que busquemos el MAX. Es una manera muy eficiente de obtener el valor máximo o mínimo de un índice.

Veamos lo siguiente. El ejemplo que acabos de realizar fue creando un índice de forma ascendente (es el default). Qué sucede si realizamos el mismo ejemplo creando un índice de forma descendente?

SQL_9iR2> DROP INDEX test_id_idx ;

Index dropped.

Eliminamos el índice que habíamos creado y ejecutamos la consulta buscando el MAX:

SQL_9iR2> explain plan for
2 SELECT MAX(id)
3 FROM test ;

Explained.

SQL_9iR2> @explains

--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 279 |
| 1 | SORT AGGREGATE | | 1 | 5 | |
| 2 | TABLE ACCESS FULL | TEST | 1000K| 4882K| 279 |
--------------------------------------------------------------------

SQL_9iR2> SET AUTOTRACE TRACEONLY STATISTICS

Statistics
---------------------------------------------------
0 recursive calls
0 db block gets
2889 consistent gets
773 physical reads

0 redo size
329 bytes sent via SQL*Net to client
495 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

Ahora creamos un índice descendiente por la columna ID:

SQL_9iR2> CREATE UNIQUE INDEX test_id_idx ON test(id DESC) ;

Index created.

SQL_9iR2> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'TEST', cascade => true) ;

PL/SQL procedure successfully completed.

Ejecutamos la consulta buscando el MAX y luego el MIN:

SQL_9iR2> explain plan for
2 SELECT MAX(id)
3 FROM test ;

Explained.

SQL_9iR2> @explains

--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 279 |
| 1 | SORT AGGREGATE | | 1 | 5 | |
| 2 | TABLE ACCESS FULL | TEST | 1000K| 4882K| 279 |
--------------------------------------------------------------------

SQL_9iR2> SET AUTOTRACE TRACEONLY STATISTICS

Statistics
---------------------------------------------------
0 recursive calls
0 db block gets
2889 consistent gets
773 physical reads

0 redo size
329 bytes sent via SQL*Net to client
495 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL_9iR2> explain plan for
2 SELECT MIN(id)
3 FROM test ;

Explained.

SQL_9iR2> @explains

--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 279 |
| 1 | SORT AGGREGATE | | 1 | 5 | |
| 2 | TABLE ACCESS FULL | TEST | 1000K| 4882K| 279 |
--------------------------------------------------------------------

SQL_9iR2> SET AUTOTRACE TRACEONLY STATISTICS

Statistics
---------------------------------------------------
0 recursive calls
0 db block gets
2889 consistent gets
773 physical reads

0 redo size
329 bytes sent via SQL*Net to client
495 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

Ouch!!! Que pasó??? El CBO no está utilizando nuestro índice! Pero porqué? Bueno, ésto es debido al Bug 1389479. EL CBO no puede utilizar el tipo de acceso INDEX FULL SCAN (MIN/MAX) cuando creamos un índice descendiente.

8 comentarios:

fran dijo...

Oye tú blogs es super bueno, muchas gracias he aprendido harto gracias a tí

fran dijo...

Discula Leonardo,

Qué hace esto "EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'TEST') ;"?

Leonardo Horikian dijo...

flavin, si ejecutamos "EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'TEST') ;" obtenemos estadísticas para la tabla TEST y las columnas que contiene la tabla.

Anónimo dijo...

que buenos consejos, gracias voy a aplicarlos en mis diseños en al BD.

Anónimo dijo...

Esta bueno tu blog mi amigo, te pregunto si no sería mas optimo en vez de usar un max utilizar un hint tipo index_desc y en el where agregarle un rownum = 1?

Saludos,

Marcelo

Leonardo Horikian dijo...

Hola Marcelo, no habría diferencia de utilizar tu técnica. De todos modos estaríamos leyendo la misma cantidad de bloques de datos. Pero, no podemos utilizar tu técnica para el query que muestro en mi ejemplo:

SELECT MAX(id)
FROM test ;

Esto es porque el CBO, sólo utiliza el hint INDEX_DESC si el query utiliza un Index Range Scan. En ese caso, Oracle escanea el índice en orden descendente.
Podríamos utilizar tu técnica si el query sería de ésta manera...

SELECT /*+ INDEX_DESC(test test_id_idx) */ id
FROM test
WHERE id BETWEEN 1 AND 10
AND ROWNUM = 1 ;

Saludos.

Anónimo dijo...

Hola Leonardo,
Tengo una pregunta, sucede lo mismo si el indice es ascendente?
Gracias!

Leonardo Horikian dijo...

Hola Anónimo,

Si, sucede con índices ascendentes.
En el post se muestran ejemplos buscando el valor MIN y MAX con índices ascendentes.

Saludos

The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.