17.10 - テーブル定義に関する考慮事項 - Advanced SQL Engine - Teradata Database

Teradata Vantage™- データベースの管理

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
2021年7月
Content Type
管理
Publication ID
B035-1093-171K-JPN
Language
日本語 (日本)

最低限名前とデータ型句を使ってCREATE TABLE文またはALTER TABLE文で列を定義すれば、テーブルはデータ属性を取得します。

データ属性は、格納されたデータの内部表記を制御し、そのデータをどのようにユーザーに提示するかを判別します。FORMAT句を使えば、SELECT問合わせから戻された外部表記を変更することができます。

テーブル定義は、そのテーブルにアクセスするアプリケーションのパフォーマンスに直接影響を与えます。正しく計画すれば、テーブルの作成時にテーブルを正しく定義するのに役立ちます。以下のテーブルに、テーブルの計画をたてる際に考慮する項目をいくつかリストしています。

問題 考慮事項
圧縮

(アルゴリズム)

CREATE TABLE文またはALTER TABLE文のCOMPRESS USING udfnameオプションを指定して、テーブルの列ごとに文字データを圧縮し、ストレージを最小にします。このオプションを使用すれば、独自の圧縮または圧縮解除のアルゴリズムを選択できます。アルゴリズム圧縮は使用頻度がかなり低いデータに適用すると最も効果を発揮します。詳細については、<Teradata Vantage™ - データベースの設計、B035-1094>を参照してください。
圧縮

(ブロック レベル)

CREATE TABLE文またはALTER TABLE文のBLOCKCOMPRESSION = block_compression_optionを指定して、プライマリ テーブルと フォールバック テーブルのデータ ブロックを圧縮し、ストレージを最小にします。ソフトウェアベースのブロックレベル圧縮は、大容量でアクセス頻度のかなり低いテーブルに適用すると最も効果を発揮します。詳細については、<Teradata Vantage™ - データベースの設計、B035-1094>を参照してください。
圧縮

(複数値)

COMPRESS句を指定して最大255の個々の値を圧縮するか、またはNULLの領域をゼロにします。(NULL受け入れ可能な列のNULLは、"COMPRESS"が指定されている場合は自動的に圧縮されます。)

ALTER TABLEを使用して、列、列パーティション、またはテーブルで圧縮を即時に追加、変更、または削除します。詳細は、<Teradata Vantage™ - データベースの設計、B035-1094>を参照してください。

列のデフォルト値 テーブルを作成または変更する場合は、DEFAULT関数を使用して列にデフォルト値を指定できます。デフォルト値を指定することにより、列に対応する値が返されます。値を指定しない場合のデフォルトはNULLです。
ユーザー定義型(UDT)を明示的なデフォルト値として列に割り当てることはできません。

列に、USER、CURRENT_DATE、CURRENT_TIME、CURRENT_TIMESTAMPなどの組み込み値をデフォルト値として返すよう指定することができます。詳細については、<Teradata Vantage™ - SQL関数、式、および述部、B035-1145>を参照してください。ホストに戻されるDEFAULT値およびNULL値に関する詳細は、<Teradata Vantage™ - データ タイプおよびリテラル、B035-1143>を参照してください。

集約結果におけるデフォルトとNULL 集約における未指定の値は次のように表記されて、それぞれ以下のように処理されます。
  • NULL。計算において無視されます。
  • デフォルト値。計算に組み入れられます。

例えば、次のようにDepartment 300の社員の平均給与を知りたいと仮定します。

SELECT DeptNo, AVG(Salary)
FROM Employee
GROUP BY DeptNo
WHERE DeptNo = 300 ;
1件の給与が不明である場合には、列がどのように定義されているかに応じて結果が変わります。Salary列の未指定の値が次のように定義される場合:
  • DEFAULT値。結果には、デフォルト値が実際の値として含まれます。これは、実際にすべての値が認識されている場合の平均とかけ離れる可能性があります。
  • NULL。結果は、確認済みの給与の平均になります。NULLは無視されます。このほうが、実際の平均に近くなると考えられます。
以下を行なうことにより、集約時に不明値をどう処理するかをさらに制御できます。
  • NULLを不明値のデフォルト値とすることで代替値を排除し、NULLIF関数定義を使用する。例:
    NULLIF(SALARY, defaultvalue )

    この場合、平均値の計算ではdefaultvalueは無視されます。

  • NULLと表記された不明値をデフォルト値に変更し、

    COALESCE関数定義を使用する。例えば、次のようになります。

    COALESCE(Salary, defaultvalue )

    ここでdefaultvalueは、平均値の計算に使用される値です。

一貫性のあるデータ型と列名の定義 異なるテーブルにある同じデータの列のデータ型は同じでなければならず、簡単に識別するためには、同じ名前でなければなりません。例えば、1つのテーブルの列のタイトルがEmpNoである場合には、別のテーブルでもEmpNoでなければなりません。複数のテーブルでのデータの整合性は非常に重要です。次のような考慮事項があります。
  • 1つのテーブル内のデータの変更。
    1つのテーブル内のデータを変更すると、別のテーブルのデータに影響が出る場合があります。例えば、部門テーブルで部門番号を更新すると、部門番号の列を擁する従業員テーブルにも影響を与えることになります。以下を使用すれば、データの整合性を保つことができます。
    • 変更により影響を受けるテーブルすべてを更新するマクロ、ストアド プロシージャ、トリガー、またはアプリケーション プログラム。
    • RIの制約。(詳細については、<Teradata Vantage™ - データベースの設計、B035-1094>および<Teradata Vantage™- SQLデータ定義言語 - 詳細トピック、B035-1184>を参照してください。)
  • ある1つのテーブルの列の別のテーブルの列との結合。以下を確認します。
    • 結合列が同じデータ型かつ同じサイズである。
    • 両方のテーブルでデータに整合性がある。例えば、2つのテーブルをEmployee Name(社員名)列で結合する場合、どちらのテーブルでも列の表記法(例えば「Smith H」などの姓と名のイニシャル)は同一でなければなりません。
    • 結合内で参照される、行レベル セキュリティで保護されたすべてのテーブルは、同じ制約を含んでいる。
  • テーブルを使用した大きなデータ オブジェクトの保存。LOB型の列でテーブルを作成します。これには、以下のものが含まれます。
    • 文字ラージ オブジェクト(CLOB)- CLOB列には、通常のテキスト、HTML、またはXML文書などの文字データを格納できます。
    • バイナリ ラージ オブジェクト(BLOB)- BLOB列には、グラフィックス、ビデオ クリップ、ファイル、および文書などのバイナリ オブジェクトを格納できます。
IDENTITY列 IDENTITYは、定義したテーブルに行が挿入されるごとに、番号を生成させるために使用するオプションの属性です。識別列は、テーブル内の最初の列である必要も、インデックスとして定義されている必要もありません。IDENTITY属性は、生成される番号が固有になるように指定できます。
NULLを伴う結合 結合で使用する列にNULLと汎用値の両方が入っている場合、結果は誤りとなる可能性があります。例えば、EmployeeテーブルおよびDepartmentテーブルを結合させて、社員と配属部署のリストを作成するとします。
SELECT Name, Loc FROM Employee, Department WHERE Employee.DeptNo = Department.DeptNo;

次のような望ましくない結果を生じることがあります。各テーブルのnullによってマッチングができません。部署番号が不明な社員(Employee.DeptNoがNULL)と社員の存在しない部署はリストされません。

部署番号が不明な社員と社員の存在しない部署をリストするためには、DeptNo列において別のデフォルト値を使って不明値を表記します。

  1. Departmentテーブルで使用するデフォルト値と同じDeptNoを持つダミーの「unknown (不明)」の社員を、Employeeテーブルに追加します。
  2. Employeeテーブルで使用するデフォルト値と同じDeptNoを持つダミーの「unknown (不明)」の部署を、Departmentテーブルに追加します。
問合わせのサンプルでは、DeptNo内にNULLがある場合、内部結合の代わりに完全外部結合を使用して一致しない行の情報を取得することができます(また、前述のように、COALESCE関数を使用してNULLを値に変更することもできます)。ただし、外部結合を使って非一致の行を取得するほうが効率は高くなります。

NULLを使用した場合の影響の詳細、および効率的な結合の設計方法の詳細については、< Teradata Vantage™ - データベースの設計、B035-1094>の「結合とハッシュ インデックス」を参照してください。

NULLとGROUP BY文節 集約結果をグループ化する際にNULLと定義した列を使用すると、結果が混乱する可能性があります。例えば、組織内の各部署の平均給与を知るために以下の問合わせを実行依頼したとします。
SELECT DeptNo, AVG(Salary)
FROM Employee
GROUP BY DeptNo;
結果はDeptNo列の定義によって異なります。
  • DeptNoにNULLを使用することができ、2人の社員にまだ部署番号が割り当てられていない場合、その2人の社員の平均値が計算されて、NULL部署番号の下にリストされます。これは、混乱を招く可能性があります。
  • DeptNoがDEFAULTで定義され、指定されている定数に意味がある場合(Unknown(不明)など)、この結果は有効です。
前記の集約結果の解説どおりに、NULLをデフォルト値に(NULLIFを使って)、そしてデフォルト値をNULLに(COALESCEを使って)変換できます。
列のNULL可能性 新しいデータ行を挿入する際には、常に値を指定できるとは限りません。例えば、新入社員にすぐに役職が付くことはないでしょう。次のようにNULLまたはNOT NULLを使って、列がnull可能かどうかを明示的に定義することができます。
行の挿入時に列に値を与えない場合の列のNULL可能性は次のとおりです。
  • NOT NULLとして定義されDEFAULT値が指定されていない場合、INSERT文はエラーを返します。
  • 定義されていない場合、自動的にNULLが指定されます。
ベスト プラクティスは、列をNULL受入れ可能にする明確な理由がない限り、列をNOT NULLにしてデフォルト値( デフォルト値が適切な場合)を指定する方法です。これによって領域を節約し、場合によってはより効率的な問合わせ計画を立てることができます。列をNULL受入れ可能にする必要がない場合、null にはさまざまな解釈が存在するため、この列のNULLの意味を確認し、理解して、文書化してください。

null可能な列(NULL標識なし)のデータをアプリケーション プログラムが要求した場合にNULLが検出されると、nullではなくその列のデータ型と互換性のある代替値がアプリケーションに戻されます。NULLは不明値であるため、その代替値(ゼロの数値、ブランク、または長さ0の要素文字列)は誤動作を引き起こす可能性があります。

NULL受入れ可能な列では絶対にNOT IN句を使用せず、必ずNOT EXISTS句に置換してください。NOT INを使用すると、データベースの動作が著しく低下します。

また、NULLの結果と比較すると必ずUNKNOWN値になる点に注意してください。ただし、NOT EXISTSはUNKNOWN値をEXPLAINで示されるFALSEと同じように処理します。

例:

1 in (1,2,NULL) -> 1=1 or 1=2 or 1=NULL -> TRUE OR FALSE OR UNKNOWN -> TRUE

1 not in (1,2,NULL) -> 1<>1 and 1<>2 and 1<>NULL -> TRUE and FALSE and UNKNOWN -> UNKNOWN

予想外の応答セットの詳細については、<Teradata Vantage™ - SQL関数、式、および述部、B035-1145>の「NOT INに対するNullの動作」を参照してください。

その他の考慮事項 以下の例を考えてみてください。
  • プライマリ インデックスを使用するかどうか。(このテーブルのNoPIの行を参照。)
  • プライマリ インデックスを使用するための列、およびPIは固有かどうか。
  • テーブルにプライマリ インデックスがある場合、1または複数レベルの行パーティションを行なうかどうか。
  • プライマリ インデックスがない場合、列パーティション化、およびオプションで1または複数レベルの行パーティション化を行なうかどうか。
  • セカンダリ インデックスがある場合はどんなセカンダリ インデックスを定義するか、およびセカンダリ インデックスは固有かどうか。
  • 各列ではどんな制約が必要か。
  • COLUMN形式の列パーティションに対して行ヘッダー圧縮を行なうと、ストレージ コストを削減し、システム パフォーマンスを向上させることができます。ただし、幅の広い列パーティションなどは自動圧縮の利点が得られないため、ROW形式(非行ヘッダー圧縮)の方が適していることがあります。
  • テーブルのコミット済み行でロードと同時の読み取りを許可するかどうか。この機能を使用するには、ロードを分離するようにテーブルをセットアップする必要があります。ロード分離テーブルでは、非分離のテーブルより多くの領域が必要になり、論理的に削除された行を除去するために定期的な保守が必要になります。詳細については、同じテーブルへのロードと並行したコミット済みデータ読み取りについてを参照してください。
NoPIテーブル テーブルの列パーティション化をする場合、プライマリ インデックスによって値が追加されない場合、指定すべきプライマリ インデックスが不明な場合、ステージング テーブルが必要な場合、またはNoPIテーブルに対する制限が許容されない場合には、NoPIテーブルを使用します。

ステージング テーブルを作成するためには、PIのないテーブルであるNoPIテーブルを使用します。ステージングにNoPIテーブルを使用すると、FastLoadジョブやTPumpジョブからデータをロードする場合のパフォーマンスが向上します。これは、テーブルがPIによってハッシュされず、行を特定の順番に並び替えたり、特定のAMPに再度分散する必要がないためです。

NoPIテーブルは、ある種の変換や標準化を行なう必要のあるステージング テーブルにデータをロードするアプリケーションに有効です。この場合、データは別のテーブルに保存する前に変換する必要があります。INSERT ... SELECT、UPDATE-FROM、またはMERGE-INTOの各文を使用すると、ステージング テーブルのデータをターゲット テーブルに適用できます。

PIのデフォルトを使用する代わりに、明示的にPIまたはNoPIを指定する方法がベスト プラクティスです。デフォルトは適切でない場合があり、AMPのデータに偏りが起こる場合があります。

NoPIテーブルの作成方法については、<Teradata Vantage™ - SQLデータ定義言語-構文規則および例、B035-1144>の「CREATE TABLE」を参照してください。