索引迁移

Teradata中CREATE INDEX的列和表名的顺序和DWS中不同。使用参数distributeByHash配置数据在集群节点间的分布方式。该工具不会添加DISTRIBUTE BY HASH用于创建具有主键和非唯一主索引的表。

输入：主键并不是主索引的超集，且仅有1列匹配

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

输出

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

输入：主键不是主索引的超集，且无匹配的列

CREATE SET  TABLE  DP_SEDW.T_170UT_HOLDER_ACCT
           ,NO FALLBACK
           ,NO BEFORE JOURNAL
           ,NO AFTER JOURNAL (
               BUSINESSDATE VARCHAR( 10 )
               ,SOURCESYSTEM VARCHAR( 5 )
               ,UPLOADCODE VARCHAR( 1 )
               ,HOLDER_NO VARCHAR( 7 ) NOT NULL
               ,POSTAL_ADD_4 VARCHAR( 40 )
               ,EPF_IND CHAR( 1 )
               ,PRIMARY KEY (  UPLOADCODE  ,HOLDER_NO   ) 
) PRIMARY INDEX ( SOURCESYSTEM,EPF_IND  );

输出

CREATE TABLE   DP_SEDW.T_170UT_HOLDER_ACCT (
               BUSINESSDATE VARCHAR( 10 )
               ,SOURCESYSTEM VARCHAR( 5 )
               ,UPLOADCODE VARCHAR( 1 )
               ,HOLDER_NO VARCHAR( 7 ) NOT NULL
               ,POSTAL_ADD_4 VARCHAR( 40 )
               ,EPF_IND CHAR( 1 )
               ,PRIMARY KEY (UPLOADCODE ,HOLDER_NO   ) );

输入：不存在主键，且唯一索引有索引名称

CREATE SET TABLE "DP_TEDW"."T0409_INTERNAL_ORG_GRP_FUNCT",
     NO FALLBACK, NO BEFORE JOURNAL,
     NO AFTER JOURNAL
     ( Organization_Party_Id        INTEGER                NOT NULL
     , Function_Code                 SMALLINT               NOT NULL
     , Intern_Funct_Strt_Date     DATE FORMAT 'YYYY-MM-DD'  NOT NULL
     , Intern_Funct_End_Date     DATE FORMAT 'YYYY-MM-DD'
     )
PRIMARY INDEX ( Organization_Party_Id )
UNIQUE INDEX ux_t0409_intr_fn_1 ( Function_Code, Intern_Funct_Strt_Date )
UNIQUE INDEX ( Organization_Party_Id, Intern_Funct_Strt_Date );

输出

CREATE TABLE "DP_TEDW"."T0409_INTERNAL_ORG_GRP_FUNCT" 
         ( Organization_Party_Id        INTEGER             NOT NULL
         , Function_Code                 SMALLINT            NOT NULL
         , Intern_Funct_Strt_Date     DATE                  NOT NULL
         , Intern_Funct_End_Date     DATE
         ) 
DISTRIBUTE BY HASH ( Organization_Party_Id );
CREATE INDEX ux_t0409_intr_fn_1 ON "DP_TEDW"."T0409_INTERNAL_ORG_GRP_FUNCT" ( Function_Code, Intern_Funct_Strt_Date );
CREATE UNIQUE INDEX ON "DP_TEDW"."T0409_INTERNAL_ORG_GRP_FUNCT" ( Organization_Party_Id, Intern_Funct_Strt_Date );

输入：使用主键和非唯一主键索引的CREATE TABLE（未添加DISTRIBUTE BY HASH）

CREATE TABLE employee
 (
   EMP_NO INTEGER
 , DEPT_NO INTEGER
 , FIRST_NAME VARCHAR(20)
 , LAST_NAME CHAR(20)
 , SALARY DECIMAL(10,2)
 , ADDRESS VARCHAR(100)
 , CONSTRAINT pk_emp PRIMARY KEY ( EMP_NO )
   ) PRIMARY INDEX ( DEPT_NO ) ;

输出

CREATE TABLE employee 
 (
   EMP_NO INTEGER
 , DEPT_NO INTEGER
 , FIRST_NAME VARCHAR(20)
 , LAST_NAME CHAR(20)
 , SALARY DECIMAL(10,2)
 , ADDRESS VARCHAR(100)
 , CONSTRAINT pk_emp PRIMARY KEY ( EMP_NO )       
  )
;