17.00 - 17.05 - 行パーティション化を使用した書き換えの例 - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQLリクエストおよびトランザクション処理

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
17.05
Published
2020年6月
Content Type
プログラミング リファレンス
ユーザー ガイド
Publication ID
B035-1142-170K-JPN
Language
日本語 (日本)

例のテーブル定義

次の例のセット内にあるすべてのクエリーは、次のCREATE TABLEリクエストによって定義されるマルチレベルのパーティション化されたテーブルを使用します。

CREATE TABLE markets (
  productid       INTEGER NOT NULL,
  region          BYTEINT NOT NULL,
  activity_date   DATE FORMAT 'yyyy-mm-dd' NOT NULL,
  revenue_code    BYTEINT NOT NULL,
  business_sector BYTEINT NOT NULL,
  note            VARCHAR(256))
PRIMARY INDEX (productid, region)
PARTITION BY (
RANGE_N(region          BETWEEN 1
                        AND     9
                        EACH    3),
RANGE_N(business_sector BETWEEN 0
                        AND    49
                        EACH   10),
RANGE_N(revenue_code    BETWEEN 1
                        AND    34
                        EACH    2),
RANGE_N(activity_date   BETWEEN DATE '1986-01-01'
                        AND     DATE '2007-05-31'
                        EACH INTERVAL '1' MONTH));

行パーティション化の詳細

次のテーブルに、パーティションについての詳細を示します。
  • 次の4つのレベルの行パーティションは定義されていて、それぞれのレベルには示されている行パーティション数があります。
    行パーティション レベルの番号 このレベルのパーティション列 このレベルで定義されている行パーティションの数
    1 region 3
    2 business_sector 5
    3 revenue_code 17
    4 activity_date 257
  • テーブルに定義される組み合わせパーティションの総数は、2バイト内部パーティション番号の場合は最大で65,535です。これは、次のように計算されます。

    組み合わせパーティションの総数の式

    説明

    等式要素 指定内容
    P Ln レベルnでのパーティションの数
説明を簡単にするために、問合わせ例に関する注釈では、以下の事項を前提としています。
  • 行は行パーティション間に均等に配分される。
  • 組み合わせパーティションごとに多くのデータ ブロックを組み合わせできる。

問合わせの例が静的行パーティション排除を十分に活用できなかった場合、それらの問合わせはすべて、全AMPのフル テーブル スキャンを処理する必要があります。

行パーティション化の例: 行パーティション2のみの読み取り

次のクエリーでは、データベースは全AMP上の行パーティション2のみをレベル1で読み取ります。この結果、システムはデータ ブロックの約1/3 (33.3%)をマーケット テーブルについて読み取ります。これは、レベル1の行パーティションが3つあり、データベースが適格な行すべてにアクセスする際に、それらのうち1つのみを読み取る必要があるためです。

行パーティション2の領域には、値が5または6の行が含まれています。データベースは、行パーティション2を読み取る際に、それらの行を必然的に読み取りますが、それらは問合わせの述部に適格ではないので、結果セットでは返されません。

SELECT *
FROM markets
WHERE region=4;

行パーティション化の例: すべてのAMPにおけるレベル2での行パーティション4と5のみの読み取り

次のクエリーでは、データベースは全AMP上のパーティション4および5のみをレベル2で読み取ります。この結果、システムはデータ ブロックの約2/5 (40%)をマーケット テーブルについて読み取ります。これは、レベル2の行パーティションが5つあり、データベースが適格な行すべてにアクセスする際に、それら5つのうち2つを読み取る必要があるためです。

行パーティション4には、business_sectorの値が30の行が含まれています。データベースは、パーティション4を読み取る際に、それらの行を必然的に読み取りますが、それらは問合わせの述部に適格ではないので、結果セットでは返されません。

SELECT *
FROM markets
WHERE business_sector>30;

行パーティション化の例: 行パーティション1、2、3のみの読み取り

次のクエリーでは、データベースは全AMP上のパーティション1、2および3のみをレベル3で読み取ります。この結果、システムはデータ ブロックの約3/17 (17.6%)をマーケット テーブルについて読み取ります。これは、レベル3の行パーティションが17個あり、データベースが適格な行すべてにアクセスする際に、それらのうち3つのみを読み取る必要があるためです。

パーティション3には、revenue_codeの値が6の行が含まれています。データベースは、パーティション5を読み取る際に、それらの行を必然的に読み取りますが、それらは問合わせの述部に適格ではないので、結果セットでは返されません。

SELECT *
FROM markets
WHERE revenue_code<5;

行パーティション化の例: 行パーティション50および51のみの読み取り

次のクエリーでは、データベースは全AMP上のパーティション50および51のみをレベル4で読み取ります。この結果、システムはデータ ブロックの約2/257 (0.78%)をマーケット テーブルについて読み取ります。これは、レベル4の行パーティションが257つあり、データベースが適格な行すべてにアクセスする際に、それらのうち2つのみを読み取る必要があるためです。

パーティション50とパーティション51の両方に、問合わせの述部を満たさない行が含まれています。データベースは、行パーティションを読み取る際に、それらの行を必然的に読み取りますが、それらは問合わせの述部に適格ではないので、結果セットでは返されません。

SELECT *
FROM markets
WHERE activity_date>=DATE '1990-02-12'
AND   activity_date<=DATE '1990-03-28';

行パーティション化の例: すべてのAMPにおけるレベル2での行パーティション4と5、およびすべてのAMPにおけるレベル1でのパーティション2のみの読み取り

次のクエリーでは、システムは全AMP上のパーティション4と5をレベル2で読み取り、パーティション2をレベル1で読み取ります。この結果、システムはデータ ブロックの約2/15 (13.3%)をマーケット テーブルについて読み取ります。これは、結合されたレベル1と2に15個のパーティションがあり(結合総数は3 x 5 = 15)、レベル1に3個、レベル2に5個のパーティションがあります。システムが適格な行すべてにアクセスする際に、それらの2つのみを読み取る必要があるためです。

これらの全パーティションには問合わせ述部に適格でないいくつかの行が含まれているため、結果セットでは返されません。

SELECT *
FROM markets
WHERE region=4
AND   business_sector>30;

行パーティション化の例1: すべてのAMPにおけるマルチレベルでの複数の行パーティションの読み取り

次の問合わせでは、データベースは全AMP上レベル4の2つの行パーティション(パーティション50、51)、レベル3の3つの行パーティション(パーティション1、2、3)、およびレベル2の2つの行パーティション(パーティション4、5)を読み取ります。

レベル番号 このレベルで読み取る

行パーティションの数

レベル内で読み取られるパーティションの番号
2 2
  • 4
  • 5
3 3
  • 1
  • 2
  • 3
4 2
  • 50
  • 51

その結果、データベースはデータ ブロックの約12/21,845 (0.05%)をマーケット テーブルについて読み取ります。これは、結合したレベル2、3、および4に21,845のパーティションがあり(結合総数は5 x 17 x 257 = 21,845)、システムが適格な行すべてにアクセスする際に、それらのうち12のみ(結合総数は2 x 3 x 2 = 12)を読み取る必要があるためです。

これらすべてのパーティションには問合わせ述部に適格でないいくつかの行が含まれているため、データベースはそれらの行を結果セットで返しません。

SELECT *
FROM markets
WHERE business_sector>30
AND   revenue_code<5
AND   activity_date>=DATE '1990-02-12'
AND   activity_date<=DATE '1990-03-28';

行パーティション化の例2: すべてのAMPにおけるマルチレベルでの複数の行パーティションの読み取り

次の問合わせでは、データベースは全AMP上のレベル4の2つの行パーティション(パーティション50、51)、レベル3の3つの行パーティション(パーティション1、2、3)、レベル2の2つの行パーティション(パーティション4、5)、レベル1の1つの行パーティション(パーティション2)を読み取ります。

レベル番号 このレベルで読み取られるパーティションの数 レベル内で読み取られるパーティションの番号
1 1 2
2 2
  • 4
  • 5
3 3
  • 1
  • 2
  • 3
4 2
  • 50
  • 51

その結果、データベースはデータ ブロックの約12/65,535 (0.18%)をマーケット テーブルについて読み取ります。これは、結合したレベル2、3、および4に65,535のパーティションがあり(結合総数は3 x 5 x 17 x 257 = 65,535)、データベースが適格な行すべてにアクセスする際に、それらのうち12のみ(結合総数は1 x 2 x 3 x 2 = 12)を読み取る必要があるためです。

これらすべてのパーティションには問合わせ述部に適格でないいくつかの行が含まれているため、データベースはそれらの行を結果セットで返しません。

SELECT *
FROM markets
WHERE region=4
AND   business_sector>30
AND   revenue_code<5
AND   activity_date>=DATE '1990-02-12'
AND   activity_date<=DATE '1990-03-28';

行パーティション化の例: すべてのAMPにおけるレベル2での1つの行パーティションの読み取り

次のクエリーでは、データベースは全AMP上の1つの行パーティション(パーティション1)をレベル2で読み取ります。この結果、システムはデータ ブロックの約1/5 (20.0%)をマーケット テーブルについて読み取ります。これは、レベル2の行パーティションが5つあり、データベースが適格な行すべてにアクセスする際に、それらのうち1つのみを読み取る必要があるためです。

データベースが読み取る必要がある行パーティションには、business_sectorに0から9までの値を保持する行が含まれています。

この行パーティションには問合わせ述部に適格でないいくつかの行が含まれているので、システムはそれらを結果セットで返しません。

SELECT *
FROM markets
WHERE PARTITION#L2=1;

行パーティション化の例: すべてのAMPにおける1つの組み合わせ行パーティションの読み取り

次のクエリーでは、データベースは全AMP上の1つの組み合わせ行パーティションを読み取ります。これは、指定された述部は32,531に等しいという組み合わせパーティションに対する等価条件になるためです。この結果、システムはデータ ブロックの1/65,525 (0.15%)のみをマーケット テーブルについて読み取ります。これは、値32,531を持つ組み合わせパーティションが1つのみであり、システムが適格な行すべてにアクセスする際に、組み合わせパーティション番号32,531があるデータ ブロックのみを読み取る必要があるためです。

データベースが読み取る必要がある行パーティションには、次の値セットによって定義される行が含まれています。

PARTITION#Ln 適格な行が格納されているPARTITIONの値 このパーティション値の範囲にマップされる条件
1 2 regionが4と6の間
2 3 business_sectorが20と29の間
3 8 revenue_codeが15と16の間
4 149 activity_date が1998-05-01と1998-05-31の間
SELECT *
FROM markets
WHERE PARTITION=32531;