Indexando columnas con funciones impuras.

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 obtener los ROWIDs adecuados. 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;

73318 filas creadas.

SQL> select * from test

2 where dt < to_timestamp('07/01/2014 14:00','DD/MM/YYYY HH24:MI');

ID DT

---------- ---------------------------------------------------------------------------
1 07/01/14 13:10:00,000000
2 07/01/14 13:20:00,000000
3 07/01/14 13:30:00,000000
4 07/01/14 13:40:00,000000
5 07/01/14 13:50:00,000000
6 07/01/14 14:00:00,000000

6 filas seleccionadas.

SQL> create index idx_test_fecha on test(dt);

Índice creado.

SQL> explain plan for
2 select *
3 from test
4 where dt < to_date('07/01/2014 14:00','DD/MM/YYYY HH24:MI');

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 (5)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST | 3 | 66 | 71 (5)| 00:00:01 |
--------------------------------------------------------------------------

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

1 - filter(INTERNAL_FUNCTION("DT")>=TIMESTAMP' 2014-01-07
13:00:00.000000000' AND INTERNAL_FUNCTION("DT")<=TIMESTAMP' 2014-01-07
14:00:00.000000000')

Note
-----
- dynamic statistics used: dynamic sampling (level=2)

 

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

El motivo es porque Oracle no puede establecer una conversión directa entre TIMESTAMP y DATE. Como función, TO_TIMESTAMP está transformando los valores en base a la región local y eso nos lleva al siguiente problema.

El hecho de crear un índice basado en funciones tampoco serviría, dado que la función de conversión TO_TIMESTAMP no es deterministica. 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. Así que además de la conversión de la función, la globalización permite que los valores que resultan de TO_TIMESTAMP sean distintos si el cliente tiene la configuración horaria de España o la de México.

Así, si intentamos crear un índice basado en la función TO_TIMESTAMP, Oracle nos devolverá un error indicando que la función TO_TIMESTAMP no es pura. Sobre todo para crear un índice donde los valores que se almacenan son fijos, los valores que devuelve una función han de ser siempre los mismos a unos mismos valores de entrada, porque así han de ser los valores dentro del índice: siempre los mismos resultados a los mismos valores de entrada de la función.

TO_NUMBER es una función pura. Siempre que se le pase la cadena ‘123’ el número será 123.
TO_TIMESTAMP variará el resultado dependiendo de las variables de NLS. La ordenación de este resultado se ve afectada y, por tanto, los datos que devolviera el índice no coincidirían.

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

Y ahora un truco peligroso.

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 puedan incluir 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.

TO_TIMESTAMP variará el resultado dependiendo de las variables de NLS y no es determinista, pero TO_TIMESTAMP_DETERMINISTA está definida como si lo fuera, aunque no lo sea.

Es decir, nuestra función TO_TIMESTAMP_DETERMINISTA está considerada pura.

Aunque no lo sea, insisto.

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

Índice creado.

Ahora ya es posible que nuestra consulta pueda utilizar el índice basado en función para convertir a TIMESTAMP de forma determinística, y beneficiarnos del uso del índice para recuperar las 7 filas entre más de 73.000.

SQL> select * from test
2 where to_timestamp_determinista(dt)<
3 to_timestamp_determinista(to_date('07/01/2014 14:00','DD/MM/YYYY HH24:MI'));

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> explain plan for
2 select * from test
3 where to_timestamp_determinista(dt)<
4 to_timestamp_determinista(to_date('07/01/2014 14:00','DD/MM/YYYY HH24:MI'));

Explicado.

SQL> @?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------
Plan hash value: 2253730852

-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5378 | 231K| 7 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 5378 | 231K| 7 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_FB_TEST_FECHA | 968 | | 4 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

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

2 - access("SYS"."TO_TIMESTAMP_DETERMINISTA"(INTERNAL_FUNCTION("DT"))<"TO_TIMESTAMP_DETERMINISTA"(TIMESTAMP' 2014-01-07 14:00:00'))

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