パーティション基本索引覚え書き(1)

sql teradata
  • パーティション基本索引(Partition Primary Index = PPI)を使うことで、テーブルのパーティショニングが可能。
  • テーブルのパーティショニングなので、SIには設定できない。
  • PRIMARY INDEX (COL1, COL2, ...) の後に PARTITION BY パーティショニング条件を付与する。
    • パーティショニング条件は、結果が1-65,535の範囲の整数になればOK。
  • 単一レベルPPI(SLPPI)と複数レベルPPI(MLPPI)とあるらしい。
    • →条件の数が1個 or たくさん
  • 基本索引 ≠ パーティション列セット みたいな記述になってるけど、用語の説明がないのでよく判りません…。
    • primary index (a,b,c) pertition by a とかこんなの？
  • パーティショニング条件の確認は、DBC.IndexConstraints の ConstraintText で確認できる。


以下いろいろ

単一レベルパーティション

パーティション列がINTEGER型の場合
create multiset table hoge1 (
  a integer not null,
  b integer not null
)
primary index (a) partition by a;
> 完了。 0 行処理されました

データ・ディクショナリでPPIになっている事を確認。

select IndexType
from DBC.Indices
where DatabaseName = 'hoge' and TableName = 'hoge1';

IndexType
----------
Q

※IndexType一覧

P 非パーティション基本
Q パーティション基本
S 副次
J 結合索引
N ハッシュ索引
K 基本キー
U 固有の制約
V 値順副次
H ハッシュ順ALL(カバーリング) 副次
O 値順ALL(カバーリング) 副次
I 複合副次索引の列の並べ替え
M 複数列統計
D 列のパーティション統計情報を導出
1 結合またはハッシュ索引のfield1 列
2 結合またはハッシュ索引のfield2 列

IndexConstraints.ConstraintText を確認すると、勝手に条件が増えてる。

select ConstraintText
from DBC.IndexConstraints
where DatabaseName = 'hoge' and TableName = 'hoge1';
ConstraintText
----------
CHECK (a BETWEEN 1 AND 65535)

insert into hoge.hoge1 values (1, 1);
> 完了。 1 行処理されました
insert into hoge.hoge1 values (0, 1);
> 5728:  Partitioning violation for table hoge.hoge1.
パーティション列をCHAR型にしてみた
create multiset table hoge1 (
  a char(5) char set latin not null,
  b integer
)
unique primary index (a) partition by a;
> 完了。 0 行処理されました

IndexConstraints.ConstraintText を確認すると、整数型に強制キャストされてる。

select ConstraintText from DBC.IndexConstraints
where DatabaseName = 'hoge' and TableName = 'hoge1';

ConstraintText
----------
CHECK ((CAST((a ) AS INTEGER)) BETWEEN 1 AND 65535)

insert into hoge1 values ('aaaaa', 1);
> 5728:  Partitioning violation for table hoge.hoge1.

insert into hoge1 values ('1', 1);
> 完了。 1 行処理されました
演算

値を足してやれば、負数も設定できる。

create multiset table hoge1 (
  a integer not null,
  b integer not null
)
primary index (a) partition by a + 10;
> 完了。 0 行処理されました

insert into hoge1 values (0, 1);
> 完了。 1 行処理されました
insert into hoge1 values (-1, 2)
> 完了。 1 行処理されました

列同士の演算も可能。

create multiset table hoge1 (
  a integer not null,
  b integer not null
)
primary index (a) partition by a + b
> 完了。 0 行処理されました

insert into hoge1 values (10000, 55535);
> 完了。 1 行処理されました
insert into hoge1 values (10001, 55535);
> 5728:  Partitioning violation for table hoge.hoge1.

結果がUNKNOWNになるとエラー

create multiset table hoge1 (
  a integer not null,
  b integer
)
primary index (a) partition by a + b
> 完了。 0 行処理されました

insert into hoge1 values (10000, 55535);
> 完了。 1 行処理されました
insert into hoge1 values (10000, null);
> 5728:  Partitioning violation for table hoge.hoge1.

そういう場合は、coalesceとか使えるから、それで何とかする。
内部的にはCASE式に変換されるみたい。

create multiset table hoge1 (
  a integer not null,
  b integer
)
primary index (a) partition by a + coalesce(b, 0);
> 完了。 0 行処理されました
insert into hoge1 values (10000, null);
> 完了。 1 行処理されました

select ConstraintText
from DBC.IndexConstraints
where DatabaseName = 'hoge' and TableName = 'hoge1';

ConstraintText
----------
CHECK ((a + (( CASE WHEN (NOT (b IS NULL )) THEN (b ) ELSE (0 ) END ))) BETWEEN 1 AND 65535)
Unique PPI(UPPI)の制限

Non Uniqueなら、PIに含まれない列をパーティショニング条件で指定できる。

create multiset table hoge1 (
  a integer not null,
  b integer not null)
primary index (a) partition by b;
> 完了。 0 行処理されました

Uniqueの場合は、PIで指定している列のみ

create multiset table hoge1 (
  a integer not null,
  b integer not null
)
unique primary index (a) partition by b;
> 5712:  UNIQUE is not allowed.
条件分岐によるパーティショニング

CASE_N関数を使用する。

create multiset table hoge1 (
  a integer not null,
  b integer
)
unique primary index (a, b) partition by case_n(b < 10, b < 100, b < 1000);
> 完了。 0 行処理されました

insert into hoge1 values (0, 0);
> 完了。 1 行処理されました
insert into hoge1 values (1, 1);
> 完了。 1 行処理されました
insert into hoge1 values (2, null);
> 5728:  Partitioning violation for table hoge.hoge1.
insert into hoge1 values (3, 1001);
> 5728:  Partitioning violation for table hoge.hoge1.

NO CASEを指定すると、全条件でFLASEになる場合の振り分けが可能。
また、UNKNOWNを指定すると、UNKNOWNになる場合の振り分けが可能。
両方指定する場合（格納先は多分同じになる）はORか","で連結。
ORは全FALSEとUNKNOWNを同一パーティションに格納。","は別パーティションに格納。

create multiset table hoge1 (
  a integer not null,
  b integer
)
unique primary index (a, b) partition by case_n(b < 10, b < 100, b < 1000, no case);
> 完了。 0 行処理されました
insert into hoge1 values (2, null);
> 5728:  Partitioning violation for table hoge.hoge1.
insert into hoge1 values (3, 1001);
> 完了。 1 行処理されました

drop table hoge1;
create multiset table hoge1(
  a integer not null,
  b integer
)
unique primary index (a, b) partition by case_n(b < 10, b < 100, b < 1000, unknown);
> 完了。 0 行処理されました
insert into hoge1 values (2, null);
> 完了。 1 行処理されました
insert into hoge1 values (3, 1001);
> 5728:  Partitioning violation for table hoge.hoge1.

drop table hoge1;
create multiset table hoge1 (
  a integer not null,
  b integer
)
unique primary index (a, b) partition by case_n(b < 10, b < 100, b < 1000, no case or unknown);
> 完了。 0 行処理されました
insert into hoge1 values (2, null);
> 完了。 1 行処理されました
insert into hoge1 values (3, 1001);
> 完了。 1 行処理されました

文字列の比較は駄目

create multiset table hoge1 (
  a varchar(5) char set latin not null,
  b integer
)
primary index (a) partition by case_n(a = 'aaaaa', no case);
> 5714:  Invalid partitioning expression for PARTITION BY.

create multiset table hoge1 (
  a varchar(5) char set latin not null,
  b integer
)
primary index (a) partition by case_n(position(a IN 'x') > 0, no case);
> 5714:  Invalid partitioning expression for PARTITION BY.
範囲指定によるパーティショニング

RANGE_Nを使用する。
RANGE_N(test_expression BETWEEN start_expression AND end_expression EACH range_size)で記述。

create multiset table hoge1 (
  a integer not null,
  b integer
)
primary index (a) partition by range_n(a between 1 and 10000 each 100);
> 完了。 0 行処理されました

insert into hoge1 values (1, 1);
> 完了。 1 行処理されました
insert into hoge1 values (1000, 1);
> 完了。 1 行処理されました
insert into hoge1 values (10000, 1);
> 完了。 1 行処理されました
insert into hoge1 values (10001, 1);
> 5728:  Partitioning violation for table dwhdev_db.hoge1.

DATE型の場合、range_sizeの指定にはINTERVALを使う*1
ちなみにstart_expression / end_expressionにcurrent_date使うとエラーになるっぽいです。

create multiset table hoge1 (
  a date not null,
  b integer
)
primary index (a) partition by range_n(a between DATE '1900-01-01' and DATE '2100-12-31' each interval '1' month);

insert into hoge1 values (DATE '1900-01-01', 1);
insert into hoge1 values (DATE '1900-02-01', 2);
insert into hoge1 values (DATE '1900-03-05', 3);
insert into hoge1 values (DATE '2000-01-30', 4);
insert into hoge1 values (DATE '2001-01-15', 5);

select
  a, b,
  range_n(a between DATE '1900-01-01' and DATE '2100-12-31' each interval '1' month)
from hoge1
order by a;
a b
1900/01/01 1 1
1900/02/01 2 2
1900/03/05 3 3
2000/01/30 4 1201
2001/01/15 5 1213

start_expression AND end_expression をカンマで区切って列挙できる。歯抜けでもOK。

create multiset table hoge1 (
  a integer not null,
  b integer
)
primary index (a) partition by range_n(a between 1 and 100, 101 and 200, 50000 and 65535)

start_expression / end_expression を"*"でワイルドカード指定できる。

create multiset table hoge1 (
  a integer not null,
  b integer
)
primary index (a) partition by range_n(a between * and 100, 101 and 50000 each 1000, 50001 and *)

ただし、パーティションの個数が65533を超えてはいけない。
ワイルドカードは1や65535ではないので注意。

create multiset table hoge1 (
  a integer not null,
  b integer
)
primary index (a) partition by range_n(a between 60000 and * each 10000);
> 5716:  PARTITION BY RANGE_N defines more than 65533 ranges.

NO RANGE / UNKNOWNで、FALSEとUNKNOWNを引っ掛ける。

複数レベル

MLPPIの場合は、partition by (expression1, expression2, ...)のように記述する。
ただし、CASE_N / RANGE_Nのいずれかのみ使用可能。

create multiset table hoge1 (
  a integer not null,
  b integer
)
primary index (a) partition by (a, b)
> 5746:  The partitioning expression for level 1 is not valid; partitioning expressions for multi-level partitioning must be RANGE_N or CASE_N functions.

create multiset table hoge1 (
  a integer not null,
  b integer
)
primary index (a) partition by (
  range_n(a between 1 and 10000 each 1000, no range),
  case_n(b = 1, b = 2, b = 3, no case or unknown)
)
> 完了。 0 行処理されました

*1:マニュアルだとADD_MONTHSを使う事になってたけど、やったらSyntax Errorになった