Archives of the TeradataForum

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

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

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