0 - 38 of 38 tags for ppi

Hello All,
What is the query to drop and add CASE_N and KEY for PPI?.
I was able to drop and add range_n whereas when i tried for CASE_N and KEY i was not able to either add or drop the PPI.
Thanks in Advance

For a PPI table (with a NUPI), is it possible to load data concurrently to the same table via multiple sessions by the same user ? In other words, in a PPI table does TD lock only a particular partition for loading or the entire table is locked ?
For e.g. we have the following table :
Create table test_concur

We have a set table with NUPI and is partitioned with date. My question is when we try inserting a new record based on row hash it will perform duplicate checking or it will go to particular partition and perform duplicate check?
Ex: in one AMP
Primary Index(ex: ID1): 10
Partition 1 with date 2015-01-01: 100 rows

We are working on extending the PPI ranges in our tables,so i am working on automated script.For this i need to capture following information
1. Can i get partition range  & partition column information from any sytem tables?

Hi All and a Happy New Year.
We have a new requirement to place a Join Index (that also happens to be ML-PPI) over two tables to aid performance for a Tactical Workload. The JI works well with excellent response times for a very large dataset. (One Table is 1.5Tb and the other 330Mb) The JI is 1.3Tb.

can we join PPI table to NON PPI table?, What is the behavioural of the table?, which Join we have to use to do this? 
Can any one explain this?

Hello All,
How to determine columns, for which we like to create JOIN INDEX,Collect Statistics or PPI?.
Plz Help.

Hi All,
Can we create PPI on Compressed columns?
If yes, pls share me link where i can get info or material.

Hello ,
I have a n00b question, I wanted to know whether its possible to specify names for the individual partitions that we create in the CREATE TABLE statement.
For example, is something like this possible (?) :


Hi All,
I have a table that has PI consisting of Col_A and Col_B. Col_C is a timestamp(6) field which is a PPI.
As per the current data, the table has a skew factor of 75%.
Therefore, I created an identical table, this time with PI consisting of Col_A, Col_B and Col_C, with Col_C again as PPI.

Hi All,
I have a table that has PI consisting of Col_A and Col_B. Col_C is a timestamp(6) field which is a PPI.
As per the current data, the table has a skew factor of 75%.
Therefore, I created an identical table, this time with PI consisting of Col_A, Col_B and Col_C, with Col_C again as PPI.

Can you please let me know the query to have dynamic query to build an alter statement to incorporate the new narrow partition values for the new year(PPI are to be changed to incorporate new year), which otherwise will reside in wider window.
for eg.  if the partitions are defined to carry the recent data for

Hi Teradatares :-)
I am new to Teradata database. Currently I am thiking of creating "big" table that should grow 200 mln rows each week. After a year data it will be 10 000 000 000 rows
After reading some best practice, documentation and forum, that's what I kind of understand.
Create table test

Hi Experts,
Can you pls  give your suggestions for the below requirement-->



There is a table which would be having history of 24 months of data at any point of time.


Hello all,
we hav a large table (CDRs) with PPI defined on timestamp containing date & time when a record was loaded in DW.

I am trying to add a range to my CPPI column but get this error when I run the alter table statement:

Why can we have UPI only when PPI column is part of PI, can someone explain in detail?
Thanks in Advance

Hi All,


I have the following table defined :



p_num bigint

p_dt date

p_car_num varchar(100)


primary index (p_num) 


Hi All,

I have a PPI table defined as like below,

Hi everyone..
Well, I have a table with the following structure and I need to add  a new RANGE by adding the field People_Subscription_Start_DT:
PARTITION BY RANGE_N (People_Subscription_Start_DT DATE '2008-09-01' AND DATE '2013-12-31' EACH INTERVAL '1' DAY, NO RANGE);

This session provides information on the basic concepts of Partitioned Primary Indexes.

We have a history table which is partitioned on loadDate. This table also has another column called ProcessYearMonthNumber which is the year and month number of the loadDate. I know this can be derived on the fly but for some reason we have this as a separate column with values populated by ETL.

I'm new to Teradata and have a question on creating a table with a CPPI.
I have a table with a few hundered million rows. I want to create partitioning on a column called SAP_CODE whose structure is always four digits. The first digit is alphanumeric the last 3 digits are numeric, e.g. S001, M123, Z012.

Hi Folks 
I have 2 tables with same PI columns . Both are M-set tables but the target has a PPI on one of the columns.
I am doing an Ins - sel from non PPI to PPI table everything else being the same. The table PI has 5 cols of which the PPI is on the date column.

I need to extract the tablenames and its corresponding column names(on which PPI has defined) where PPI is used in the table definition. Any inputs on this please.

In my blog entries "Teradata Columnar" and "9.2 Quintillion? What's that all about?", I discussed column partitioning and the increased partition limit that are introduced in TD 14.0.  But there are other TD 14.0 partitioning enhancements that you may find useful.  The following provides a brief introduction to these enhancements; for more detailed information, see the Orange Book: Increased Partition Limit and other Partitioning Enhancements, the Orange Book: Teradata Columnar, and the TD 14.0 manuals.

With row partitioning (for a PPI or column-partitioned table), the Teradata Database makes sure rows are placed in their appropriate partitions.  When the row partitioning for the table is altered, rows may need to move from one partition to another so they are in their appropriate partitions according to the altered partitioning.  The partitioning expression must be deterministic (always computes the same value for the same value of the partitioning column) to properly place and retrieve the row.

For instance if the table has RANGE_N partitioning, ALTER TABLE will move rows automatically from NO RANGE to newly-added ranges as needed.  Also, if a range is dropped, rows from that partition are moved to the NO RANGE, NO RANGE OR UNKNOWN partition or a newly-added range that covers the dropped range.

We have a large transaction table for a global implementation is being created that has the columns Region_Id, Location_Id, and Transaction_Dt. The table contains five years of data and the filters will primarily be on the columns identified. In reviewing the data demographics, below things were observed:
• The Region_Id has 10 distinct values
• The Location_Id has 2,000 distinct values
• The Transaction_Dt typically has 200,000 rows per value

What is the optimal implementation in terms of request performance?



Wish you all a very Happy New Year!

I have the following question regarding PPI and Left joins in Teradata.

I am joining tableA and tableB as below. Both the tables are partitioned on the joining column Col1.

Teradata Database 14.0's new partition limit of 9223372036854775807 (over 9 quintillion) is coming!

Hi gurus,

How does Teradata store/retrieve Partition ID/number information?

Consider the scenario where a query/insert statement specifies the PI and Partition information (PI contains the column used in partition - simply say a Date column)


Is there any way to partition directly on a small (ie. CHAR(3)) character column, without defining a CASE_N or RANGE_N for the column (ie. partition on all unique values of the column)?

Is there any other way of reliably tell the optimizer to use the PPI other than hard-coding the minimal/maximal value (date) in a following case?

Here is a DDL for a big table with customer transactions:

PRIMARY INDEX NUPI_txn ( transaction_id )
INDEX NUSI_customer_id( customer_id)

I was recently asked about NO RANGE, UNKNOWN, and NO RANGE OR UNKNOWN partitions for the RANGE_N function.  While these have been available since TD V2R5.0, there is sometimes confusion about what they mean and when to use or not use them.

This is more than an overview of the Indexes that are available in Teradata and how they work.


The most obvious use for the partitioning would be DATE. Based on the kind of queries we currently receive, the ranging would be something like.

Current Date to 30 days ago, split by DAY
1 month ago to 12 months ago, split by Month

I know this can be done.

My questions are regards to keeping this range a moving window.

When defining a PPI on a date column, would it be better to define your partitions so that fewer (and larger) partitions are accessed by the query or would it be better to define your partitions so that more (but smaller) partitions are accessed?

I still remember when PPI was first introduced in V2R5 and all the questions that came up in my mind in terms of utilization and performance impact. Long time has passed and most of the Teradata systems nowadays have at least one large PPI table defined.