Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Fri, 24 Jun 2011 @ 10:23:42 GMT

  <Prev Next>   <<First <Prev Next> Last>>  

Subj:   Re: MLPPI on date field
From:   Dieter Noeth

Anomy.Anom wrote:

  I have a table in which there is a date field and I have data starting from 1900 to till date. Now I want this date field to be partitioned first on year basis and then on month basis.  

  i.e. I should have 1 partiton for each year between 1900 and 2011. And again in this, I would like to have sub partition on month. Can some one help me with the syntax.  

You probably could do:

     partition by (
        range_n (extract(year from date_col) between 1900 and 2011 each 1),
        range_n (extract(month from date_col) between 1 and 12 each 1))

But you don't need to partition like that :-)

You can simply use:

     partition by range_n
        (date_col between date '1900-01-01' and date '2011-12-31'
         each interval '1' month)

When you access one year you'll scan those 12 monthly partitions either way.

The performance will always be the same whether you access one year of data

- in a single yearly partiton

- in 12 monthly partitions

- in 365/366 daily partitions


  <Prev Next>   <<First <Prev Next> Last>>  
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023