17.05 - PRIMARY INDEX - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQLデータ定義言語 構文規則および例

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
2021年1月
Content Type
プログラミング リファレンス
Publication ID
B035-1144-175K-JPN
Language
日本語 (日本)

プライマリ インデックスの定義。

プライマリ インデックスは、テーブルの行を各AMP全体にわたって分散するためのハッシュ アルゴリズムによって使用されます。

PRIMARY INDEX(column_list)またはNO PRIMARY INDEXを明示的に指定しない定義では、DBS制御パラメータのPrimaryIndexDefaultの設定を使用します。ただし、PrimaryIndexDefaultの設定に関係なく常にデフォルトのNO PRIMARY INDEXで定義される列パーティション テーブルを除きます。DBS制御パラメータの情報については、<Teradata Vantage™ - データベース ユーティリティ、B035-1102>を参照してください。

列パーティション テーブルの場合、デフォルトのテーブルの種類はMULTISETです。列パーティション テーブルについては、このデフォルトのテーブルの種類を変更できません。

プライマリ インデックスのデフォルトについては、<Teradata Vantage™ - データベースの設計、B035-1094>を参照してください。

index_column_name
パーティション プライマリ インデックスを定義する列セット内の列。
複数の列名を指定すると、インデックスは指定された各列が組み合わせられた値に基づいて作成されます。1つのインデックスに対して最大64個の列を指定することができます。1つのテーブルに最大32個のセカンダリ インデックスを作成できます。この計算では、ORDER BY句と共に定義された複数列NUSIは、2つの連続するインデックスとなります。
UDTデータ型の列にプライマリ インデックスを定義できます。
次のいずれかのデータ型で定義されている列に対してはプライマリ インデックスを定義できません。
  • BLOB
  • CLOB
  • LOB UDT
  • VARIANT_TYPE
  • ARRAY
  • VARRAY
  • Period
  • XML
  • Geospatial
  • JSON
  • DATASET
行レベル セキュリティ制約の列にはプライマリ インデックスを定義することはできません。
index_name
インデックスに対するオプションの名前。
データベース オブジェクトの命名ルールについては、<Teradata Vantage™ - SQLの基本、B035-1141>を参照してください。
UNIQUE
プライマリ インデックスを固有にします。
任意のセカンダリ インデックスとプライマリ インデックスを固有として定義できますが、定義にパーティション列がある場合にすべてのパーティション列が含まれるわけではないPIは例外です。

例: 同じテーブルでのUPIとUSIの指定

この例では、固有プライマリ インデックスがempno列に定義され、固有セカンダリ インデックスがname列に定義されています。empno列とname列には常に値が含まれている必要があるため、NOT NULL属性が割り当てられます。

表示の目的のため、sex、race、mstatの値は、UPPERCASE列属性を使用して定義されます。

     CREATE TABLE employee (
       emp_no    SMALLINT FORMAT '9(5)' 
                 CHECK (emp_no >= 10001 AND emp_no <= 32001) NOT NULL, 
       name      VARCHAR(12) NOT NULL, 
       dept_no   SMALLINT FORMAT '999' 
                 CHECK (deptno >= 100 AND dept_no <= 900), 
       job_title VARCHAR(12), 
       salary    DECIMAL(8,2) FORMAT 'ZZZ,ZZ9.99' 
                 CHECK (salary >= 1.00 AND salary <= 999000.00), 
       yrs_exp   BYTEINT FORMAT 'Z9' 
                 CHECK(yrs_exp >= -99 AND yrs_exp <=99), 
       dob       DATE FORMAT 'MMMbDDbYYYY' NOT NULL, 
       sex       CHARACTER UPPERCASE NOT NULL 
                 CHECK (sex IN ('M','F')),
       race      CHARACTER UPPERCASE, 
       m_stat    CHARACTER UPPERCASE 
                 CHECK (m_stat IN ('S','M','D','U')),
       edlev     BYTEINT FORMAT 'Z9' 
                 CHECK (ed_lev >=0 AND ed_lev <=22) NOT NULL, 
       h_cap     BYTEINT FORMAT 'Z9' 
                 CHECK (h_cap >= -99 AND h_cap <= 99)
     UNIQUE PRIMARY INDEX (emp_no), 
     UNIQUE INDEX (name);

例: 固有プライマリ インデックスとしての識別列の指定

この例は、テーブルのプライマリ インデックスとして使用される識別列を作成します。idnumの値の生成は、1,000で始まり、指定されたMAXVALUE300,000に達するまで10ずつ増えていきます。

テーブルに挿入される行に生成される数は次のとおりです。1,000, 1,010, 1,020, …, 300,000までで、その後は最大値に達するとエラーが返されます。

    CREATE TABLE t (
      idnum INTEGER GENERATED ALWAYS AS IDENTITY
                   (START WITH 1000
                    INCREMENT BY 10
                    MINVALUE 0
                    MAXVALUE 300000),
      phone INTEGER)
    UNIQUE PRIMARY INDEX(idnum);

この例では、CYCLEパラメータを指定しません。

CYCLE 説明
指定されない。 指定はデフォルトのNO CYCLEに設定される。
指定される。 生成される番号の順序は1,000、1,010、1,020 … 300,000、0、10、20、30 …のようになる。

番号が循環している場合、警告メッセージは返されません。しかし、idnum値がまだ存在している場合、テーブルのUPIであるidnumの値はすべて固有でなければならないので、idnum値を循環させようとするリクエストはアボートされます。

システム再始動が行なわれると、番号の順序は、最後に生成された数値から厳密に10増えたものでない場合があります。そのテーブル用にDBC.IdCol.AvailValueに格納された、次に使用可能な番号が取得され、番号はそこから開始します。最後に予約され、キャッシュに格納された未割当ての番号は失われます。

例: CYCLEを使用した固有プライマリ インデックスとしての識別列の指定

この例は、テーブルのプライマリ インデックスとして使用される識別列も作成します。idnumの値の生成は、1,000で始まり、指定されたMINVALUE 1に達するまで10ずつ減らされます。CYCLEが指定されているので、エラーは返されず、番号は次のようになります: 1,000, 990, 980 … 10, 100,000, 99,990, 99,980 …

しかし、idnum値がまだ存在している場合、テーブルのUPIであるidnumの値はすべて固有でなければならないので、idnum値を循環させようとするリクエストはアボートされます。

    CREATE TABLE t (
      idnum INTEGER GENERATED ALWAYS AS IDENTITY
                   (START WITH 1000
                    INCREMENT BY -10
                    MINVALUE 1
                    MAXVALUE 100000 CYCLE),
      phone INTEGER)
    UNIQUE PRIMARY INDEX(idnum);

例: プライマリ インデックスとプライマリ キーの指定

この例のリクエストは、プライマリ インデックスとプライマリ キーの両方を指定します。

プライマリ キー(column_1)は、固有セカンダリ インデックスに変換(マップ)されます。column_3に対するUNIQUE制約も、USIに変換(マップ)されます。これらの2つの固有のセカンダリ インデックスは、NULLであってはなりません。固有プライマリ インデックス(column_2 )はNULLにすることができます。ただし、テーブル内の1つの行だけがNULLのUPI値を持つことができます。

    CREATE TABLE good_5 (
      column_1 INTEGER NOT NULL PRIMARY KEY,
      column_2 INTEGER,
      column_3 INTEGER NOT NULL UNIQUE,
      column_4 INTEGER)
    UNIQUE PRIMARY INDEX (column_2);

例: 列パーティションを持つプライマリ インデックス

次の例では、プライマリ インデックスを持つCPテーブル用のCREATE TABLE文を示しています。

CREATE TABLE Sales4 (
    storeid         INTEGER NOT NULL,
    productid       INTEGER NOT NULL,
    salesdate       DATE FORMAT 'yyyy-mm-dd' NOT NULL,
    totalrevenue    DECIMAL(13,2),
    note            VARCHAR(256)
  )
  PRIMARY INDEX (storeid, productid)
  PARTITION BY COLUMN;

このテーブルのデフォルトでは、各列は別個の列パーティション内にあり、それぞれの列パーティションに自動圧縮付きのCOLUMN形式があります。次の問合わせの場合、これによってAMPに対するハッシュ アクセスと、列パーティション排除を使用した単一のAMPステップが可能になります。ステップ1で、1つのハッシュ値について、この単一のAMP上の3つのユーザーの列パーティションと削除列パーティションがアクセスされます。この問合わせでは効率的なアクセスを提供できますが、行ヘッダー圧縮と自動圧縮が基本AMPインデックスを持つCPテーブルほどには効果的でない場合があります(特にプライマリ インデックスがほぼ固有の場合)。

EXPLAIN
SELECT SUM(totalrevenue) FROM Sales4 s
WHERE s.storeid = 37 AND s.productid = 1466;

Explanation
---------------------------------------------------------------------------
  1) First, we do a single-AMP SUM step to aggregate from 4 column
     partitions of PLS.s by way of the primary index "PLS.s.storeid =
     37, PLS.s.productid = 1466" with no residual conditions.  Aggregate
     Intermediate Results are computed locally, then placed in Spool 3.
     The size of Spool 3 is estimated with high confidence to be 1 row (23
     bytes).  The estimated time for this step is 0.02 seconds.
  2) Next, we do a single-AMP RETRIEVE step from Spool 3 (Last Use) by
     way of the hash value of "PLS.s.storeid = 37, PLS.s.productid =
     1466" into Spool 1 (one-amp), which is built locally on that AMP.
     The size of Spool 1 is estimated with high confidence to be 1 row
     (36 bytes).  The estimated time for this step is 0.02 seconds.
  3) Finally, we send out an END TRANSACTION step to all AMPs involved
     in processing the request.
  -> The contents of Spool 1 are sent back to the user as the result of
     statement 1.  The total estimated time is 0.04 seconds.

例: 列パーティションを持つ固有プライマリ インデックス

この例では、固有プライマリ インデックスを持つ列パーティション テーブルを定義しています。各列は、システム決定のROW形式を持つそれ自体のパーティションに入ります。2バイト パーティションが定義されています。列パーティション テーブルは、デフォルトではMULTISETテーブルです。MULTISETは明示的に指定できますが、SETを指定してはいけません。行あたりのバイト数は、列パーティションあたりの行ヘッダーのため、86ずつ増えます。この例では、自動圧縮が列パーティションのデフォルトとして想定されています。

CREATE TABLE Orders (
    o_orderkey INTEGER NOT NULL,
    o_custkey INTEGER,
    o_orderstatus CHAR(1) CASESPECIFIC,
    o_totalprice DECIMAL(13,2) NOT NULL,
    o_orderdate DATE FORMAT 'yyyy-mm-dd' NOT NULL,
    o_shippriority INTEGER,
    o_comment VARCHAR(79) )
  UNIQUE PRIMARY INDEX (o_orderkey) PARTITION BY COLUMN;