Sometimes it is the engine itself that adds functions to the filters and that can drive us a bit crazy trying to figure out why Oracle doesn’t use the proper index.
For example:
SQL> create table test (id number, dt date);
Table created.
SQL> insert into test select rownum, to_date('07/01/2014 13:00','DD/MM/YYYY HH24:MI')+rownum/144 from dba_objects;
73318 rows created.
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);
Index created.
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)
This implicit conversion mentioned in INTERNAL_FUNCTION takes us to the FULL SCAN of the TEST table.
The reason is because Oracle cannot establish a direct conversion between TIMESTAMP and DATE. As a function, TO_TIMESTAMP is transforming the values based on the local region and that brings us to the next problem.
Creating an index based on functions wouldn’t work either, since the TO_TIMESTAMP conversion function is not deterministic. Deterministic functions always return the same value to a given parameter pass, but TO_TIMESTAMP relies on local NLS variables like timezone. So in addition to the conversion of the function, globalization allows the values that result from TO_TIMESTAMP to be different if the client has the time settings of Spain or Mexico.
Thus, if we try to create an index based on the TO_TIMESTAMP function, Oracle will return an error indicating that the TO_TIMESTAMP function is not pure. Above all, to create an index where the values that are stored are fixed, the values that a function returns must always be the same for the same input values, because that is how the values within the index must be: always the same results at the same input values of the function.
TO_NUMBER is a pure function. Whenever the string ‘123’ is passed to it the number will be 123.
TO_TIMESTAMP will vary the result depending on the NLS variables.
The ordering of this result is affected, and therefore the data returned by the index would not match. SQL> create index idx_fb_test_fecha on test(to_timestamp(dt));
create index idx_fb_test_fecha on test(to_timestamp(dt)) * ERROR in line 1: ORA-01743: only pure functions can be indexed
And now a dangerous trick.
Let’s assume our database is for local usage (one specific region) and TIMESTAMP will always be queried for the same time zone. In that case, we can create our own deterministic TO_TIMESTAMP function and try to create the index on it, and build queries that may include our function, of course!.
SQL> create or replace function to_timestamp_determinista(fecha timestamp) return timestamp deterministic is 2 begin 3 return to_timestamp(fecha); 4 end; 5 / Function created.
TO_TIMESTAMP will vary the result depending on NLS variables and is not deterministic, but TO_TIMESTAMP_DETERMINIST is defined as if it were, even though it is not.
That is, our TO_TIMESTAMP_DETERMINIST function is considered pure.
Even if it is not, I insist.
SQL> create index idx_fb_test_fecha on test(to_timestamp_determinista(dt)); Index created.
It is now possible for our query to use the function-based index to convert to TIMESTAMP deterministically, and to benefit from using the index to retrieve all 7 rows out of 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'));
Explained.
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)