Emulando índices parciales en la base de datos Oracle

La manera extraña de la base de datos Oracle de tomar en cuenta NULL dentro de los índices puede utilizarse para emular los índices parciales. Para eso, tenemos que usar solamente NULL para los registros que no han de indexarse.

Para demostrarlo, emulamos el siguiente índice parcial:

CREATE INDEX messages_todo
          ON messages (receiver)
       WHERE processed = 'N'

Primero, necesitamos una función que devuelve un valor RECEIVER solamente si el valor PROCESSED es igual a 'N'.

CREATE OR REPLACE
FUNCTION pi_processed(processed CHAR, receiver NUMBER)
RETURN NUMBER
DETERMINISTIC
AS BEGIN
   IF processed IN ('N') THEN
      RETURN receiver;
   ELSE
      RETURN NULL;
   END IF;
END

La función es determinista así que puede usarse dentro de la definición del índice.

Ahora, se puede crear un índice que contenga solamente registros con PROCESSED='N'.

CREATE INDEX messages_todo
          ON messages (pi_processed(processed, receiver))

Para usar el índice, se debe utilizar la expresión indexada dentro de la sentencia:

SELECT message
  FROM messages
 WHERE pi_processed(processed, receiver) = ?

----------------------------------------------------------
|Id | Operation                   | Name          | Cost |
----------------------------------------------------------
| 0 | SELECT STATEMENT            |               | 5330 |
| 1 |  TABLE ACCESS BY INDEX ROWID| MESSAGES      | 5330 |
|*2 |   INDEX RANGE SCAN          | MESSAGES_TODO | 5303 |
----------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("PI_PROCESSED"("PROCESSED","RECEIVER")=:X)

Si te gusta mi manera de explicar, te encantará mi libro.