SYMPTOM

Index on timestamp is not used when compared with systimestamp value.

SQL> desc tets_tbl
Name         Null?    Type
------------ -------- ----------------------
...
..
EXPIRY_TIME          TIMESTAMP(6)
...
..

Create an index on expiry_time column, and see the index is not used.

SQL>CREATE INDEX TEST_TBL_IDX1 ON TEST_TBL (EXPIRY_TIME);
SQL>SET AUTOT trace exp;
SQL> select * from test_tbl where EXPIRY_TIME<systimestamp;

Execution Plan
----------------------------------------------------------
Plan hash value: 692824678
---------------------------------------------------------------------
| Id  | Operation         | Name   | Rows|Bytes |Cost(%CPU)|Time
---------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        | 8147| 1630K| 1347  (1)|00:00:17
|*  1 |  TABLE ACCESS FULL|TEST_TBL| 8147| 1630K| 1347  (1)|00:00:17 
----------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(SYS_EXTRACT_UTC(INTERNAL_FUNCTION("EXPIRY_TIME"))< 
SYS_EXTRACT_UTC(SYSTIMESTAMP(6)))

CAUSES

TIMESTAMP datatype contains the datetime fields YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, TIMEZONE_HOUR, and TIMEZONE_MINUTE. It has contains fractional seconds but does NOT have a time zone.

SYSTIMESTAMP returns the system date, including fractional seconds and time zone, of the system on which the database resides. The return type is TIMESTAMP WITH TIME ZONE.

SOLUTION

1) Create a function index:

SQL> create index test_tbl_idx2_fn on test_tbl( sys_extract_utc(expiry_time));

Index created.

SQL> select * from test_tbl where EXPIRY_TIME<systimestamp;

Execution Plan
----------------------------------------------------------
Plan hash value: 3272729254
--------------------------------------------------------------------------
| Id  | Operation                   | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 |SELECT STATEMENT            |                    |  8147 |  1630K|  1224   (0)| 00:00:15 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_TBL         |  8147 |  1630K|  1224   (0)| 00:00:15 |
|*  2 |   INDEX RANGE SCAN          | TEST_TBL_IDX2_FN |  1467 |       |     7   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

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

   2 - access(SYS_EXTRACT_UTC(INTERNAL_FUNCTION("EXPIRY_TIME"))< 
SYS_EXTRACT_UTC(SYSTIMESTAMP(6)))

OR

2) Define the column as “TIMESTAMP WITH TIME ZONE” instead of “TIMESTAMP”, then a function index is created automatically for this column.

OR

3) Rewrite  query by using sysdate instead of systimestamp:

SQL> select * from test_tbl where EXPIRY_TIME<sysdate-1;

Execution Plan
----------------------------------------------------------
Plan hash value: 205107284

-------------------------------------------------------------------------
| Id | Operation                  | Name         | Rows | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT            |               | 1    | 205   | 4 (0)      | 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | TEST_TBL      | 1    | 205   | 4 (0)      | 00:00:01 |
|* 2 | INDEX RANGE SCAN           | TEST_TBL_IDX1 | 1    |       | 3 (0)      | 00:00:01 |
--------------------------------------------------------------------------