Using COLUMNAR in the ddl of a table and then find 'add 10' in the table DDL added

Database
Highlighted
Enthusiast

Using COLUMNAR in the ddl of a table and then find 'add 10' in the table DDL added

For Example:

If you execute the following DDL to create the table:

CREATE TABLE SALES (
TxnNo INTEGER,
TxnDate DATE,
ItemNo INTEGER,
Quantity INTEGER )
PARTITION BY (
COLUMN,
RANGE_N(TxnDate BETWEEN
DATE '2011-01-01' AND DATE '2011-12-31' EACH INTERVAL '1' DAY) ),
UNIQUE INDEX (TxnNo);


Then show table defenition, you will find the defenition as following:


CREATE MULTISET TABLE recoverydb.test ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO
(
TxnNo INTEGER,
TxnDate DATE FORMAT 'yyyy-mm-dd',
ItemNo INTEGER,
Quantity INTEGER)
NO PRIMARY INDEX
PARTITION BY ( COLUMN ADD 10,RANGE_N(TxnDate BETWEEN DATE '2011-01-01' AND DATE '2011-12-31' EACH INTERVAL '1' DAY ) )
,UNIQUE INDEX ( TxnNo );


Notice that there is an 'ADD 10' after the COLUMN in the DDL...what does that refer to?

Thanks for your reply,

Accepted Solutions
rjg
Supporter

Re: Using COLUMNAR in the ddl of a table and then find 'add 10' in the table DDL added

Mohamed,

 

 

If adding partitions for that level 10 is the default limit using alter table.

 

Additional info:

"Orange Book: Increased Partition Limit and other Partitioning Enhancements" for more information on the ADD option.

 

Rglass

1 ACCEPTED SOLUTION
3 REPLIES
rjg
Supporter

Re: Using COLUMNAR in the ddl of a table and then find 'add 10' in the table DDL added

Mohamad,

 

 

If adding partitions for that level 10 is the default limit using alter table.

 

Additional info:

"Orange Book: Increased Partition Limit and other Partitioning Enhancements" for more information on the ADD option.

 

Rglass

rjg
Supporter

Re: Using COLUMNAR in the ddl of a table and then find 'add 10' in the table DDL added

Mohamed,

 

 

If adding partitions for that level 10 is the default limit using alter table.

 

Additional info:

"Orange Book: Increased Partition Limit and other Partitioning Enhancements" for more information on the ADD option.

 

Rglass

Teradata Employee

Re: Using COLUMNAR in the ddl of a table and then find 'add 10' in the table DDL added

From the DDL manual, in the create-table chapter, this "Specifies that the maximum number of partitions for a partitioning level is the number of partitions it defines plus" 10.  It adds this by default in this case.  If you are sure you do no want or need these extra partitions, you can specify "ADD 0" in the DDL.  The DDL Detailed Topics manual has a long writeup on this phrase, "Rules for Using the ADD Option for the Partitioning Levels of a Column-Partitioned Table."