Re: Index on timestamp with timezone AT LOCAL

From: Robert Klemme <shortcutter_at_googlemail.com>
Date: Fri, 06 Nov 2009 22:39:43 +0100
Message-ID: <7ljjcvF3ddhk7U1_at_mid.individual.net>

On 11/06/2009 04:12 PM, Mark D Powell wrote:

> 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

It seems TIMESTAMP WITH LOCAL TIME ZONE seems the most appropriate type if the query is to show timestamps in session timezone most of the time.

Kind regards

        robert

-- 
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/

Received on Fri Nov 06 2009 - 15:39:43 CST