Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Wed, 14 Apr 2010 @ 14:27:53 GMT


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


Subj:   Re: PPI on Date and Char data
 
From:   Barner, Eric

Raju,

From the DDL Manual on Multilevel PPI Restrictions.

If you specify more than one partitioning expression in the PARTITION BY clause, each such partitioning expression must consist of either a single RANGE_N or a single CASE_N function; otherwise, the system aborts the request and returns an error to the requestor.

Example:

     CREATE TABLE orders (
     o_orderkey INTEGER NOT NULL,
     o_custkey INTEGER,
     o_orderstatus CHARACTER(1) CASESPECIFIC,
     o_totalprice DECIMAL(13,2) NOT NULL,
     o_orderdate DATE FORMAT 'yyyy-mm-dd' NOT NULL,
     o_orderpriority CHARACTER(21),
     o_clerk CHARACTER(16),
     o_shippriority INTEGER,
     o_comment VARCHAR(79))
     PRIMARY INDEX (o_orderkey)
     PARTITION BY (RANGE_N(o_custkey BETWEEN 0
     AND 49999
     EACH 100),
     RANGE_N(o_orderdate BETWEEN DATE '2000-01-01'
     AND DATE '2006-12-31'
     EACH INTERVAL '1' MONTH))

So if you want an MLPPI you only have 65,535 total partitions to use The second level partition can only have a max of 25 partitions.

     select cast ( '2015-12-31' as date)  - cast ( '2009-01-01'  as Date) = 2555 Partitions.


     2555  * 25 = 63875

     64000 / 25 = 2560 (Numbers of values in each partition)

Take the following Example, if you modified your code similar the following it would work I suppose, but I am not sure that is what you want.

     drop   table  t1;
     create table  t1
     (
     DT_Col date,
     Char_col char(10),
     val integer)
     Primary Index (DT_COl)

     PARTITION BY (
     RANGE_N
     (DT_Col BETWEEN DATE '2009-01-01' AND DATE '2015-12-31'
     EACH INTERVAL '1' MONTH),
     RANGE_N (HASHBUCKET(HASHROW( Char_col)) MOD 64000 BETWEEN 0
     AND 64000
     EACH 2600 )
     )

Regards,

Eric W. Barner



     
  <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