Re: Index on timestamp with timezone AT LOCAL
Date: Fri, 6 Nov 2009 07:12:32 -0800 (PST)
On Nov 5, 3:51 pm, Björn Wächter <Bjoern.Waechter_at__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
> 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_TZ AT LOCAL DATE_TIME_LOCAL
> But the problem is that queries using the time stamp column as a filter
> like this:
> FROM VI_TEST_TZ
> 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))
> 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
HTH -- Mark D Powell -- Received on Fri Nov 06 2009 - 09:12:32 CST