Partition Primary Index(PPI) and PI

Database
Enthusiast

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

4 REPLIES
Enthusiast

Re: Partition Primary Index(PPI) and PI

Please refer this thread.

http://www.teradata.com/teradataForum/Topic6553-9-1.aspx
Enthusiast

Re: Partition Primary Index(PPI) and PI

An example from the Teradata SQL DDL manual.

Example 15: Partitioned NUPI Specification With a USI On the Same Column
Set and Partitioned By a RANGE_N Expression
The following example creates a NUPPI on o_orderkey and a USI on the same
column set. The statement bases its partitioning expression on the RANGE_N
function on o_orderdate.
The PPI cannot be defined as unique because its partitioning expression is
based on o_orderdate and that column is not included in the primary index
column 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);

Re: Partition Primary Index(PPI) and PI

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.

Re: Partition Primary Index(PPI) and PI

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.