[DB] Teradata PPI(Partitioned Primary Index) 優化策略教學

        近日在使用Teradata時常感覺校能不如預期(前公司的TD太強了也是原因)，但是硬體沒辦法改變，只好從其他地方調整校能。Teradata是分散式儲存的資料倉儲，一些index的方式和一班資料庫不同，沒有Primary Key而是使用Primary Index(簡稱PI)來決定資料散佈的方式，當PI值越離散，資料也會越平均的分布在每個AMP上，下Query時，可以每個AMP同時平行的的處理資料，來加快查詢速度。但是因為資料量時在太大，若要再加快存取時間，就要減少找尋資料的時間，避免Full Table Scan，因此另外使用PPI，來優化查詢過程。
        PPI的原理簡單來說跟上圖一樣，將每個AMP中的資料依據PPI排序分組，這樣查詢時如果有指定PPI，就可以直接到該位置查找資料，避免不必要的時間浪費。PPI通常有兩種定義方式：

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

(範例來源：http://www.teradatahelp.com/2011/03/partitioned-primary-index-ppi.html)

用來做為PPI的欄位通常為一連續數值或是日期。通常我們的使用情境就是查找某一天或某個時間範圍內的資料時，加上PPI可已大幅提升查找效率。就算是想查全部的月份，在考量到暫存空間的情況下，也會使用PPI一次查找一個月的資料後，再將資料合併，速度也比一次查找所有日期還快。

可惜偏偏事情沒那麼單純，不是只有單純單一表格的查找，最大的麻煩在於Join。在Teradata中有兩個效能殺手：Full table scans和redistribution，任何策略思考都要先盡量避免這兩件事情發生。偏偏Join時，這兩種狀況很容易一起發生。

• PPI要不要放在PI中?

CREATE TABLE ORDER_HEAD
(
  ORDER_NO INTEGER
, ORDER_DT DATE
) UNIQUE PRIMARY INDEX (ORDER_NO, ORDER_DT)

PARTITION BY RANGE_N(ORDER_DT BETWEEN DATE '2010-01-01' AND DATE '2013-12-31' EACH INTERVAL '1' DAY , NO RANGE, UNKNOWN)

CREATE TABLE ORDER_ITEM
(
  ORDER_NO INTEGER
, ORDER_ITEM_NO
, PROD_NO INTEGER
) PRIMARY INDEX (ORDER_NO);

範例來自http://teradatatuning.blogspot.tw/2013/12/using-partitioned-primary-index.html

考量上面這個簡單範例，如果ORDER_HEAD這個Table沒有將ORDER_DT放入PI時，ORDER_HEAD和ORDER_ITEM在ORDER_NUMBER的分佈會是一樣的。TD可以在同一個AMP上join，不需要將資料重新分佈。 但是另外一方面，TD在JOIN時會需要將資料重新排序，而有PPI的TABLE原本是依照PPI排序，這時另外一個沒有PPI的TABLE就必須一一去和每個PARTTION裡的資料JOIN(在TD中稱為Sliding window merge join)，效率也會比較差。

反過來，如果ORDER_HEAD將DT放入PI中，資料分佈就會和ORDER_ITEM不同，要做JOIN就需要重新分配資料，AMP之間傳資料(或適任何分散式系統)都是很花功夫的。

• PPI TABLE JOIN NPPI TABLE

http://teradatatuning.blogspot.tw/2013/12/using-partitioned-primary-index.html整理了幾種表格合併的狀況如下：

T2 T1	PI:(a)	PI:(a) PART(b)	PI:(a,b) PART(b)
PI:(a)	Join: T1.a=T2.a RowHash match
PI:(a) PART(b)	Join: T1.a=T2.a T1 sorted by hash(a) or Sliding-window MJ	Join: T1.a=T2.a T1&T2 sorted by hash(a) or Sliding-window MJ (NxM combinations) Join: T1.a=T2.a and T1.b=T2.b T1&T2 sorted by RowKey RowKey based MJ
PI:(a,b) PART(b)	Join: T1.a=T2.a T1 Redistributed & sorted by hash(a)	Join: T1.a=T2.a T1 Redistributed by hash(a) T2 sorted by hash(a) and MJ Join: T1.a=T2.a and T1.b=T2.b T2 Redistributed and sorted by RowKey RowKey based MJ	Join: T1.a=T2.a and T1.b=T2.b RowKey based MJ

雖然最好的情況就是兩個表格的資料分佈和partition都一致，但是世界上哪裡有那麼好的事情.。如果有常用的表格時常JOIN的話，不如就另外存成table，雖然占空間，但是在速度上是絕對沒問題的XD