Index on Timestamp Column in Oracle Database

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

Oracle Database Time Zone

Time Zone concept is critical to global transactions.

1) dbtimezone only shows the database creation time TZ offset from OS environment.
For AEST TZ, it could be either of the below depending on OS environment variable:

00:00
+10:00 ( non DST )
+11:00 ( DST )

SQL> select dbtimezone from dual;

DBTIME
------
+11:00


SQL> select created from v$database;

CREATED
-----------------
20161115-15:06:11    <------ DST

2) timezone file. For Australia, it needs to be greater than 14.

SQL> select * from v$timezone_file;

FILENAME              VERSION 
-------------------- ---------- 
timezlrg_18.dat       18

3) Database server time zone ( TIMESTAMP WITH TIME ZONE ):

SQL> select SYSTIMESTAMP from dual;

SYSTIMESTAMP
-------------------------------------
24-MAY-17 04.12.15.769958 PM +10:00

4) Current session time zone :

SQL> select CURRENT_TIMESTAMP from dual;

CURRENT_TIMESTAMP
-------------------------------------------------
24-MAY-17 02.16.06.660376 PM +08:00 

SQL> select sessiontimezone from dual;

SESSIONTIMEZONE
-------------------------------------------------
+08:00

5) Time zone example :

SQL> create table table_tz ( t1 TIMESTAMP WITH TIME ZONE, t2 TIMESTAMP WITH LOCAL TIME ZONE);

Table created.


SQL> insert into table_tz values (SYSTIMESTAMP,SYSTIMESTAMP);

1 row created.

SQL> select t1,t2 from table_tz ;

T1
----------------------------------------
T2
----------------------------------------
24-MAY-17 04.28.20.428944 PM +10:00
24-MAY-17 02.28.20.428944 PM

SQL> insert into table_tz values (LOCALTIMESTAMP,LOCALTIMESTAMP );

1 row created.

SQL> select t1,t2 from table_tz;

T1
-------------------------------------------------------------
T2
-------------------------------------------------------------
24-MAY-17 04.28.20.428944 PM +10:00
24-MAY-17 02.28.20.428944 PM

24-MAY-17 02.30.26.599416 PM +08:00
24-MAY-17 02.30.26.599416 PM