performance problem querying against a "TIMESTAMP WITH TIME ZONE" column
Hello, I've got a fairly large log table (2.5M rows) with a time column of type "TIMESTAMP(6) WITH TIME ZONE".
DB vesion: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Queries that filter that column have a horrible performance
query example:
SELECT COUNT(1) FROM crn_log x WHERE x.log_time > CURRENT_TIMESTAMP - 1;
using DBMS_XPLAN.DISPLAY I see the following
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(SYS_EXTRACT_UTC(ORA_DST_CONVERT(INTERNAL_FUNCTION("X"."LOG
_TIME")))>SYS_EXTRACT_UTC(CURRENT_TIMESTAMP(6)-1))
I understand that indexes on such columns will really be function-based indexes that use SYS_EXTRACT_UTC, but why ORA_DST_CONVERT(INTERNAL_FUNCTION())?
I googled and searched the forums and found nothing. It seems to me that some kind of implicit conversion is ruining the performance.
Copying the data to another table and changing the data type to "TIMESTAMP" makes a similar query run in 0.01s, so there's obviously something wrong with the TIME ZONE.
What is the proper way to query the original data?
Thanks in advance