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

On Nov 5, 3:51 pm, Björn Wächter <Bjoern.Waechter at (no spam) _NOSPAM_web.de
wrote:
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


What full version of Oracle?
What data type is bind variable :T ?
Did you generate statistics on the Table and Index?

If :T is not declared as a timestamp what happens if you change the
query to the form:

where date_time_local = to_timestamp(:T)


Also there is mention of Time Zone in the post but the Timestamp data
type does not include the time zone. Should you be using one of the
Timestamp data types that does?

From Concepts Manual chapter on native data types
Datatype Time
Zone Fractional Seconds
DATE
No No
TIMESTAMP
No Yes
TIMESTAMP WITH TIME ZONE Explicit Yes
TIMESTAMP WITH LOCAL TIME ZONE Relative Yes