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);
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.