All Forums Database
ayes 24 posts Joined 02/06
29 Oct 2007
Partition Primary Index(PPI) and PI

Hi ,I have read - You can not have a UNIQUE PRIMARY INDEX on a table that is partitioned by something not included in the Primary Index.Please explain me the logic behind this?Regards,Ayes

joedsilva 505 posts Joined 07/05
29 Oct 2007

Please refer this thread.http://www.teradata.com/teradataForum/Topic6553-9-1.aspx

30 Oct 2007

An example from the Teradata SQL DDL manual.Example 15: Partitioned NUPI Specification With a USI On the Same ColumnSet and Partitioned By a RANGE_N ExpressionThe following example creates a NUPPI on o_orderkey and a USI on the samecolumn set. The statement bases its partitioning expression on the RANGE_Nfunction on o_orderdate.The PPI cannot be defined as unique because its partitioning expression isbased on o_orderdate and that column is not included in the primary indexcolumn set.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_orderdate BETWEEN DATE '1992-01-01' AND DATE '1998-12-31'EACH INTERVAL '1' MONTH)UNIQUE INDEX (o_orderkey);

Ramakrishna Vedantam, Tata consultancy services, Hyderabad

redravi207 2 posts Joined 07/11
22 Jul 2011

Explanation:
If we have a data like :
E_NAME DEPT_NO
=============
Ganesh 10
Ravi 20
Kale 20
Prashant 30
Ganesh 20

Here say Primary Index is E_NAME and this table is partitioned by DEPT_NO

So is it possible here to have a UPI defined on E_NAME?
Ans is NO, because we dont have unique values with E_NAME column and if we include the DEPT_NO column here with Primary Index then it will be definately unique.
So this is the one of reason that we can not have a UNIQUE PRIMARY INDEX on a table that is partitioned by something not included in the Primary Index.

To the best of my knowledge
--Ganesh.

emaillenin 4 posts Joined 04/13
18 Apr 2013

The link mentioned in the 2nd reply of this thread does not exist - http://www.teradata.com/teradataForum/Topic6553-9-1.aspx
Please provide the updated link.

-- Lenin, www.emaillenin.com

You must sign in to leave a comment.