Lu, Huayi 27019c2991 DWS TOOL 830.201 version

Reviewed-by: Hasko, Vladimir <vladimir.hasko@t-systems.com>
Reviewed-by: Pruthi, Vineet <vineet.pruthi@t-systems.com>
Co-authored-by: Lu, Huayi <luhuayi@huawei.com>
Co-committed-by: Lu, Huayi <luhuayi@huawei.com>

2024-05-16 07:35:25 +00:00

23 KiB

Raw Permalink Blame History

Indexes

When an index is created in GaussDB(DWS), a schema name cannot be specified along with the index name. The index will be automatically created in the schema where the index table is created.

Figure 1 Input: INDEX

Figure 2 Output: INDEX

Input - Function-based indexes by using CASE

A function-based index is an index that is created on the results of a function or expression on top of a column.

Output

1
2
3
4
5
6
7
8
9
CREATE
     UNIQUE index GCC_RSRC_ASSIGN_U1
          ON GCC_PLAN.GCC_RSRC_ASSIGN_T (
          (CASE
               WHEN( ENABLE_FLAG = 'Y' AND ASSIGN_TYPE = '13' AND WORK_ORDER_ID IS NOT NULL )
               THEN WORK_ORDER_ID
               ELSE NULL
          END)
     ) ;

The expression or function needs to be put inside brackets.

Input - Function-based indexes by using DECODE

1
2
3
4
5
6
7
8
9
CREATE UNIQUE index GCC_PLAN_N2
          ON GCC_PLAN.GCC_PLAN_T (
          DECODE (
               ENABLE_FLAG
               ,'Y'
               ,BUSINESS_ID
               ,NULL
          )
     ) ;  

Output

1
2
3
4
5
6
7
8
9
CREATE UNIQUE index GCC_PLAN_N2
          ON GCC_PLAN.GCC_PLAN_T (
          (DECODE (
               ENABLE_FLAG
               ,'Y'
               ,BUSINESS_ID
               ,NULL
          ))
     ) ;  

The expression or function needs to be put inside brackets.

ORA_HASH

ORA_HASH is a function that computes a hash value for a given expression or column. If this function is specified on the column(s) in the CREATE INDEX statement, this function will be removed.

Input

1
CREATE INDEX SD_WO.WO_WORK_ORDER_T_N3 on SD_WO.WO_WORK_ORDER_T (PROJECT_NUMBER, ORA_HASH(WORK_ORDER_NAME));

Output

1
2
3
4
5
6
CREATE
index WO_WORK_ORDER_T_N3
ON SD_WO.WO_WORK_ORDER_T (
PROJECT_NUMBER
,ORA_HASH( WORK_ORDER_NAME )
) ;

DECODE

If DECODE function in the CREATE INDEX statement is used as a part of a column, the following error will be reported: "syntax error at or near 'DECODE' (Script - gcc_plan_t.sql)".

Input

1
CREATE UNIQUE index GCC_PLAN.GCC_PLAN_N2 on GCC_PLAN.GCC_PLAN_T (DECODE(ENABLE_FLAG,'Y',BUSINESS_ID,NULL));

Output

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
CREATE
UNIQUE index GCC_PLAN_N2
ON GCC_PLAN.GCC_PLAN_T (
DECODE (
ENABLE_FLAG
,'Y'
,BUSINESS_ID
,NULL
)
) ;

CASE statement

The CASE statement is not supported in the CREATE INDEX statement.

Input

1
2
3
4
5
6
7
8
9
CREATE
     UNIQUE index GCC_RSRC_ASSIGN_U1
          ON GCC_PLAN.GCC_RSRC_ASSIGN_T (
          (CASE
               WHEN( ENABLE_FLAG = 'Y' AND ASSIGN_TYPE = '13' AND WORK_ORDER_ID IS NOT NULL )
               THEN WORK_ORDER_ID
               ELSE NULL
          END)
     ) ;

Output

1
2
3
4
5
6
7
CREATE UNIQUE INDEX gcc_rsrc_assign_u1 
  ON gcc_plan.gcc_rsrc_assign_t ( (( CASE 
                                                        WHEN( enable_flag = 'Y' 
                                                                  AND assign_type = '13' 
                                                                  AND work_order_id IS NOT NULL ) 
                                                  THEN work_order_id 
                                                  ELSE NULL END )) ); 

Parent topic: Schema Objects

23 KiB Raw Permalink Blame History

Indexes

23 KiB

Raw Permalink Blame History