Re: Partitioning on Timestamp with Time zone column in 10g

From: Bobby Z. <vladimir.zakharychev_at_gmail.com>
Date: Sat, 14 Mar 2009 11:33:20 -0700 (PDT)
Message-ID: <287c806a-d1d5-4d14-a5cc-e2371c500de7_at_c11g2000yqj.googlegroups.com>

On Mar 13, 8:57 pm, GTSP Inc <suresh..._at_gmail.com> wrote:
> Hi
>
> Is partitioning on Timestamp with Time zone data type possible in 10g?
> WHen I try the following it reports ORA-03001 unimplemented feature.
> The DW guide  illustrates partitioning only on DATe columns and there
> is no specific mention of this data type.
>
> create table testtbl
> (id number,
>  tm_date timestamp with time zone
> )
> partition by RANGE (tm_date)
> (
> PARTITION DEC_2008 VALUES LESS THAN (TO_TIMESTAMP_TZ('2009-01-01
> 11:00:00 -8:00','YYYY-MM-DD HH:MI:SS TZH:TZM'))
> )
> NOCOMPRESS
> NOCACHE
> NOPARALLEL
> MONITORING;
>
> But it works for TIMESTAMP WITH LOCAL TIMESTAMP data type column
>
> create table testtbl
> (id number,
>  tm_date timestamp with local time zone
> )
> partition by RANGE (tm_date)
> (
> PARTITION DEC_2008 VALUES LESS THAN (TIMESTAMP'2009-01-01 00:00:00
> +0:00')
> )
> NOCOMPRESS
> NOCACHE
> NOPARALLEL
> MONITORING;
>
> Thanks for your inputs,
>
> Suresh

I think this is because time zone component makes the timestamps incomparable directly unless they are all converted to, say, UTC before being compared, which Oracle doesn't seem to be able to do automatically. Timestamps with local time zone are easily comparable because they are all in the same database time zone, which is guaranteed to be the same for the life of the database.

Regards,

   Vladimir M. Zakharychev
   N-Networks, makers of Dynamic PSP(tm)    http://www.dynamicpsp.com Received on Sat Mar 14 2009 - 13:33:20 CDT