Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

performance problem querying against a "TIMESTAMP WITH TIME ZONE" column

GChiericoMay 31 2012 — edited May 31 2012
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
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 28 2012
Added on May 31 2012
7 comments
4,489 views