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 and later
Information in this document applies to any platform.


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 
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):

       >SYS_EXTRACT_UTC(SYSTIMESTAMP(6)))  <======= transformation takes place

Looking at the predicates, the original predicate:

idtime > systimestamp;

Is converted to :



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