TERADATA: Primary Partition Indexでパーティション設定

Primary Partition Index(PPI)で検索をさらに早く

TERADATAは、分散したデータに、さらにパーティション設定ができます。

パーティション設定した項目に検索条件を指定すると、フルスキャンではなく該当するパーティションのみスキャンすることができて、性能があがります。

例えば次のテーブルで考えます。
Datestring項目には、YYYYMMDD形式で値がセットされる仕様とした場合に、Datestring項目へ検索条件を指定すると該当のパーティションだけスキャンされます。

CREATE SET TABLE Sample1
(
     Datestring     CHAR(8)     CHARACTER SET LATIN CASESPECIFIC
    ,ProductCode    CHAR(10)    CHARACTER SET LATIN CASESPECIFIC
    ,StoreCode      CHAR(10)    CHARACTER SET LATIN CASESPECIFIC
    ,Amount         DECIMAL(6,0) COMPRESS (0)
)
UNIQUE PRIMARY INDEX
(
     Datestring
    ,ProductCode
    ,StoreCode
)
PARTITION BY (ZEROIFNULL(( CAST((Datestring) AS INTEGER))) MOD 65535 ) + 1
;

※上記は8文字の数字のためINTEGERにcastしているが、INTEGERでCASTできない場合（数値に変換した場合にINTEGERの最大値、約20億を超えてしまう場合）はテーブルにデータを登録する際にエラーとなるため注意。

補足としては、Unique Primary Index（以下UPI）を指定した場合は、UPIで指定した項目以外の項目はパーティションを指定することができません。

テーブルのユニーク項目以外の項目でパーティション指定をする場合は、UPIではなく、Primary Indexでテーブル定義をします。（ただし、ユニークであることを保証したい場合は、unique secondary indexをユニーク項目に指定しておくことをお勧めします）

CREATE SET TABLE Sample2
(
     UniqueNumber   DECIMAL(8)
    ,Datestring     CHAR(8)     CHARACTER SET LATIN CASESPECIFIC
    ,ProductCode    CHAR(10)    CHARACTER SET LATIN CASESPECIFIC
    ,StoreCode      CHAR(10)    CHARACTER SET LATIN CASESPECIFIC
    ,Amount         DECIMAL(6,0) COMPRESS (0)
)
PRIMARY INDEX
     UniqueNumber
 PARTITION BY (ZEROIFNULL(( CAST((Datestring) AS INTEGER))) MOD 65535 ) + 1 ; 

パーティションの指定の仕方は、上記のように65535の余り＋1で指定する場合と、HASHBUCKET(HASHROW(カラム名１,カラム名2）で指定する場合があります。個人的にですが、HASHBUCKET(HASHROW(カラム名１,カラム名2）でテーブル定義をしたことはまだありません。

TERADATA: PRIMARY INDEXは、データの分散のキー

PRIMARY INDEXは分散の指定です

TERADATAは、データを分散して管理していて、その分散用のキーとしてPRIMARY INDEXを指定します。

CREATE SET TABLE SAMPLE
(
    ,LATINFIELD       CHAR(2)       CHARACTER SET LATIN CASESPECIFIC NOT NULL
    ,UNICODEFIELD     VARCHAR(9)    CHARACTER SET UNICODE CASESPECIFIC
)
[UNIQUE] PRIMARY INDEX
(
     LATINFIELD
);

上記ようにさらにuniqueを指定することもできます。

uniuqueを指定するといわゆるPrimaryKeyと同じ気がしますが、あくまで分散用のキーのPrimary IndexにUnique制約を追加したものになります。また、primary indexに指定した項目はnullでも問題ありません。

PRIMARY INDEXに指定した項目の更新は避けよう

TERADATAはPPRIMARY INDEXで指定した項目のハッシュ値を元にデータを分散しています。なのでPRIMARY INDEXで指定した項目の値が変更されるとハッシュ値が変わりデータを再度ハッシュ値に基づき分散します。（再配置）

ファクトテーブルなど件数が多いテーブルのPRIMARY INDEXを更新すると非常に時間がかかります。

業務要件で洗い替えが必須要件なのであれば、洗い替えを行う項目をPRIMARY INDEXに指定するのはやめましょう。

どうしても洗い替えを行うのであれば、updateではなく、別のテーブルへinsertした方が早い場合があります。

Compressを指定したい項目はPRIMARY INDEXに指定できない

区分などほぼ値が決まっている項目はcompressを指定したくなりますが、PRIMARY INDEXには指定できなくなります。データのばらつき具合とcompressをかけるメリットを比較してより良い方を選択することになります。

最後に

TERADATAが高速である理由はデータを分割して保持しているからです。1人で全体を探すのではなく、10人で10個に分割されたデータを探した方が早いというような単純な理屈ですが、これを実現するのがPRIMARY INDEXです。データベースの性能を十分に引き出すためにもある程度根拠を持って定義したいところです。（適当に設定しても十分早いのですけどね。。）

TERADATA: カラム単位の文字コード指定

文字列型に適切な文字コードを指定しよう

TERADATAは、カラム単位に文字コードが指定できます。

CREATE SET TABLE SAMPLE
(
     LATINFIELD       CHAR(2)       CHARACTER SET LATIN CASESPECIFIC NOT NULL
    ,UNICODEFIELD     VARCHAR(9)    CHARACTER SET UNICODE CASESPECIFIC
)
UNIQUE PRIMARY INDEX
(
     LATINFIELD
);

文字が確実に半角アルファベット・記号・数字なのであればLATINにするとデータサイズが小さくなって、検索、結合のパフォーマンスも良くなります。

半角カナは、LATINで定義した項目には入りません。

ですが、いくつか注意点があって
crmprogrammer38.hatenablog.com

上の記事で書いた通り、UNICODEで定義した項目とLATINで定義した項目を連結して使うと遅くなってしまいますので、マスタテーブルには、結合用のLATIN項目と、文字列連結用のUNICODE項目の両方を持たせるというテクニックがあります。そして、トランザクションテーブルには結合のLATIN項目のみを持たせ、データサイズを小さくします。

上記のようにマスタテーブルに、結合キーと同じ値で、文字コードをUNICODEにした項目を持たせておけば、次のようにSQLで連結しても問題ありません。

[SQL]

select
     a."表示用キー（UNICODE) " || a."名称(UNICODE)"
    ,b"年月日" 
    ,sum(数値項目)
from     マスタテーブル a
inner join トランザクションテーブル b
on a.結合キー(LATIN) = b.結合キー(LATIN)
group by 
     a."表示用キー（UNICODE) "
    ,a."名称(UNICODE)"
    ,b."年月日" 

これなら、結合はLATIN同士で高速で、文字列の連結はUNICODE同士で遅くならないSQLとなります。
マスタに"表示用キー（UNICODE)"が追加されていて、その分スプール領域を使っているのですが、結合と、連結時のコストに比べると無視できるレベルです。

DWHで結合用の項目を別で持つのは有効なテクニックとなります。今回はナチュラルキー同士で工夫した形ですが、さらに結合キーが数字だけで構成されている場合は、結合項目をintや、decimalで定義する場合もあります。

はじめました。

主にメモです。