jueves, 20 de septiembre de 2007

Buscar valores NULL en forma eficiente

Un problema que veo seguidamente, se relaciona con la escritura de consultas que buscan valores NULL en una tabla. Este es un problema muy común.
A continuación vamos a ver algunas soluciones que podemos implementar para buscar los valores NULL en forma eficiente y evitar lecturas innecesarias de bloques de datos.

Veamos algunos ejemplos:

SQL_9iR2> CREATE TABLE test AS
2 SELECT decode(mod(rownum,100),0,null,level) id, 'nom_'||level nom
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.

Veamos la cantidad de valores NULL que tiene la tabla que creamos:

SQL_9iR2> SELECT count(*)
2 FROM test
3 WHERE id IS NULL ;

COUNT(*)
----------
10000

1 row selected.

Veamos cómo se ejecuta internamente esa consulta:

SQL_9iR2> explain plan for
2 SELECT count(*)
3 FROM test
4 WHERE id IS NULL ;

Explained.

SQL_9iR2> @explains

--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 304 |
| 1 | SORT AGGREGATE | | 1 | 7 | |
|* 2 | TABLE ACCESS FULL | TEST | 10000 | 70000 | 304 |
--------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("TEST"."ID" IS NULL)

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.16 0.16 3144 3150 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.17 0.16 3144 3150 0 1

Observamos que obviamente está realizando un Full Scan. Qué sucede si creamos un índice B*Tree?

SQL_9iR2> CREATE 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 count(*)
3 FROM test
4 WHERE id IS NULL ;

Explained.

SQL_9iR2> @explains

--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 304 |
| 1 | SORT AGGREGATE | | 1 | 7 | |
|* 2 | TABLE ACCESS FULL | TEST | 10000 | 70000 | 304 |
--------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("TEST"."ID" IS NULL)

Porqué nuestra consulta no está accediendo a través del índice? por la simple razón de que los índices B*Tree no indexan los valores NULL. Como estamos realizando un COUNT buscando la cantidad de valores NULL de la tabla, el CBO sabe que si accede por índice, la consulta nos va a retornar un resultado erróneo ya que no existen valores NULL en el índice. Es por eso, que en vez de acceder por índice, realiza un Full Scan de la tabla. Esto tiene como consecuencia una lectura innecesaria de bloques por no haber accedido a través de un índice.

Una solución para éste problema sería crear un FBI (Function Based-Index) para indexar sólo los valores NULL de la columna:

SQL_9iR2> CREATE INDEX test_id_nulo_idx ON test(NVL(id,'nulo')) ;

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 count(*)
3 FROM test
4 WHERE NVL(id,'nulo') = 'nulo' ;

Explained.

SQL_9iR2> @explains

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 27 |
| 1 | SORT AGGREGATE | | 1 | 7 | |
|* 2 | INDEX RANGE SCAN | TEST_ID_NULO_IDX | 10001 | 70007 | 27 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access(NVL("TEST"."ID",'nulo')='nulo')

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 1 26 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 1 26 0 1


Veamos qué sucede si creamos un índice Bitmap:

SQL_9iR2> DROP INDEX test_id_idx ;

Index dropped.

SQL_9iR2> CREATE BITMAP INDEX test_id_bitmap 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 count(*)
3 FROM test
4 WHERE id IS NULL ;

Explained.

SQL_9iR2> @explains

--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 44 |
| 1 | SORT AGGREGATE | | 1 | 7 | |
| 2 | BITMAP CONVERSION COUNT | | | | |
|* 3 | BITMAP INDEX SINGLE VALUE| TEST_ID_BITMAP | | | |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("TEST"."ID" IS NULL)

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 1 5 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 1 5 0 1

Observamos que también podemos utilizar índices Bitmap para buscar valores NULL ya que ésta clase de índices SI indexan éstos valores.
Lo importante a tener en cuenta si utilizamos éste índice es que cada modificación que se realiza sobre el índice, requiere un gran trabajo del sistema que si utilizamos índices B*tree. Por otro lado, si a ésto le sumamos las modificaciones concurrentes que se realizan sobre la columna indexada, puede llegar a ser mortal para el sistema.

18 comentarios:

Alejandro dijo...

Hola, tengo una duda, no es respecto al tema de los valores NULL, es sobre la descarga de Oracle, quiero bajar el 10g para windows, y veo que en la pagina hay varios archivos, debo bajarlos todos o solo el primero que dice ser los archivos completos?? una vez descomprimidos ya se puede usar o hay que realizar un proceso de instalación? Ojalá pudieras ayudarme, de antemano gracias. Atte. Alejandro Galván

Leonardo Horikian dijo...

Hola Alejandro, podes bajar la base de datos 10g para windows desde http://www.oracle.com/technology/software/products/database/index.html y dependiendo donde lo vas a instalar debes elegir el link correspondiente. Si por ejemplo, queres instalar "Oracle Database 10g Release 2 (10.2.0.1.0) for Microsoft Windows (64-bit Itanium)", para bajar la base de datos, elegis "Download the Complete Files". Una vez que lo bajas y descomprimas, lo instalas con la herramienta gráfica que se incluye.
Saludos.

Hector Gabriel Ulloa Ligarius dijo...

Hola Leonardo..

Como lo mencionas , el índice de bitmap con transacciones concurrentes se bloquea de una manera increíble, por ejemplo

un registro1 con el valor ORACLE y un registro2 con el valor ORAC

Si yo actualizo el registro2 , además bloqueo el registro1 , simplemente por la similitud de valores. Lo cual de una u otra forma te hace pensar en que tipo de índice colocar, desde un bitmap , un B*Tree, un Based Function , un Inverse key, un compress etc,etc,etc

Atte
Hector Gabriel Ulloa Ligarius
http://ligarius.wordpress.com

Anónimo dijo...

Hola mi nombre es Pierre de Perú, tengo Oracle Database 10g Enterprise Edition Release 10.2.0.1.0, cuando creo el indice basado en función, igual me hace el full scan, sera que es necesario instalar alguna opción adicional.
Pierre Villacorta
pvillacorta@agroindustriallaredo.com

Leonardo Horikian dijo...

Hola Pierre!

Fijate el valor del parámetro query_rewrite_enabled. Para usar Function Based-Indexes, este parámetro debería estar en TRUE.

SELECT name, value
FROM v$parameter
WHERE name = 'query_rewrite_enabled' ;

Saludos.

Anónimo dijo...

SELECT name, substr(value,1,50) FROM v$parameter WHERE name = 'query_rewrite_enabled';
NAME SUBSTR(VALUE,1,50)
------------------- -----------------------
query_rewrite_enabled TRUE

es correcto de acuerdo a lo que expresaste.. el bitmap si funciona.. pero no se porque el por funcion no.. existen otros parametros que chequear

Leonardo Horikian dijo...

Ok, te pido que me muestres la consulta que estas ejecutando y el índice que creaste.

Luego de crear el índice, ejecutaste las estadísticas para el índice? La tabla está analizada?

Saludos

Anónimo dijo...

SQL> set autotrace on;
SP2-0618: No se ha podido encontrar el identificador de sesión. Compruebe si el rol PLUSTRACE está activado
SP2-0611: Error al activar el informe STATISTICS
SQL> set echo on;
SQL> set timing on;
SQL> set linesize 150;
SQL> set pagesize 3000;
SQL> CREATE INDEX test_id_nulo_idx ON test(NVL(id,'nulo'));

Índice creado.

Transcurrido: 00:00:08.32
SQL> EXEC dbms_stats.GATHER_TABLE_STATS(USER,'TEST',cascade=>true) ;

Procedimiento PL/SQL terminado correctamente.

Transcurrido: 00:00:17.98
SQL> Analyze Table PVILLACORTA.TEST
2 ;

*
ERROR en línea 2:
ORA-01490: comando ANALYZE no válido


Transcurrido: 00:00:00.21
SQL> SELECT count(*) FROM test WHERE id IS NULL ;

COUNT(*)
----------
10000

Transcurrido: 00:00:00.34

Plan de Ejecución
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=736 Card=1 Bytes
=7)

1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'TEST' (TABLE) (Cost=736 Card=946
1 Bytes=66227)

Anónimo dijo...

SQL>
SQL> Analyze Index PVILLACORTA.TEST_ID_NULO_IDX COMPUTE STATISTICS;

Índice analizado.

Transcurrido: 00:00:00.98
SQL>
SQL> Analyze Table PVILLACORTA.TEST COMPUTE STATISTICS;

Tabla analizada.

Transcurrido: 00:00:44.20
SQL> SELECT count(*) FROM test WHERE id IS NULL ;

COUNT(*)
----------
10000

Transcurrido: 00:00:00.32

Plan de Ejecución
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=736 Card=1 Bytes
=6)

1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'TEST' (TABLE) (Cost=736 Card=100
00 Bytes=60000)


disculpa fallaron mis codigos anteriores.

Leonardo Horikian dijo...

Fijate lo siguiente.

Vos creaste el siguiente índice:

CREATE INDEX test_id_nulo_idx ON test(NVL(id,'nulo'));

Y ejecutas la consulta:

SELECT count(*) FROM test WHERE id IS NULL ;

Obviamente el CBO no va a tomar el índice que creaste. Porque? Porque tenes que colocar la función con la cual creaste el índice en la consulta.

Si ejecutas la consulta de esta manera...

SELECT count(*) FROM test WHERE NVL(id,'nulo') = 'nulo' ;

...vas a ver que el CBO va a acceder a través del índice que creaste.


Saludos.

Anónimo dijo...

SQL> SELECT count(*) FROM test WHERE NVL(id,'nulo') = 'nulo' ;

COUNT(*)
----------
10000

Transcurrido: 00:00:00.14

Plan de Ejecución
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=27 Card=1 Bytes=
6)

1 0 SORT (AGGREGATE)
2 1 INDEX (RANGE SCAN) OF 'TEST_ID_NULO_IDX' (INDEX) (Cost=2
7 Card=10000 Bytes=60000)

Tienes toda la razón, gracias por el apoyo.

Anónimo dijo...

Hola, tengo 2 consultas. La primera es sobre la creación del FBI (CREATE INDEX test_id_nulo_idx ON test(NVL(id,'nulo')) ), este indexa solo los valores null y el resto los ignora. Mi segunda consulta es acerca de poder ver el resultado del explain del ejemplo, yo utilizo solamente (select * from table dbms_xplan.display) ) me sale al final el mensaje de : (Note: cpu costing is off) y no se como cambiarlo para que me aparezcan los datos del uso del CPU como muestras en tu ejemplo.
Gracias.

Leonardo Horikian dijo...

Hola,

1) Si creas un FBI (CREATE INDEX test_id_nulo_idx ON test(NVL(id,'nulo'))), se almacenan en el índice sólo los registros que contienen valores NULL y el resto de los registros se ignoran.

2) La parte de mi ejemplo en donde estas viendo el CPU es debido a la ejecución del TKPROF, no es la salida que te muestra en el Explain Plan.
El Explain Plan te muestra el mensaje "cpu costing is off" porque seguramente estás utilizando una versión de Oracle anterior a 10g y no hay estadísticas obtenidas para el sistema. En 10g, la obtención de estas estadísticas es automática, pero en versiones anteriores no lo es.

Hugo E. Contreras Gamiño dijo...

En un índice compuesto, los valores nulos de los campos "prefijo" deben de ser indexados, de tal forma que si se crea un índice con un campo "dummy", el valor nulo quedará indexado. El índice en este caso sería tomado y no necesitas el query_rewrite_enabled.

SQL> CREATE TABLE test AS
2 SELECT decode(MOD(rownum, 100), 0, NULL, LEVEL) id,
3 'nom_' || LEVEL nom
4 FROM dual CONNECT BY LEVEL <= 1000000;

Tabla creada.

SQL> CREATE INDEX test_id_idx ON test(id, 1);

═ndice creado.

SQL> ALTER SESSION
2 SET query_rewrite_enabled = FALSE;

Sesi¾n modificada.

SQL> EXPLAIN PLAN FOR
2 SELECT COUNT(*)
3 FROM test
4 WHERE id IS NULL;

Explicado.

SQL> SELECT *
2 FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------

Plan hash value: 3311446695

----------------------------------------
| Id | Operation | Name
----------------------------------------
| 0 | SELECT STATEMENT |
| 1 | SORT AGGREGATE |
|* 2 | INDEX RANGE SCAN| TEST_ID_IDX
----------------------------------------


2 - access("ID" IS NULL)

Anónimo dijo...

Hola,

cómopuedo utilizar la páginación en Oracle, para una consulta parametrizada, es decir, una PROCEDURE recibe varios parámetros, algunos de los cuáles pueden ser nulos. Estos parámetros son de filtro (el WHERE sería dinámico según los parámetros).

Este PROCEDURE quiero que haga uso de la Paginación.

Algún ejemplo real que me sirva de base por favor ?

Gracias y saludos.

Anónimo dijo...

Hola,
Perdón por mi ignorancia, pero el procedimiento "explains" utilizado, ¿es uno de los prefijados por Oracle? (entonces, ¿en qué carpeta se encuentra?), o ¿es un procedimiento propio?, en ese caso ¿podrías postear su contenido? porque con un select * from plan_table sólo obtenemos el plan de ejecución, no los datos sobre parse, execute, etc.

Gracias por todo, y felicidades por el blog.

Leonardo Horikian dijo...

Hola,

El script "explains.sql" tiene la misma información que el script llamado "utlxpls.sql" que se encuentra en $ORACLE_HOME/rdbms/admin.

Este es el contenido del script "utlxpls.sql"...

select plan_table_output from table(dbms_xplan.display('plan_table',null,'serial'));

Saludos!

Leonardo Horikian dijo...

Hola Anónimo,

Uno de los mejores métodos que conozco para hacer uso de la paginación es el siguiente...

select *
from
(
select a.*, rownum rnum
from ( AQUI VA EL SELECT ) a
where rownum <= MAX_ROWS
)
where rnum >= MIN_ROWS;

Saludos