Partitioned Primary Index (PPI)

Partitioned Primary Index (PPI)

Partitioned Primary Index is one of the unique features of Teradata, which allows access of portion of data of large table. This reduces the overhead of scanning the complete table thus improving performance. 

PPI works by hashing rows to different virtual AMPs, as is done with a normal PI.  PPI does not alter data distribution, it only creates partitions on data already distributed based on PI.

Usually PPI's are defined on a table in order to increase query efficiency by avoiding full table scans without the overhead and maintenance costs of secondary indexes.

Partitions are usually defined based on Range or Case as follows.

Two functions, RANGE_N and CASE_N, can be used to simplify the specification of a partitioning expression.

1. Partition by CASE

CREATE      TABLE ORDER_Table

(

ORD_number  integer NOT NULL,

customer_number integer NOT NULL,

order_date  date ,

order_total integer

)

PRIMARY INDEX (customer_number)

PARTITION BY case_n (

            order_total < 10000 ,

            order_total < 20000 ,

            order_total < 30000,

NO           CASE     OR        UNKNOWN ) ;

2. Partition by Range - example using date range

CREATE TABLE ORDER_Table

(

ORD_number  integer NOT NULL,

customer_number integer NOT NULL,

order_date  date ,

order_total integer

)

PRIMARY INDEX (customer_number)

PARTITION BY range_n (

            Order_date BETWEEN date '2010-01-01'       AND      date '2010-12-01'

            EACH interval '1' month,

NO RANGE

        OR  UNKNOWN);

P.S: If we use NO RANGE or NO CASE - then all values not in this range will be in a single partition.

If we specify UNKNOWN, then all null values will be placed in this partition

PPI improves performance as follows:

• Automatic optimization occurs for queries that specify a restrictive condition on the partitioning column.
• Uses partition elimination to improve the efficiency of range searches when, for example, the searches are range partitioned.
• Only the rows of the qualified partitions in a query need to be accessed avoid full table scans.
• Provides an access path to the rows in the base table while still providing efficient join Strategies
• If the same partition is consistently targeted, the part of the table updated may be able to  fit largely in cache, significantly boosting performance
• PPI based tables have advantage during Purging stages. Since purging based on partition is very fast and deletion happens quickly.

PPI also comes with some disadvantages like

• The PI access disadvantage occurs only when the partitioning column is not part of the PI. In this situation, a query specifying a PI value, but no value for the partitioning column, must look in each partition for that value, instead of positioning directly to the first row for the PI value.
• Another disadvantage is that when another table (without PPI) is joined with PPI table on PI=PI condition. If one of the tables is partitioned, the rows won't be ordered the same, and the task, in effect, becomes a set of sub-joins, one for each partition of the PPI table. This type of join is sliding window join

Limitations of Partitioned Primary Index (PPI) :

•  Primary index of PPI table has to be 
• Non unique PI, if PPI column is not part of Index, since enforcing a Unique PI would require checking for a duplicate key value in each partition, which would be very expensive.
•  Primary Index of PPI table can be Unique, if PPI is part of UPI. This will result in checking for unique constraint in same partition.
•  PPI cannot be defined on Global temporary tables and Volatile tables and also on compressed join indices
• PPI Table rows occupy two extra bytes compared to NPPI table row, as these extra bytes store the partition number for each row .
• PPI table rows are four bytes wider if value compression is specified for the table. 

Since PPI , results in lot of partitions , there is a little overhead to user/dba.  He has to regularly run collect stats on the PPI column.

It is beneficial to collect stats on Partition column .Collecting stats on the system derived column Partition is faster because rather than reading all the base table rows for collecting information, it usually just scans the cylinder index for that PPI table.By doing so, it avoids all unnecessary partitions , thus speeding up the access.

Help stats tablename column PARTITION; -- used to list partitions in table and their details

Collect stats on tablename column PARTITION; -- refresh partition details

Journal’s in Teradata

Journal’s in Teradata

1. Transient Journal - This maintains current transaction history. Once the query is successful it deletes entries from its table. If the current query transaction fails, It rolls back data from this table.

2. Permanent Journal - This is defined when a table is created. It can store BEFORE or AFTER image of tables. Both BEFORE and AFTER copy can also be maintained as DUAL Journal.

Permanent Journal maintains complete history of table. Permanent journal is maintained for critical table and a database can have one permanent journal, Permanent journal is used basically for Point-in-time recovery in case of accidental data loss, and this resembles redo mechanism in oracle.

3. Down AMP recovery Journal (DARJ) - Down AMP recovery Journal activates automatically when a AMP goes down. It is used with fallback protected table to maintain any change made on table during AMP is unavailable. When failed AMP is brought online the restart process applies all the changes to recovered AMP using DARJ.

Getting Previous Row Data in Teradata using OLAP Teradata Function

Getting Previous Row Data in Teradata using OLAP Teradata Function

"ROWS UNBOUNDED PRECEDING" is used in an ordered analytical function to tell it to include all of the preceding rows in the partition in the calculation being performed.

 “ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING” is used in an ordered analytical function to tell it to include only preceding row in the partition in the calculation being performed.

“ROWS BETWEEN 1 FOLLOWINGAND 1 FOLLOWING” is used in an ordered analytical function to tell it to include only following row in the partition in the calculation being performed.

Then Following example illustrates OLAP Functions Usage:

CREATE MULTISET TABLE TEMP1 (COL1 INT, COL2 INT);

INSERT INTO TEMP1 VALUES(100 , 2000);

INSERT INTO TEMP1 VALUES(100 , 3000);

INSERT INTO TEMP1 VALUES(100 , 4000);

INSERT INTO TEMP1 VALUES(300 , 5000);

INSERT INTO TEMP1 VALUES(300 , 6000);

INSERT INTO TEMP1 VALUES(300 , 7000);

SELECT  COL1

,COL2

,COALESCE(MIN(col2) OVER (PARTITION BY col1  ORDER BY col2 ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING), 0) A1

,COALESCE(MAX(col2) OVER (PARTITION BY col1  ORDER BY col2 ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING), 0) A2

,COALESCE(MIN(col2) OVER (PARTITION BY col1  ORDER BY col2 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), 0) A3

,COALESCE(MAX(col2) OVER (PARTITION BY col1  ORDER BY col2 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), 0) A4

,COALESCE(SUM(col2) OVER (PARTITION BY col1  ORDER BY col2 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), 0) A5

,COALESCE(AVG(col2) OVER (PARTITION BY col1  ORDER BY col2 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), 0) A6

,COALESCE(COUNT(col2) OVER (PARTITION BY col1  ORDER BY col2 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), 0) A7

FROM TEMP1;