dws
索引迁移
更新时间：2020/04/21 GMT+08:00
Teradata中CREATE INDEX的列和表名的顺序和GaussDB T、GaussDB A 和 DWS中不同。使用参数distributeByHash配置数据在集群节点间的分布方式。该工具不会添加DISTRIBUTE BY HASH用于创建具有主键和非唯一主索引的表。
输入：主键非主索引的超集，且仅有1列匹配
1 2 3 4 5 6 | 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);
|
输出
1 2 3 4 5 6 7 | CREATE TABLE good_5 (
column_1 INTEGER NOT NULL PRIMARY KEY
,column_2 INTEGER
,column_3 INTEGER NOT NULL
,column_4 INTEGER
)
;
|
输入：主键非主索引的超集，且无匹配的列
1 2 3 4 5 6 7 8 9 10 11 12 | 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 );
|
输出
1 2 3 4 5 6 7 8 | 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 ) );
|
输入：不存在主键，且唯一索引有名称
1 2 3 4 5 6 7 8 9 10 11 | 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 );
|
输出
1 2 3 4 5 6 7 8 9 | 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）
1 2 3 4 5 6 7 8 9 10 | 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 ) ;
|
输出
1 2 3 4 5 6 7 8 9 10 11 | 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 )
)
;
|
父主题： Teradata语法迁移
