Help Center> Data Warehouse Service (DWS)> Tool Guide> DSC> SQL Syntax Migration Reference> Teradata Syntax Migration> Schema Objects and Data Definition Language (DDL)> Migrating Indexes
Updated on 2024-04-28 GMT+08:00
View PDF

Migrating Indexes

The sequence of columns and table names in CREATE INDEX in Teradata is different from that in GaussDB T, GaussDB A, and GaussDB(DWS). Use the configuration parameter distributeByHash to configure how the data is distributed across the cluster nodes. The tool will not add DISTRIBUTE BY HASH which is used to create a table with Primary Key and Non-Unique Primary Index.

Input - Primary key is not superset of primary index and only one column is matched

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);

Output

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
          )
;

Input - Primary key is not superset of primary index and no column is matched

 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  );

Output

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   ) );

Input - No primary key and unique index has index name

 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 );

Output

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 );

Input - CREATE TABLE with Primary Key and Non-Unique Primary Index (DISTRIBUTE BY HASH is not added)

 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 ) ;

Output

 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 )       
  )
;