Uso de índices basados en funciones con conversiones TIMESTAMP

Cuando un filtro por una columna se realiza mediante una función, el optimizador no utiliza los índices de esa columna ya que la función “transforma” los valores y hace que el índice no resulte válido. Para sortear ese obstáculo, Oracle dispone de los índices basados en funciones.

En ocasiones es el propio motor quien añade funciones a los filtros y eso puede volvernos un poco locos intentando averiguar por qué Oracle no usa los índices de la columna. Por ejemplo:

SQL> create table test (id number, dt date);

Tabla creada.

SQL> insert into test select rownum, to_date(’07/01/2014 13:00′,’DD/MM/YYYY HH24:MI’)+rownum/144 from dba_objects;

94874 filas creadas.

SQL>  select * from test where dt

        ID DT
———- ——————–
         1 07-ENE-2014 13:10:00
         2 07-ENE-2014 13:20:00
         3 07-ENE-2014 13:30:00
         4 07-ENE-2014 13:40:00
         5 07-ENE-2014 13:50:00

SQL> create index idx_test_fecha on test(dt);

Índice creado.


La tabla tiene 94874 filas numeradas con fechas a partir de 7 de enero de 2014 13:10 para cada 10 minutos consecutivamente. El índice creado sobre la columna de fecha no servirá si en el filtro de una sentencia SELECT se compara con un tipo de dato TIMESTAMP (pues causaría una conversión implícita de los valores de la columna DT de tipo fecha):

SQL> explain plan for select * from test where dt

Explicado.

SQL> @?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
—————————————————————————-
Plan hash value: 1357081020

————————————————————————–
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
————————————————————————–
|   0 | SELECT STATEMENT  |      |     3 |    66 |    71   (8)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST |     3 |    66 |    71   (8)| 00:00:01 |
————————————————————————–

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

PLAN_TABLE_OUTPUT
—————————————————————————-

   1 – filter(INTERNAL_FUNCTION(“DT”)
              14:00:00′))

Note
—–
   – dynamic sampling used for this statement (level=2)

Esta conversión implícita nos lleva al FULL SCAN de la tabla TEST.

Para este caso, además, crear un índice basado en funciones tampoco serviría, pues la función de conversión TO_TIMESTAMP no es determinista. Las funciones deterministas devuelven siempre el mismo valor a un determinado paso de parámetros, pero TO_TIMESTAMP se apoya en las variables de NLS locales como el timezone.

SQL> create index idx_fb_test_fecha on test(to_timestamp(dt));
create index idx_fb_test_fecha on test(to_timestamp(dt))
                                       *
ERROR en línea 1:
ORA-01743: sólo se pueden indexar funciones puras

Supongamos que nuestra base de datos es local y siempre se consultará con TIMESTAMP sobre una misma zona horaria. En ese caso, podemos crear nuestra propia función TO_TIMESTAMP determinista e intentar crear el índice sobre ésta (y que las consultas incluyan nuestra función, claro!).

SQL> create or replace function to_timestamp_determinista(fecha timestamp) return timestamp deterministic is
  2  begin
  3     return to_timestamp(fecha);
  4  end;
  5  /

Función creada.

SQL> create index idx_fb_test_fecha on test(to_timestamp_determinista(dt));


Índice creado.