Index on a Timestamp Column Cannot be Accessed Properly (Doc ID 1321016.1)

Last updated on AUGUST 30, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 9.2.0.1 and later
Information in this document applies to any platform.

Symptoms

Index on a timestamp column can not be accessed when the column is compared with a "systimestamp" value.
For example with the following setup:

SQL> create table table_timestamp (id1 number,idtime timestamp);  

Table created.


SQL> desc table_timestamp
 Name                                       Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID1                                                NUMBER
 IDTIME                                             TIMESTAMP(6)


SQL> insert into table_timestamp
select object_id,systimestamp from dba_objects
where object_id<10000   ;

8754 rows created.

SQL> commit;

Commit complete.

SQL> create index idx_stamp on table_timestamp(idtime);

Index created.

SQL> select index_name,index_type from user_indexes where table_name='TABLE_TIMESTAMP';

INDEX_NAME                     INDEX_TYPE
------------------------------ ---------------------------
IDX_STAMP                      NORMAL

SQL> exec dbms_stats.gather_table_stats('ROGER','TABLE_TIMESTAMP');

PL/SQL procedure successfully completed.


The following occurs when the timestamp column is compared with a systimestamp value:

SQL> set autot trace exp
SQL> select id1 
from TABLE_TIMESTAMP 
where idtime > systimestamp;

Execution Plan
----------------------------------------------------------
Plan hash value: 1640507595

-------------------------------------------------------------------------------------
| Id  | Operation         | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                 |   438 |  6132 |    10  (20)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TABLE_TIMESTAMP |   438 |  6132 |    10  (20)| 00:00:01 |
-------------------------------------------------------------------------------------

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

   1 - filter(SYS_EXTRACT_UTC(INTERNAL_FUNCTION("IDTIME"))
       >SYS_EXTRACT_UTC(SYSTIMESTAMP(6)))  <======= transformation takes place



Looking at the predicates, the original predicate:

idtime > systimestamp;


Is converted to :

(SYS_EXTRACT_UTC(INTERNAL_FUNCTION("IDTIME"))>SYS_EXTRACT_UTC(SYSTIMESTAMP(6)))



Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms