Index on timestamp with timezone AT LOCAL

From: Björn Wächter <Bjoern.Waechter_at__NOSPAM_web.de>
Date: Thu, 05 Nov 2009 21:51:28 +0100
Message-ID: <7lgs62F3dvb56U1_at_mid.dfncis.de>



Hi all,

I have a table with a time stamp with timezone column:

CREATE TABLE TEST_TZ
(

  DATE_TIME_TZ TIMESTAMP(3)
);

CREATE INDEX IDX_DATE_TIME_TZ ON TEST_TZ
(DATE_TIME_TZ);
I want to use a view to get the time stamp column at the timezone of the session:

CREATE OR REPLACE FORCE VIEW VI_TEST_TZ
(DATE_TIME_LOCAL)

AS
SELECT
DATE_TIME_TZ AT LOCAL DATE_TIME_LOCAL FROM
TEST_TZ; But the problem is that queries using the time stamp column as a filter like this:

SELECT
DATE_TIME_LOCAL
FROM VI_TEST_TZ
WHERE
DATE_TIME_LOCAL = :T; are not using the index on the column. I tried to force the index usage but with no success. I also tried to use the AT LOCAL in a function based index:

CREATE INDEX SCOTT.IDX_TEST ON SCOTT.TEST_TZ
(SYS_EXTRACT_UTC(DATE_TIME_TZ AT LOCAL))
LOGGING
NOPARALLEL; but than i get an ORA-01743 only pure functions can be indexed. Anyone an idea how i can solve the problem? I don't want to use different columns in the selected columns and in the where statement. I have to keep downward compatibility.

Thanks Björn Received on Thu Nov 05 2009 - 14:51:28 CST

Original text of this message