Re: Index on timestamp with timezone AT LOCAL

From: Mark D Powell <Mark.Powell2_at_hp.com>
Date: Fri, 6 Nov 2009 07:12:32 -0800 (PST)
Message-ID: <4272a464-0923-4106-9109-90feadf5a717_at_m38g2000yqd.googlegroups.com>

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
> (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

HTH -- Mark D Powell -- Received on Fri Nov 06 2009 - 09:12:32 CST