PPI

Database
Enthusiast

PPI

Hello Experts, Please help me

I have problem with UPI, i defined UPI has customer_number,order_date.

My questions:

1)Why PPI allows UPI for partition?

2)Here, Why order_date will allow dupulication?

Here is my table

CREATE TABLE Order_Table

(

Order_Number Integer NOT NULL

,Customer_Number Integer NOT NULL

,Order_Date Date

,Order_Total Decimal (10,2)

)

UNIQUE PRIMARY INDEX

(Customer_Number, Order_Date)PARTITION BY Range_N

(Order_Date BETWEEN DATE ‘2013-01-01

AND ‘2013-12-31’

EACH INTERVAL ‘7’ DAY);

Thanks& Regards,

mack

6 REPLIES
Enthusiast

Re: PPI

i m not clear what exactly is your doubt.

1) you can define NUPI with PPI as well

2) order_date and customer_number both can have duplicate values, but there combination should be unique.

Enthusiast

Re: PPI

Hi, Pawan Thanks for your response,

Actually i defined UNIQUE PRIMARY INDEX (Customer_Number, Order_Date)

As per our rule, UPI can't be allow in PPI, Eventhough i defined UPI has Order_Date as well as  I have used in partition. Table was created successfully.

While i'm inserting on table below

ins into order_table (101,'mack', current_Date,1000);

row inserted.

ins into order_table(102,'pawan',current_date,2000);

row inserted.

But order_date(UPI) allows the duplication why?

Thanks&Regards,

Mack.

Enthusiast

Re: PPI

your UPI is not the order_date only. It's the combination of Customer_Number and order_date. Since Customer number is different (101 and 102), therefore there is no issue with the records. 

Try this and you will get the error:

ins into order_table (101,'mack', current_Date,1000);

row inserted.

ins into order_table(101,'pawan',current_date,2000);

As per the rules, you cannot have a UPI on a table which is partitioned by something which is not a part of PI. Let's say if you had tried to define only the Customer_Number as UPI and then partitioned on Order_date, that is no possible. For that to work, you would have to define Customer_number as NUPI, even if it is unique. If you want to take the advantage of uniqueness constraint, then also make Customer_number as USI. But keep in mind that Secondary indexes take up perm space so there isa trade-off. 

In your example, since order_date is a part of the PI, you can have UPI on this table. 

I hope this clears your doubt?

Enthusiast

Re: PPI

Thanks for your response mandeep, I got it.

Thanks & Regards,

mack

Enthusiast

Re: PPI

Dera All,

At BISP a free demo class of Teradata is scheduled on Saturday, 25 May at 8:00 AM IST

Below is the link to join the session

https://global.gotomeeting.com/join/183467293

Alos you can registere yourself for training at http://www.bispsolutions.com

Enthusiast

Re: PPI

Please don't post any marketing stuff in any techinal disucssion thread or tehnical forum.. it waste other's time ...