2015年5月17日 星期日

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

用來做為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);

考量上面這個簡單範例,如果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

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