Index on timestamp with timezone AT LOCAL
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