更新时间:2024-03-04 GMT+08:00
分享

表(Oracle)

CREATE TABLE

Oracle的CREATE TABLE语句用于创建表。GaussDB(DWS)直接支持该语句,无需迁移。

ALTER TABLE

Oracle的ALTER TABLE语句用于新增、重命名、修改或删除表列。GaussDB(DWS)直接支持该语句,无需迁移。

PRIMARY KEY

Oracle中如果存在两张表具有相同的主键字段,则在执行ALTER TABLE时需加上表名进行区分。

输入:PRIMARY KEY

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
CREATE TABLE CTP_ARM_CONFIG 
    ( HOSTNAME VARCHAR2(50), 
  OPNAME VARCHAR2(50), 
  PARAMTYPE VARCHAR2(2), 
  PARAMVALUE NUMBER(*,0), 
  MODIFYDATE DATE
    ) SEGMENT CREATION DEFERRED 
   PCTFREE 10 PCTUSED 0 INITRANS 1 MAXTRANS 255 
  NOCOMPRESS LOGGING
   STORAGE( PCTINCREASE 0
   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
   TABLESPACE SPMS_DATA ;

 ALTER TABLE CTP_ARM_CONFIG ADD CONSTRAINT PKCTP_ARM_CONFIG PRIMARY KEY (HOSTNAME, OPNAME)
   USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
   STORAGE( PCTINCREASE 0
   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
   TABLESPACE SPMS_DATA  ENABLE;

输出

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
CREATE
      TABLE
           CTP_ARM_CONFIG (
                HOSTNAME VARCHAR2 (50)
                ,OPNAME VARCHAR2 (50)
                ,PARAMTYPE VARCHAR2 (2)
                ,PARAMVALUE NUMBER (
                     38
                     ,0
                )
                ,MODIFYDATE DATE
                ,CONSTRAINT PKCTP_ARM_CONFIG PRIMARY KEY (
                     HOSTNAME
                     ,OPNAME
                )
           ) /*SEGMENT CREATION DEFERRED*/
           /*PCTFREE 10*/
           /*PCTUSED 0*/
           /*INITRANS 1*/
           /*MAXTRANS 255*/
           /*NOCOMPRESS*/
           /*LOGGING*/
           /*STORAGE(  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)*/
           /*TABLESPACE SPMS_DATA */
 ;

UNIQUE约束

以下ALTER TABLE语句包含约束,如果在GaussDB(DWS)直接调用会报错:Cannot create index whose evaluation cannot be enforced to remote nodes.

该约束迁移和PRIMARY KEY类似。如果已有PRIMARY KEY/UNIQUE约束,无需迁移,保持原样。

输入

1
2
3
4
5
6
7
8
CREATE
     TABLE
          GCC_PLAN.T1033 (
               ROLLOUT_PLAN_LINE_ID NUMBER NOT NULL
               ,UDF_FIELD_VALUE_ID NUMBER NOT NULL
          ) ;
ALTER TABLE
     GCC_PLAN.T1033 ADD CONSTRAINT UDF_FIELD_VALUE_ID_PK UNIQUE (UDF_FIELD_VALUE_ID) ;

输出

1
2
3
4
5
6
7
CREATE TABLE 
        GCC_PLAN.T1033
                             (
                               ROLLOUT_PLAN_LINE_ID NUMBER NOT NULL
                               ,UDF_FIELD_VALUE_ID NUMBER NOT NULL
                               ,CONSTRAINT UDF_FIELD_VALUE_ID_PK UNIQUE (UDF_FIELD_VALUE_ID)
                              ) ;

NULL约束

在以下包中声明局部变量时不支持NULL约束:

L_CONTRACT_DISTRIBUTE_STATUS SAD_DISTRIBUTION_HEADERS_T.STATUS%TYPE NULL ;

输入

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
CREATE OR REPLACE FUNCTION CONTRACT_DISTRIBUTE_STATUS_S2(PI_CONTRACT_NUMBER IN VARCHAR2)
  RETURN VARCHAR2 IS
  L_CONTRACT_DISTRIBUTE_STATUS BAS_SUBTYPE_PKG.STATUS NULL;

BEGIN

  FOR CUR_CONTRACT IN (SELECT HT.CONTRACT_STATUS
                         FROM SAD_CONTRACTS_V HT
                        WHERE HT.HTH = PI_CONTRACT_NUMBER) 
  LOOP
    IF CUR_CONTRACT.CONTRACT_STATUS = 0 THEN
      L_CONTRACT_DISTRIBUTE_STATUS := 'Cancel';
    ELSE
      L_CONTRACT_DISTRIBUTE_STATUS := BAS_SUBTYPE_PKG.G_HEADER_WAITING_SPLIT_STATUS;
    END IF;
  END LOOP;

  RETURN L_CONTRACT_DISTRIBUTE_STATUS;

END CONTRACT_DISTRIBUTE_STATUS_S2;
/

输出

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
CREATE OR REPLACE FUNCTION CONTRACT_DISTRIBUTE_STATUS_S2 
  ( PI_CONTRACT_NUMBER IN VARCHAR2 )
RETURN VARCHAR2 
PACKAGE 
IS 
 L_CONTRACT_DISTRIBUTE_STATUS BAS_SUBTYPE_PKG.STATUS /*NULL*/;
BEGIN
     FOR CUR_CONTRACT IN ( SELECT HT.CONTRACT_STATUS
        FROM SAD_CONTRACTS_V HT
       WHERE HT.HTH = PI_CONTRACT_NUMBER ) 
  LOOP
            IF CUR_CONTRACT.CONTRACT_STATUS = 0 THEN
               L_CONTRACT_DISTRIBUTE_STATUS := 'Cancel' ;

   ELSE
    L_CONTRACT_DISTRIBUTE_STATUS := BAS_SUBTYPE_PKG.G_HEADER_WAITING_SPLIT_STATUS ;

   END IF ;

     END LOOP ;

     RETURN L_CONTRACT_DISTRIBUTE_STATUS ;
END ;
/

未创建索引

如果ALTER TABLE中使用了INDEX或STORAGE参数,需要删掉。需要在CREATE TABLE中添加约束。

输入:PRIMARY KEY

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
CREATE TABLE CTP_ARM_CONFIG
( HOSTNAME VARCHAR2(50),
OPNAME VARCHAR2(50),
PARAMTYPE VARCHAR2(2),
PARAMVALUE NUMBER(*,0),
MODIFYDATE DATE
) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 0 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE( PCTINCREASE 0
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE SPMS_DATA ;
ALTER TABLE CTP_ARM_CONFIG ADD CONSTRAINT PKCTP_ARM_CONFIG PRIMARY KEY
(HOSTNAME, OPNAME)
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE( PCTINCREASE 0
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE SPMS_DATA ENABLE;

输出

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
CREATE TABLE
CTP_ARM_CONFIG (
HOSTNAME VARCHAR2 (50)
,OPNAME VARCHAR2 (50)
,PARAMTYPE VARCHAR2 (2)
,PARAMVALUE NUMBER (
38
,0
)
,MODIFYDATE DATE
,CONSTRAINT PKCTP_ARM_CONFIG PRIMARY KEY (
HOSTNAME
,OPNAME
)
) /*SEGMENT CREATION DEFERRED*/
/*PCTFREE 10*/
/*PCTUSED 0*/
/*INITRANS 1*/
/*MAXTRANS 255*/
/*NOCOMPRESS*/
/*LOGGING*/
/*STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE
DEFAULT)*/
/*TABLESPACE SPMS_DATA */
;

分区

大表和索引的维护越来越耗费时间和资源。同时,这些对象会导致数据访问性能明显降低。表和索引的分区可从各方面提升性能、便于维护。

图1 表的分区和子分区

DSC支持范围分区。

该工具不支持以下分区/子分区(在迁移脚本中会被注释掉):

  • 列表分区
  • Hash分区
  • 范围子分区
  • 列表子分区
  • Hash子分区

未来可能会支持当前不支持的分区/子分区。该工具中,用户可设置配置参数,启用/禁用对不支持语句的注释功能。详情请参见Oracle配置参数

  • PARTITION BY HASH

    Hash分区是一种分区技术,其中Hash算法用于在不同分区(子表)之间均匀分配行。通常在无法进行范围分区时使用该技术,例如通过员工ID、产品ID等进行分区。DSC不支持PARTITION BY HASH和SUBPARTITION BY HASH,且会注释掉这些语句。

    输入:HASH PARTITION

    CREATE TABLE dept (deptno NUMBER, deptname VARCHAR(32)) PARTITION BY HASH(deptno) PARTITIONS 16;

    输出

    CREATE TABLE dept ( deptno NUMBER ,deptname VARCHAR( 32 ) ) /* PARTITION BY HASH(deptno) PARTITIONS 16 */ ;

    输入:HASH PARTITION,不使用分区名

    CREATE TABLE dept (deptno NUMBER, deptname VARCHAR(32))
          PARTITION BY HASH(deptno) PARTITIONS 16;

    输出

    CREATE TABLE dept (deptno NUMBER, deptname VARCHAR(32))
      /*    PARTITION BY HASH(deptno) PARTITIONS 16 */;

    输入:HASH SUBPARTITION

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    16
    CREATE TABLE sales
       ( prod_id       NUMBER(6)
       , cust_id       NUMBER
       , time_id       DATE
       , channel_id    CHAR(1)
       , promo_id      NUMBER(6)
       , quantity_sold NUMBER(3)
       , amount_sold   NUMBER(10,2)
       )
      PARTITION BY RANGE (time_id) SUBPARTITION BY HASH (cust_id)
       SUBPARTITIONS 8STORE IN (ts1, ts2, ts3, ts4)
      ( PARTITION sales_q1_2006 VALUES LESS THAN (TO_DATE('01-APR-2006','dd-MON-yyyy'))
      , PARTITION sales_q2_2006 VALUES LESS THAN (TO_DATE('01-JUL-2006','dd-MON-yyyy'))
      , PARTITION sales_q3_2006 VALUES LESS THAN (TO_DATE('01-OCT-2006','dd-MON-yyyy'))
      , PARTITION sales_q4_2006 VALUES LESS THAN (TO_DATE('01-JAN-2007','dd-MON-yyyy'))
      );
    
    输出
     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    16
    CREATE TABLE sales
       ( prod_id       NUMBER(6)
       , cust_id       NUMBER
       , time_id       DATE
       , channel_id    CHAR(1)
       , promo_id      NUMBER(6)
       , quantity_sold NUMBER(3)
       , amount_sold   NUMBER(10,2)
       )
      PARTITION BY RANGE (time_id) /*SUBPARTITION BY HASH (cust_id)
       SUBPARTITIONS 8  STORE IN (ts1, ts2, ts3, ts4) */
      ( PARTITION sales_q1_2006 VALUES LESS THAN (TO_DATE('01-APR-2006','dd-MON-yyyy'))
      , PARTITION sales_q2_2006 VALUES LESS THAN (TO_DATE('01-JUL-2006','dd-MON-yyyy'))
      , PARTITION sales_q3_2006 VALUES LESS THAN (TO_DATE('01-OCT-2006','dd-MON-yyyy'))
      , PARTITION sales_q4_2006 VALUES LESS THAN (TO_DATE('01-JAN-2007','dd-MON-yyyy'))
      );
    
  • PARTITION BY LIST

    列表分区是一种分区技术,在每个分区的说明中指定分区键的离散值列表。DSC不支持PARTITION BY LIST和SUBPARTITION BY LIST,且会注释掉这些语句。

    输入:LIST PARTITION

    1
    CREATE TABLE sales_by_region (item# INTEGER, qty INTEGER, store_name VARCHAR(30), state_code VARCHAR(2), sale_date DATE) STORAGE(INITIAL 10K NEXT 20K) TABLESPACE tbs5 PARTITION BY LIST (state_code) ( PARTITION region_east VALUES ('MA','NY','CT','NH','ME','MD','VA','PA','NJ') STORAGE (INITIAL 8M) TABLESPACE tbs8, PARTITION region_west VALUES ('CA','AZ','NM','OR','WA','UT','NV','CO') NOLOGGING, PARTITION region_south VALUES ('TX','KY','TN','LA','MS','AR','AL','GA'), PARTITION region_central VALUES ('OH','ND','SD','MO','IL','MI','IA'), PARTITION region_null VALUES (NULL), PARTITION region_unknown VALUES (DEFAULT) );
    

    输出

    1
    CREATE UNLOGGED TABLE sales_by_region ( item# INTEGER ,qty INTEGER ,store_name VARCHAR( 30 ) ,state_code VARCHAR( 2 ) ,sale_date DATE ) TABLESPACE tbs5 /* PARTITION BY LIST(state_code)(PARTITION region_east VALUES('MA','NY','CT','NH','ME','MD','VA','PA','NJ')  TABLESPACE tbs8, PARTITION region_west VALUES('CA','AZ','NM','OR','WA','UT','NV','CO') , PARTITION region_south VALUES('TX','KY','TN','LA','MS','AR','AL','GA'), PARTITION region_central VALUES('OH','ND','SD','MO','IL','MI','IA'), PARTITION region_null VALUES(NULL), PARTITION region_unknown VALUES(DEFAULT) ) */ ;
    

    输入:LIST PARTITION(使用STORAGE参数)

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    CREATE TABLE store_master 
              ( Store_id NUMBER
              , Store_address VARCHAR2 (40)
              , City VARCHAR2 (30)
              , State VARCHAR2 (2)
              , zip VARCHAR2 (10)
              , manager_id NUMBER
              ) 
        /*TABLESPACE users*/ 
        STORAGE ( INITIAL 100 k NEXT 100 k 
         PCTINCREASE 0 ) 
       PARTITION BY LIST (city) 
            ( PARTITION south_florida 
              VALUES ( 'MIA', 'ORL' ) 
              /*TABLESPACE users*/ 
              STORAGE ( INITIAL 100 k NEXT 100 
              k PCTINCREASE 0 )
            , PARTITION north_florida 
              VALUES ( 'JAC', 'TAM', 'PEN' ) 
              /*TABLESPACE users*/ 
              STORAGE ( INITIAL 100 k NEXT 100 
              k PCTINCREASE 0 )
            , PARTITION south_georga VALUES 
              ( 'BRU', 'WAY', 'VAL' ) 
              /*TABLESPACE users*/ 
              STORAGE ( INITIAL 100 k NEXT 100 
              k PCTINCREASE 0 )
            , PARTITION north_georgia 
              VALUES ( 'ATL', 'SAV', NULL )
            );
    

    输出

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    CREATE TABLE store_master 
              ( Store_id NUMBER
              , Store_address VARCHAR2 (40)
              , City VARCHAR2 (30)
              , State VARCHAR2 (2)
              , zip VARCHAR2 (10)
              , manager_id NUMBER
              ) 
        /*TABLESPACE users*/ 
        STORAGE ( INITIAL 100 k NEXT 100 k );
    

    输入:LIST PARTITION TABLE,基于其他TABLE

    CREATE TABLE tab1_list 
          PARTITION BY LIST (col1) 
             ( partition part1 VALUES ( 1 )
              , partition part2 VALUES ( 2, 
               3, 4 )
              , partition part3 VALUES 
              (DEFAULT)
              ) 
     AS 
     SELECT *
       FROM  tab1;

    输出

    1
    2
    3
    4
    CREATE TABLE tab1_list 
     AS 
     ( SELECT *
       FROM  tab1 );
    

    输入:LIST PARTITION,使用SUBPARTITIONS

    1
    CREATE TABLE big_t_list PARTITION BY LIST(n10) (partition part1 VALUES (1) ,partition part2 VALUES (2,3,4) ,partition part3 VALUES (DEFAULT)) AS SELECT * FROM big_t;
    

    输出

    1
    CREATE TABLE big_t_list /* PARTITION BY LIST(n10)(partition part1 VALUES(1) ,partition part2 VALUES(2,3,4) ,partition part3 VALUES(DEFAULT))  */ AS ( SELECT * FROM big_t ) ;
    

    输入:LIST PARTITION,使用SUBPARTITION TEMPLATE

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    CREATE TABLE q1_sales_by_region 
              ( deptno NUMBER
              , deptname varchar2 (20)
              , quarterly_sales NUMBER 
              (10,2)
              , state varchar2 (2)
              ) 
       PARTITION BY LIST (state) 
             SUBPARTITION BY RANGE 
             (quarterly_sales) 
             SUBPARTITION TEMPLATE 
             ( SUBPARTITION original VALUES 
             LESS THAN (1001)
             , SUBPARTITION acquired VALUES 
             LESS THAN (8001)
             , SUBPARTITION recent VALUES 
             LESS THAN (MAXVALUE)
             )
        ( PARTITION q1_northwest VALUES 
         ( 'OR', 'WA' )
        , PARTITION q1_southwest VALUES 
         ( 'AZ', 'UT', 'NM' )
        , PARTITION q1_northeast VALUES 
         ( 'NY', 'VM', 'NJ' )
        , PARTITION q1_southcentral VALUES 
         ( 'OK', 'TX' )
        );
    

    输出

    1
    2
    3
    4
    5
    6
    CREATE TABLE q1_sales_by_region 
              ( deptno NUMBER
              , deptname varchar2 (20)
              , quarterly_sales NUMBER (10,2)
              , state varchar2 (2)
              );
    
  • PARTITION BY RANGE

    范围分区是一种分区技术,将不同范围数据分别存储在不同的子表中。当用户需要将不同范围的数据(例如日期字段)存储在一起时,范围分区很有用。DSC支持 PARTITION BY RANGE,不支持SUBPARTITION BY RANGE,且会注释掉该语句。

    输入:RANGE PARTITION(使用STORAGE参数)

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    CREATE
         TABLE
              CCM_TA550002_H (
                   STRU_ID VARCHAR2 (10)
                   ,ORGAN1_NO VARCHAR2 (10)
                   ,ORGAN2_NO VARCHAR2 (10)
              ) partition BY range (ORGAN2_NO) (
                   partition CCM_TA550002_01
                   VALUES LESS than ('00100') /* TABLESPACE users */
                   /*pctfree 10*/
                   /*initrans 1*/
                   /*storage(initial 256 K NEXT 256 K minextents 1 maxextents unlimited  )*/
                   ,partition CCM_TA550002_02
                   VALUES LESS than ('00200') /* TABLESPACE users */
                   /*pctfree 10*/
                   /*initrans 1*/
                   /* storage ( initial 256 K NEXT
    256K  minextents 1
    maxextents unlimited
    pctincrease 0 )*/
    

    输出

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    CREATE TABLE CCM_TA550002_H 
              ( STRU_ID VARCHAR2 (10)
              , ORGAN1_NO VARCHAR2 (10)
              , ORGAN2_NO VARCHAR2 (10)
              ) 
        partition BY range (ORGAN2_NO) 
                 ( partition CCM_TA550002_01 VALUES LESS 
                   than ('00100') 
                   /*TABLESPACE users*/
                 , partition CCM_TA550002_02 VALUES LESS 
                   than ('00200') 
                   /*TABLESPACE users*/
                 );
    

    输入:RANGE PARTITION,使用SUBPARTITIONS

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    CREATE TABLE composite_rng_list (
    cust_id     NUMBER(10), 
    cust_name   VARCHAR2(25), 
    cust_state  VARCHAR2(2), 
    time_id     DATE)
    PARTITION BY RANGE(time_id) 
    SUBPARTITION BY LIST (cust_state)
    SUBPARTITION TEMPLATE(
    SUBPARTITION west VALUES ('OR', 'WA') TABLESPACE part1,
    SUBPARTITION east VALUES ('NY', 'CT') TABLESPACE part2,
    SUBPARTITION cent VALUES ('OK', 'TX') TABLESPACE part3) ( 
    PARTITION per1 VALUES LESS THAN (TO_DATE('01/01/2000','DD/MM/YYYY')),
    PARTITION per2 VALUES LESS THAN (TO_DATE('01/01/2005','DD/MM/YYYY')),
    PARTITION per3 VALUES LESS THAN (TO_DATE('01/01/2010','DD/MM/YYYY')),
    PARTITION future VALUES LESS THAN(MAXVALUE));
    

    输出

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    CREATE TABLE composite_rng_list (
    cust_id     NUMBER(10), 
    cust_name   VARCHAR2(25), 
    cust_state  VARCHAR2(2), 
    time_id     DATE)
    PARTITION BY RANGE(time_id) 
    /*SUBPARTITION BY LIST (cust_state)
    SUBPARTITION TEMPLATE(
    SUBPARTITION west VALUES ('OR', 'WA') TABLESPACE part1,
    SUBPARTITION east VALUES ('NY', 'CT') TABLESPACE part2,
    SUBPARTITION cent VALUES ('OK', 'TX') TABLESPACE part3)*/ ( 
    PARTITION per1 VALUES LESS THAN (TO_DATE('01/01/2000','DD/MM/YYYY')),
    PARTITION per2 VALUES LESS THAN (TO_DATE('01/01/2005','DD/MM/YYYY')),
    PARTITION per3 VALUES LESS THAN (TO_DATE('01/01/2010','DD/MM/YYYY')),
    PARTITION future VALUES LESS THAN(MAXVALUE));
    
    输入:RANGE PARTITION,使用SUBPARTITION TEMPLATE
     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    CREATE TABLE composite_rng_rng (
    cust_id     NUMBER(10), 
    cust_name   VARCHAR2(25), 
    cust_state  VARCHAR2(2), 
    time_id     DATE)
    PARTITION BY RANGE(time_id) 
    SUBPARTITION BY RANGE (cust_id)
    SUBPARTITION TEMPLATE(
    SUBPARTITION original VALUES LESS THAN (1001) TABLESPACE part1,
    SUBPARTITION acquired VALUES LESS THAN (8001) TABLESPACE part2,
    SUBPARTITION recent VALUES LESS THAN (MAXVALUE) TABLESPACE part3) ( 
    PARTITION per1 VALUES LESS THAN (TO_DATE('01/01/2000','DD/MM/YYYY')),
    PARTITION per2 VALUES LESS THAN (TO_DATE('01/01/2005','DD/MM/YYYY')),
    PARTITION per3 VALUES LESS THAN (TO_DATE('01/01/2010','DD/MM/YYYY')),
    PARTITION future VALUES LESS THAN (MAXVALUE));
    

    输出

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    CREATE TABLE composite_rng_rng (
    cust_id     NUMBER(10), 
    cust_name   VARCHAR2(25), 
    cust_state  VARCHAR2(2), 
    time_id     DATE)
    PARTITION BY RANGE(time_id) 
    /*SUBPARTITION BY RANGE (cust_id)
    SUBPARTITION TEMPLATE(
    SUBPARTITION original VALUES LESS THAN (1001) TABLESPACE part1,
    SUBPARTITION acquired VALUES LESS THAN (8001) TABLESPACE part2,
    SUBPARTITION recent VALUES LESS THAN (MAXVALUE) TABLESPACE part3)*/ ( 
    PARTITION per1 VALUES LESS THAN (TO_DATE('01/01/2000','DD/MM/YYYY')),
    PARTITION per2 VALUES LESS THAN (TO_DATE('01/01/2005','DD/MM/YYYY')),
    PARTITION per3 VALUES LESS THAN (TO_DATE('01/01/2010','DD/MM/YYYY')),
    PARTITION future VALUES LESS THAN (MAXVALUE));
    

    分区表的PRIMARY KEY/UNIQUE约束

    如果CREATE TABLE语句包含范围/Hash/列表分区,则添加约束会产生如下错误:

    Invalid PRIMARY KEY/UNIQUE constraint for partitioned table

    注意:PRIMARY KEY/UNIQUE约束所在的列必须包含PARTITION KEY。

    脚本: wo_integrate_log_t.SQL, wo_change_log_t.SQL

    输入:

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    CREATE TABLE SD_WO.WO_INTEGRATE_LOG_T
    (
    LOG_ID            NUMBER not null,
    PROJECT_NUMBER    VARCHAR2(40),
    MESSAGE_ID        VARCHAR2(100),
    BUSINESS_ID       VARCHAR2(100),
    BUSINESS_TYPE     VARCHAR2(100),
    INTEGRATE_CONTENT CLOB,
    OPERATION_RESULT  VARCHAR2(100),
    FAILED_MSG        VARCHAR2(4000),
    HOST_NAME         VARCHAR2(100) not null,
    CREATED_BY        NUMBER not null,
    CREATION_DATE     DATE not null,
    LAST_UPDATED_BY   NUMBER not null,
    LAST_UPDATE_DATE  DATE not null,
    SOURCE_CODE       VARCHAR2(100),
    TENANT_ID         NUMBER
    )
    partition by range (CREATION_DATE)
    (
    partition P2018 values less than (TO_DATE(' 2018-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SDWO_DATA,
    partition SYS_P53873 values less than (TO_DATE(' 2018-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SDWO_DATA,
    partition SYS_P104273 values less than (TO_DATE(' 2018-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SDWO_DATA,
    partition SYS_P105533 values less than (TO_DATE(' 2019-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SDWO_DATA,
    partition SYS_P108153 values less than (TO_DATE(' 2019-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SDWO_DATA,
    partition SYS_P127173 values less than (TO_DATE(' 2019-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SDWO_DATA,
    partition SYS_P130313 values less than (TO_DATE(' 2019-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SDWO_DATA
    );
    alter table SD_WO.WO_INTEGRATE_LOG_T
    add constraint WO_INTEGRATE_LOG_PK primary key (LOG_ID);
    create index SD_WO.WO_INTEGRATE_LOG_N1 on SD_WO.WO_INTEGRATE_LOG_T (BUSINESS_ID);
    create index SD_WO.WO_INTEGRATE_LOG_N2 on SD_WO.WO_INTEGRATE_LOG_T (CREATION_DATE, BUSINESS_TYPE);
    create index SD_WO.WO_INTEGRATE_LOG_N3 on SD_WO.WO_INTEGRATE_LOG_T (PROJECT_NUMBER, BUSINESS_TYPE);
    

    输出:

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    CREATE TABLE
    SD_WO.WO_INTEGRATE_LOG_T (
    LOG_ID NUMBER NOT NULL
    ,PROJECT_NUMBER VARCHAR2 (40)
    ,MESSAGE_ID VARCHAR2 (100)
    ,BUSINESS_ID VARCHAR2 (100)
    ,BUSINESS_TYPE VARCHAR2 (100)
    ,INTEGRATE_CONTENT CLOB
    ,OPERATION_RESULT VARCHAR2 (100)
    ,FAILED_MSG VARCHAR2 (4000)
    ,HOST_NAME VARCHAR2 (100) NOT NULL
    ,CREATED_BY NUMBER NOT NULL
    ,CREATION_DATE DATE NOT NULL
    ,LAST_UPDATED_BY NUMBER NOT NULL
    ,LAST_UPDATE_DATE DATE NOT NULL
    ,SOURCE_CODE VARCHAR2 (100)
    ,TENANT_ID NUMBER
    ,CONSTRAINT WO_INTEGRATE_LOG_PK PRIMARY KEY (LOG_ID)
    ) partition BY range (CREATION_DATE) (
    partition P2018
    VALUES LESS than (
    TO_DATE( ' 2018-10-01 00:00:00' ,'SYYYY-MM-DD HH24:MI:SS'/*, 'NLS_CALENDAR=GREGORIAN'*/ )
    ) /* tablespace SDWO_DATA */
    ,partition SYS_P53873
    VALUES LESS than (
    TO_DATE( ' 2018-11-01 00:00:00' ,'SYYYY-MM-DD HH24:MI:SS'/*, 'NLS_CALENDAR=GREGORIAN'*/ )
    ) /* tablespace SDWO_DATA */
    ,partition SYS_P104273
    VALUES LESS than (
    TO_DATE( ' 2018-12-01 00:00:00' ,'SYYYY-MM-DD HH24:MI:SS'/*, 'NLS_CALENDAR=GREGORIAN'*/ )
    ) /* tablespace SDWO_DATA */
    ,partition SYS_P105533
    VALUES LESS than (
    TO_DATE( ' 2019-01-01 00:00:00' ,'SYYYY-MM-DD HH24:MI:SS'/*, 'NLS_CALENDAR=GREGORIAN'*/ )
    ) /* tablespace SDWO_DATA */
    ,partition SYS_P108153
    VALUES LESS than (
    TO_DATE( ' 2019-02-01 00:00:00' ,'SYYYY-MM-DD HH24:MI:SS'/*, 'NLS_CALENDAR=GREGORIAN'*/ )
    ) /* tablespace SDWO_DATA */
    ,partition SYS_P127173
    VALUES LESS than (
    TO_DATE( ' 2019-03-01 00:00:00' ,'SYYYY-MM-DD HH24:MI:SS'/*, 'NLS_CALENDAR=GREGORIAN'*/ )
    ) /* tablespace SDWO_DATA */
    ,partition SYS_P130313
    VALUES LESS than (
    TO_DATE( ' 2019-04-01 00:00:00' ,'SYYYY-MM-DD HH24:MI:SS'/*, 'NLS_CALENDAR=GREGORIAN'*/ )
    ) /* tablespace SDWO_DATA */
    ) ;
    CREATE
    index WO_INTEGRATE_LOG_N1
    ON SD_WO.WO_INTEGRATE_LOG_T (BUSINESS_ID) LOCAL ;
    CREATE
    index WO_INTEGRATE_LOG_N2
    ON SD_WO.WO_INTEGRATE_LOG_T (
    CREATION_DATE
    ,BUSINESS_TYPE
    ) LOCAL ;
    CREATE
    index WO_INTEGRATE_LOG_N3
    ON SD_WO.WO_INTEGRATE_LOG_T (
    PROJECT_NUMBER
    ,BUSINESS_TYPE
    ) LOCAL ;
    

    输入

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    CREATE TABLE SD_WO.WO_INTEGRATE_LOG_T
    (
      LOG_ID            NUMBER not null,
      PROJECT_NUMBER    VARCHAR2(40),
      MESSAGE_ID        VARCHAR2(100),
      BUSINESS_ID       VARCHAR2(100),
      BUSINESS_TYPE     VARCHAR2(100),
      INTEGRATE_CONTENT CLOB,
      OPERATION_RESULT  VARCHAR2(100),
      FAILED_MSG        VARCHAR2(4000),
      HOST_NAME         VARCHAR2(100) not null,
      CREATED_BY        NUMBER not null,
      CREATION_DATE     DATE not null,
      LAST_UPDATED_BY   NUMBER not null,
      LAST_UPDATE_DATE  DATE not null,
      SOURCE_CODE       VARCHAR2(100),
      TENANT_ID         NUMBER
    )
    partition by range (CREATION_DATE)
    (
      partition P2018 values less than (TO_DATE(' 2018-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
        tablespace SDWO_DATA,
      partition SYS_P53873 values less than (TO_DATE(' 2018-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
        tablespace SDWO_DATA,
      partition SYS_P104273 values less than (TO_DATE(' 2018-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
        tablespace SDWO_DATA,
      partition SYS_P105533 values less than (TO_DATE(' 2019-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
        tablespace SDWO_DATA,
      partition SYS_P108153 values less than (TO_DATE(' 2019-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
        tablespace SDWO_DATA,
      partition SYS_P127173 values less than (TO_DATE(' 2019-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
        tablespace SDWO_DATA,
      partition SYS_P130313 values less than (TO_DATE(' 2019-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
        tablespace SDWO_DATA
    );
    
    alter table SD_WO.WO_INTEGRATE_LOG_T
      add constraint WO_INTEGRATE_LOG_PK primary key (LOG_ID);
    create index SD_WO.WO_INTEGRATE_LOG_N1 on SD_WO.WO_INTEGRATE_LOG_T (BUSINESS_ID);
    create index SD_WO.WO_INTEGRATE_LOG_N2 on SD_WO.WO_INTEGRATE_LOG_T (CREATION_DATE, BUSINESS_TYPE);
    create index SD_WO.WO_INTEGRATE_LOG_N3 on SD_WO.WO_INTEGRATE_LOG_T (PROJECT_NUMBER, BUSINESS_TYPE);
    

    输出:

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    CREATE TABLE SD_WO.WO_INTEGRATE_LOG_T
    (
      LOG_ID            NUMBER not null,
      PROJECT_NUMBER    VARCHAR2(40),
      MESSAGE_ID        VARCHAR2(100),
      BUSINESS_ID       VARCHAR2(100),
      BUSINESS_TYPE     VARCHAR2(100),
      INTEGRATE_CONTENT CLOB,
      OPERATION_RESULT  VARCHAR2(100),
      FAILED_MSG        VARCHAR2(4000),
      HOST_NAME         VARCHAR2(100) not null,
      CREATED_BY        NUMBER not null,
      CREATION_DATE     DATE not null,
      LAST_UPDATED_BY   NUMBER not null,
      LAST_UPDATE_DATE  DATE not null,
      SOURCE_CODE       VARCHAR2(100),
      TENANT_ID         NUMBER
    )
    partition by range (CREATION_DATE)
    (
      partition P2018 values less than (TO_DATE(' 2018-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
        tablespace SDWO_DATA,
      partition SYS_P53873 values less than (TO_DATE(' 2018-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
        tablespace SDWO_DATA,
      partition SYS_P104273 values less than (TO_DATE(' 2018-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
        tablespace SDWO_DATA,
      partition SYS_P105533 values less than (TO_DATE(' 2019-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
        tablespace SDWO_DATA,
      partition SYS_P108153 values less than (TO_DATE(' 2019-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
        tablespace SDWO_DATA,
      partition SYS_P127173 values less than (TO_DATE(' 2019-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
        tablespace SDWO_DATA,
      partition SYS_P130313 values less than (TO_DATE(' 2019-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
        tablespace SDWO_DATA
    );
    
    ALTER TABLE SD_WO.WO_INTEGRATE_LOG_T
      add constraint WO_INTEGRATE_LOG_PK primary key (LOG_ID);
    create index SD_WO.WO_INTEGRATE_LOG_N1 on SD_WO.WO_INTEGRATE_LOG_T (BUSINESS_ID);
    create index SD_WO.WO_INTEGRATE_LOG_N2 on SD_WO.WO_INTEGRATE_LOG_T (CREATION_DATE, BUSINESS_TYPE);
    create index SD_WO.WO_INTEGRATE_LOG_N3 on SD_WO.WO_INTEGRATE_LOG_T (PROJECT_NUMBER, BUSINESS_TYPE);
    

数据类型

删除数据类型中的BYTE关键字。

Oracle语法

迁移后语法

1
2
3
4
5
6
CREATE TABLE TBL_ORACLE
 (
   ID     Number,
   Name   VARCHAR2(100 BYTE),
  ADDRESS VARCHAR2(200 BYTE)
  );
1
2
3
4
5
6
CREATE TABLE  TBL_ORACLE
 (
     ID NUMBER
     ,Name VARCHAR2 (100)
     ,ADDRESS VARCHAR2 (200)
 ) ; 

分区(注释分区)

oracle配置参数中“#分区表唯一或主键约束”为“comment_partition”。

Oracle语法

迁移后语法

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
CREATE TABLE TBL_ORACLE
(
   ID     Number,
   Name   VARCHAR2(100 BYTE),
  ADDRESS VARCHAR2(200 BYTE)
  )
TABLESPACE space1
PCTUSED    40
PCTFREE    0
INITRANS   1
MAXTRANS   255
NOLOGGING
PARTITION BY RANGE (ID)
(  
  PARTITION PART_2010 VALUES LESS THAN (10)
    NOLOGGING, 
  PARTITION PART_2011 VALUES LESS THAN (20)
    NOLOGGING ,  
  PARTITION PART_2012 VALUES LESS THAN (MAXVALUE)
    NOLOGGING
)
ENABLE ROW MOVEMENT;


ALTER TABLE TBL_ORACLE ADD CONSTRAINT SAMPLE_PK PRIMARY KEY (ID);
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
CREATE UNLOGGED TABLE  TBL_ORACLE 
(
               ID NUMBER
               ,Name VARCHAR2 (100)
               ,ADDRESS VARCHAR2 (200)
               ,CONSTRAINT SAMPLE_PK PRIMARY KEY (ID)
) 
 TABLESPACE space1
          /*PCTUSED 40*/
          PCTFREE 0
          INITRANS 1
          MAXTRANS 255
 /* PARTITION BY RANGE(ID)(PARTITION PART_2010 VALUES LESS THAN(10) , 
PARTITION PART_2011 VALUES LESS THAN(20)  , PARTITION PART_2012 VALUES LESS THAN(MAXVALUE)  ) 
ENABLE ROW MOVEMENT */
 ; 

分区(注释约束)

oracle配置参数中“#分区表唯一或主键约束”为“comment_unique”。

Oracle语法

迁移后语法

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
CREATE TABLE TBL_ORACLE
(
   ID     Number,
   Name   VARCHAR2(100 BYTE),
  ADDRESS VARCHAR2(200 BYTE)
  )
TABLESPACE space1
PCTUSED    40
PCTFREE    0
INITRANS   1
MAXTRANS   255
NOLOGGING
PARTITION BY RANGE (ID)
(  
  PARTITION PART_2010 VALUES LESS THAN (10)
    NOLOGGING, 
  PARTITION PART_2011 VALUES LESS THAN (20)
    NOLOGGING ,  
  PARTITION PART_2012 VALUES LESS THAN (MAXVALUE)
    NOLOGGING
)
ENABLE ROW MOVEMENT;


ALTER TABLE TBL_ORACLE ADD CONSTRAINT SAMPLE_PK PRIMARY KEY (ID);
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
CREATE UNLOGGED TABLE TBL_ORACLE 
(
               ID NUMBER
               ,Name VARCHAR2 (100)
               ,ADDRESS VARCHAR2 (200) 
/*,CONSTRAINT SAMPLE_PK PRIMARY KEY (ID)*/
) 
 TABLESPACE space1 
          /*PCTUSED 40*/
          PCTFREE 0
          INITRANS 1
          MAXTRANS 255
PARTITION BY RANGE (ID) 
(
      PARTITION PART_2010  VALUES LESS THAN (10)
     ,PARTITION PART_2011  VALUES LESS THAN (20)
     ,PARTITION PART_2012  VALUES LESS THAN (MAXVALUE)
          ) ENABLE ROW MOVEMENT ; 

分区(一)

在非分区表中,为表“ALTER TABLE TRUNCATE PARTITION”添加注释。

Oracle语法

迁移后语法

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
CREATE TABLE product_range
(
  product_id      VARCHAR2(20),
  Product_Name    VARCHAR2(50),
  Year_Manufacture DATE
)
partition by range (Year_Manufacture)
(
  partition Year_Manufacture values less than (TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
    pctfree 10
    initrans 1
);

CREATE TABLE product_list
(
  product_id      VARCHAR2(20),
  Product_Name    VARCHAR2(50),
  Year_Manufacture vARCHAR2(10)
)
partition by list (Year_Manufacture)
(
  partition P_2020 VALUES (2020)
    pctfree 10
    initrans 1
);


CREATE OR REPLACE PROCEDURE Range_test
IS
V_ID VARCHAR2(10);
BEGIN
EXECUTE IMMEDIATE 'ALTER TABLE product TRUNCATE PARTITION PART'||V_ID;
NULL;
END;
/
CREATE OR REPLACE PROCEDURE List_test
IS
V_ID VARCHAR2(10);
BEGIN
EXECUTE IMMEDIATE 'ALTER TABLE product TRUNCATE PARTITION PART'||V_ID;
NULL;
END;
/

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
CREATE TABLE product_range
(
  product_id      VARCHAR2(20),
  Product_Name    VARCHAR2(50),
  Year_Manufacture DATE
)
partition by range (Year_Manufacture)
(
  partition Year_Manufacture values less than (TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
    pctfree 10
    initrans 1
);

CREATE TABLE product_list
(
  product_id      VARCHAR2(20),
  Product_Name    VARCHAR2(50),
  Year_Manufacture vARCHAR2(10)
)
/*partition by list (Year_Manufacture)
(
  partition P_2020 VALUES (2020)
    pctfree 10
    initrans 1
)*/;


CREATE OR REPLACE PROCEDURE Range_test
IS
V_ID VARCHAR2(10);
BEGIN
EXECUTE IMMEDIATE 'ALTER TABLE product TRUNCATE PARTITION PART'||V_ID;
NULL;
END;
/
CREATE OR REPLACE PROCEDURE List_test
IS
V_ID VARCHAR2(10);
BEGIN
/*EXECUTE IMMEDIATE 'ALTER TABLE product TRUNCATE PARTITION PART'||V_ID;*/
NULL;
END;
/

分区(二)

在非分区表中,删除表“ALTER TABLE TRUNCATE PARTITION”中的数据。

Oracle语法

迁移后语法

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
CREATE TABLE product_list
(
  product_id      VARCHAR2(20),
  Product_Name    VARCHAR2(50),
  Year_Manufacture vARCHAR2(10)
)
partition by list (Year_Manufacture)
( partition PART_2015 VALUES (2011,2012,2013,2014,2015) pctfree 10 initrans 1 ,
 partition PART_2016 VALUES (2016) pctfree 10 initrans 1 ,
 partition PART_2017 VALUES (2017) pctfree 10 initrans 1 ,
 partition PART_2018 VALUES (2018) pctfree 10 initrans 1 ,
 partition PART_2019 VALUES (2019) pctfree 10 initrans 1 ,
 partition PART_2020 VALUES (2020) pctfree 10 initrans 1 ,
 PARTITION PART_unknown VALUES (DEFAULT) );

CREATE OR REPLACE PROCEDURE List_test
IS
V_ID VARCHAR2(10);
BEGIN
 EXECUTE IMMEDIATE 'ALTER TABLE product_list TRUNCATE PARTITION PART_2020;
 NULL;
END;
/

CREATE OR REPLACE PROCEDURE List_test
IS
V_ID VARCHAR2(10);
BEGIN
 EXECUTE IMMEDIATE 'ALTER TABLE product_list TRUNCATE PARTITION PART_' || V_ID;
 NULL;
END;
/
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
CREATE TABLE product_list
(
  product_id      VARCHAR2(20),
  Product_Name    VARCHAR2(50),
  Year_Manufacture vARCHAR2(10)
)
/*partition by list (Year_Manufacture)
( partition PART_2015 VALUES (2011,2012,2013,2014,2015) pctfree 10 initrans 1 ,
 partition PART_2016 VALUES (2016) pctfree 10 initrans 1 ,
 partition PART_2017 VALUES (2017) pctfree 10 initrans 1 ,
 partition PART_2018 VALUES (2018) pctfree 10 initrans 1 ,
 partition PART_2019 VALUES (2019) pctfree 10 initrans 1 ,
 partition PART_2020 VALUES (2020) pctfree 10 initrans 1 ,
 PARTITION PART_unknown VALUES (DEFAULT) )*/;


CREATE OR REPLACE PROCEDURE List_test
IS
V_ID VARCHAR2(10);
BEGIN
 EXECUTE IMMEDIATE 'ALTER TABLE product_list TRUNCATE PARTITION PART_' || V_ID;
 NULL;
END;
/

CREATE OR REPLACE PROCEDURE List_test
IS
V_ID VARCHAR2(10);
BEGIN
 /* EXECUTE IMMEDIATE 'ALTER TABLE product_list TRUNCATE PARTITION PART_' || V_ID; */
 IF 'PART_' || V_ID = 'PART_2015' THEN
    DELETE FROM product_list WHERE Year_Manufacture IN (2011,2012,2013,2014,2015);
 ELSIF 'PART_' || V_ID = 'PART_2016' THEN
    DELETE FROM product_list WHERE Year_Manufacture IN (2016);
 ELSIF 'PART_' || V_ID = 'PART_2017' THEN
    DELETE FROM product_list WHERE Year_Manufacture IN (2017);
 ELSIF 'PART_' || V_ID = 'PART_2018' THEN
    DELETE FROM product_list WHERE Year_Manufacture IN (2018);
 ELSIF 'PART_' || V_ID = 'PART_2019' THEN
    DELETE FROM product_list WHERE Year_Manufacture IN (2019);
 ELSIF 'PART_' || V_ID = 'PART_2020' THEN
    DELETE FROM product_list WHERE Year_Manufacture IN (2020);
 ELSE
    DELETE FROM product_list WHERE Year_Manufacture NOT IN (2011,2012,2013,2014,2015,2016,2017,2018,2019,2020);
 END IF;
 NULL;
END;
/

SEGMENT CREATION

GaussDB不支持SEGMENT CREATION { IMMEDIATE | DEFERRED },因此该语句在迁移后被注释掉,需要设置commentStorageParameter=true

输入:TABLE,使用SEGMENT CREATION

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
CREATE TABLE T1 
   ( MESSAGE_CODE VARCHAR2(50), 
 MAIL_TITLE VARCHAR2(1000), 
 MAIL_BODY VARCHAR2(1000), 
 MAIL_ADDRESS VARCHAR2(1000), 
 MAIL_ADDRESS_CC VARCHAR2(1000)
   ) SEGMENT CREATION DEFERRED 
  PCTFREE 10 PCTUSED 0 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE( INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE Test ;

输出:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
CREATE TABLE T1 
   ( MESSAGE_CODE VARCHAR2(50), 
 MAIL_TITLE VARCHAR2(1000), 
 MAIL_BODY VARCHAR2(1000), 
 MAIL_ADDRESS VARCHAR2(1000), 
 MAIL_ADDRESS_CC VARCHAR2(1000)
   ) /*SEGMENT CREATION DEFERRED */
  /*PCTFREE 10*/
/* PCTUSED 0 */
/*INITRANS 1 */
/*MAXTRANS 255 */
/* NOCOMPRESS LOGGING*/
/*  STORAGE( INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)*/
/*  TABLESPACE Test */;

STORAGE

GaussDB不支持BUFFER_POOL、MAXEXTENTS等存储参数。如果commment_storage_parameter设置为true,出现在表或索引中的这些参数在迁移时会被注释掉。

输入:TABLE,使用STORAGE

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
CREATE UNIQUE INDEX PK_BASE_APPR_STEP_DEF ON BASE_APPR_STEP_DEF (FLOW_ID, NODE_ID, STEP_ID) 
   PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
   STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
   TABLESPACE SPMS_DATA ;

 CREATE TABLE UFP_MAIL 
    ( MAIL_ID NUMBER(*,0), 
  MAIL_TITLE VARCHAR2(1000), 
  MAIL_BODY VARCHAR2(4000), 
  STATUS VARCHAR2(50), 
  CREATE_TIME DATE, 
  SEND_TIME DATE, 
  MAIL_ADDRESS CLOB, 
  MAIL_CC CLOB, 
  BASE_ID VARCHAR2(20), 
  BASE_STATUS VARCHAR2(50), 
  BASE_VERIFY VARCHAR2(20), 
  BASE_LINK VARCHAR2(4000), 
  MAIL_TYPE VARCHAR2(20), 
  BLIND_COPY_TO CLOB, 
  FILE_NAME VARCHAR2(4000), 
  FULL_FILEPATH VARCHAR2(4000)
    ) SEGMENT CREATION IMMEDIATE 
   PCTFREE 10 PCTUSED 0 INITRANS 1 MAXTRANS 255 
  NOCOMPRESS LOGGING
   STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
   TABLESPACE SPMS_DATA 
  LOB (MAIL_ADDRESS) STORE AS BASICFILE (
   TABLESPACE SPMS_DATA ENABLE STORAGE IN ROW CHUNK 8192 RETENTION 
   NOCACHE LOGGING 
   STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) 
  LOB (MAIL_CC) STORE AS BASICFILE (
   TABLESPACE SPMS_DATA ENABLE STORAGE IN ROW CHUNK 8192 RETENTION 
   NOCACHE LOGGING 
   STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) 
  LOB (BLIND_COPY_TO) STORE AS BASICFILE (
   TABLESPACE SPMS_DATA ENABLE STORAGE IN ROW CHUNK 8192 RETENTION 
   NOCACHE LOGGING 
   STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) ;

输出

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
  CREATE
      UNIQUE INDEX PK_BASE_APPR_STEP_DEF
           ON BASE_APPR_STEP_DEF (
           FLOW_ID
           ,NODE_ID
           ,STEP_ID
      ) /*PCTFREE 10*/
      /*INITRANS 2*/
      /*MAXTRANS 255*/
      /*COMPUTE STATISTICS*/
      /*STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645  FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)*/
      /*TABLESPACE SPMS_DATA */
 ;

如果commment_storage_parameter设为true,存储参数会被注释掉。

STORE

Gauss不支持LOB列的STORE关键字,因此该关键字在迁移后会被注释掉。

输入:TABLE,使用STORE

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
CREATE TABLE CTP_PROC_LOG 
    ( PORC_NAME VARCHAR2(100), 
  LOG_TIME VARCHAR2(100), 
  LOG_INFO CLOB
    ) SEGMENT CREATION IMMEDIATE 
   PCTFREE 10 PCTUSED 0 INITRANS 1 MAXTRANS 255 
  NOCOMPRESS LOGGING
   STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
   TABLESPACE SPMS_DATA 
  LOB (LOG_INFO) STORE AS BASICFILE (
   TABLESPACE SPMS_DATA ENABLE STORAGE IN ROW CHUNK 8192 RETENTION 
   NOCACHE LOGGING 
   STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) ;

输出

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
 CREATE
      TABLE
           CTP_PROC_LOG (
                PORC_NAME VARCHAR2 (100)
                ,LOG_TIME VARCHAR2 (100)
                ,LOG_INFO CLOB
           ) /*SEGMENT CREATION IMMEDIATE*/
           /*PCTFREE 10*/
           /*PCTUSED 0*/
           /*INITRANS 1*/
           /*MAXTRANS 255*/
           /*NOCOMPRESS*/
           /*LOGGING*/
           /*STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645  FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)*/
           /*TABLESPACE SPMS_DATA */
           /*LOB (LOG_INFO) STORE AS BASICFILE ( TABLESPACE SPMS_DATA ENABLE STORAGE IN ROW CHUNK 8192 RETENTION NOCACHE LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645  FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))*/
 ;

PCTINCREASE

所有表均不支持存储参数PCTINCREASE。此外,分区表不支持所有存储参数(包括pctfree、minextents和 maxextents)。

输入:TABLE,使用PCTINCREASE

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
CREATE TABLE tab1 (
             col1 < datatype >
           , col2 < datatype >
           , ...
           , colN < datatype > ) 
          TABLESPACE testts 
          PCTFREE 10 INITRANS 1 MAXTRANS 
          255 
          /* STORAGE (
          INITIAL 5 M NEXT 5 M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 );*/

输出

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
CREATE TABLE tab1 (
             col1 < datatype >
           , col2 < datatype >
           , ...
           , colN < datatype >  ) 
             TABLESPACE testts 
             PCTFREE 10 INITRANS 1 MAXTRANS 255
             /* STORAGE (
             INITIAL 5 M NEXT 5 M MINEXTENTS 1 MAXEXTENTS 
             UNLIMITED );*/

外键

外键在Oracle数据库中用于强制保证引用的完整性。外键意味着一个表中的值必须同时存在另一个表中。被引用的表称为父表,而包含外键的表称为子表。子表中的外键通常会引用父表中的主键。可以在CREATE TABLE或ALTER TABLE语句中定义外键。

必须通过REFERENCE子句建立外键约束。内联约束子句是列定义子句或对象属性子句的一部分。外联约束是关系属性子句或对象属性子句中的一部分。

如果参数foreignKeyHandler设置为true(默认值),工具将这些语句迁移为注释语句。

DSC支持内联和外联外键约束,如下所示。

输入:CREATE TABLE,使用外键和内联约束

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
CREATE TABLE orders (
    order_no    INT  NOT NULL PRIMARY KEY,
    order_date   DATE  NOT NULL,
    cust_id    INT 
 [CONSTRAINT fk_orders_cust]
    REFERENCES customers(cust_id)
   [ON DELETE SET NULL]
 [INITIALLY DEFERRED]
 [ENABLE NOVALIDATE]
);

输出:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
CREATE TABLE orders (
    order_no    INT  NOT NULL PRIMARY KEY,
    order_date   DATE  NOT NULL,
    cust_id    INT 
/*
  [CONSTRAINT fk_orders_cust]
   REFERENCES customers(cust_id)
   [ON DELETE SET NULL]
   [INITIALLY DEFERRED]
   [ENABLE NOVALIDATE] */
);

输入:CREATE TABLE,使用外键和外联约束

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
CREATE TABLE customers (
        cust_id   INT   NOT NULL,
       cust_name  VARCHAR(64) NOT NULL,
       cust_addr  VARCHAR(256),
 cust_contact_no  VARCHAR(16),
    PRIMARY KEY (cust_id)
);

CREATE TABLE orders (
    order_no    INT  NOT NULL,
    order_date  DATE  NOT NULL,
    cust_id     INT  NOT NULL,
    PRIMARY KEY (order_no),
     CONSTRAINT fk_orders_cust
    FOREIGN KEY (cust_id)
     REFERENCES customers(cust_id)
      ON DELETE CASCADE
);

输出

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
CREATE TABLE customers (
        cust_id   INT   NOT NULL,
       cust_name  VARCHAR(64) NOT NULL,
       cust_addr  VARCHAR(256),
 cust_contact_no  VARCHAR(16),
    PRIMARY KEY (cust_id)
);

CREATE TABLE orders (
      order_no  INT  NOT NULL,
    order_date  DATE  NOT NULL,
       cust_id  INT  NOT NULL,
    PRIMARY KEY (order_no) /*,
     CONSTRAINT fk_orders_cust
    FOREIGN KEY (cust_id)
     REFERENCES customers(cust_id)
      ON DELETE CASCADE */
);

LONG数据类型

定义为LONG的列可存储变长字符数据,最多可包含2GB信息。MT支持表结构和PL/SQL中的LONG数据类型。

输入:在表结构中使用LONG数据类型

1
2
3
4
CREATE TABLE project ( proj_cd INT
                       , proj_name VARCHAR2(32)
                       , dept_no INT
                       , proj_det LONG );

输出:

1
2
3
4
CREATE TABLE project ( proj_cd INT
                        , proj_name VARCHAR2(32)
                        , dept_no INT
                        , proj_det TEXT );

输入:在PL/SQL中使用LONG数据类型

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
CREATE OR REPLACE FUNCTION fn_proj_det 
                          ( i_proj_cd INT )
RETURN LONG 
IS 
   v_proj_det LONG;
BEGIN 
    SELECT proj_det
      INTO v_proj_det 
      FROM project
     WHERE proj_cd = i_proj_cd; 

   RETURN v_proj_det; 
END; 
/

输出

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
CREATE OR REPLACE FUNCTION fn_proj_det 
                          ( i_proj_cd INT )
RETURN TEXT 
IS 
   v_proj_det TEXT;
BEGIN 
    SELECT proj_det
      INTO v_proj_det 
      FROM project
     WHERE proj_cd = i_proj_cd; 

   RETURN v_proj_det; 
END;
/

TYPE

将“MDSYS.MBRCOORDLIST”替换为“CLOB”。

Oracle语法

迁移后语法

1
2
3
4
5
create table product_part
(
  partid    VARCHAR2(24),
  mbrcoords MDSYS.MBRCOORDLIST
);
1
2
3
4
5
CREATE TABLE product_part
(
  partid    VARCHAR2(24),
  mbrcoords CLOB
);

将“MDSYS.SDO_GEOMETRY”替换为“CLOB”。

Oracle语法

迁移后语法

1
2
3
4
5
create table product_part
(
  partid    VARCHAR2(24),
  shape MDSYS.SDO_GEOMETRY
);
1
2
3
4
5
CREATE TABLE product_part
(
  partid    VARCHAR2(24),
  shape CLOB
);

将“MDSYS.GEOMETRY”为“CLOB”。

Oracle语法

迁移后语法

1
2
3
4
5
create table product_part
(
  partid    VARCHAR2(24),
  shape GEOMETRY
);
1
2
3
4
5
CREATE TABLE product_part
(
  partid    VARCHAR2(24),
  shape CLOB
);

xmax、xmin、left、right、maxvalue为Gauss关键字,这些关键字应全字母大写并加英文双引号("")。

Oracle语法

迁移后语法

1
2
3
4
5
6
7
8
create table product
(
  xmax     VARCHAR2(20),
  xmin     VARCHAR2(50),
  left     VARCHAR2(50),
  right    VARCHAR2(50),
  maxvalue VARCHAR2(50)
);
1
2
3
4
5
6
7
8
CREATE TABLE product1
(
  "XMAX"     VARCHAR2(20),
  "XMIN"     VARCHAR2(50),
  "LEFT"     VARCHAR2(50),
  "RIGHT"    VARCHAR2(50),
  "MAXVALUE" VARCHAR2(50)
);

间隔分区

对于间隔分区,应该注释分区。

Oracle语法

迁移后语法

1
2
3
4
5
6
7
8
9
CREATE TABLE product
(
  product_id     VARCHAR2(20),
  product_name   VARCHAR2(50),
  manufacture_month DATE
)
partition by range (manufacture_month) interval (NUMTODSINTERVAL (1, 'MONTH'))
(
  partition T_PARTITION_2018_11_LESS values less than (TO_DATE(' 2018-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')));
1
2
3
4
5
6
7
8
9
CREATE TABLE product
(
  product_id     VARCHAR2(20),
  product_name   VARCHAR2(50),
  manufacture_month DATE
)
/*partition by range (manufacture_month) interval (NUMTODSINTERVAL (1, 'MONTH'))
(
  partition T_PARTITION_2018_11_LESS values less than (TO_DATE(' 2018-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')))*/;
分享:

    相关文档

    相关产品