we are saving UTC timezone time into a oracle table. The column is defined as TIMESTAMP only.
We insert UTC time into this column.
We need to query to select all records which has a specific key value and timestamp less than the current UTC time.
select * from TABLE
where KEY_COLUMN ='VALUE' and DATE_COLUMN < SYSTIMESTAMP AT TIME ZONE 'UTC' ;
We have composite index on KEY_COLUMN and DATE_COLUMN.
It's using range scan on this index but it is using only KEY_COLUMN to access and using DATE_COLUMN for filter.
16 - access("KEY_COLUMN"='VALUE')
filter(SYS_EXTRACT_UTC(INTERNAL_FUNCTION("DATE_COLUMN"))<SYS_EXTRACT_UTC(SYSTIMESTAMP(6) AT TIMEZONE 'UTC'))
As you see above, oracle applying an INTERNAL_FUNCTION.
I understand that TIMESTAMP datatype column doesn't save TIMEZONE, so my input will be TIMESTAMP WITH TIMEZONE as i am using SYSTIMESTAMP AT TIME ZONE 'UTC' .
What will be the best solution to use index effectively ?
This is within pl/sql procedure, can i use a variable of type timestamp and use it for query ?
date_variable timestamp ;
select SYSTIMESTAMP AT TIME ZONE 'UTC' into date_variable ;
select * from TABLE
where KEY_COLUMN ='VALUE' and DATE_COLUMN < date_variable ;