文档首页 > > 工具指南> Migration Tool SQL语法迁移工具> Oracle(Beta)语法迁移> 模式对象>

分享
更新时间: 2019/08/09 GMT+08:00

分区

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

图1 表的分区和子分区

Migration Tool支持范围分区迁移。

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

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

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

  • PARTITION by HASH

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

    输入:HASH PARTITION

    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')) storage (initial 10T)
     , 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')) pctused 10
     );

    输出

    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

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

    输入:LIST PARTITION

    CREATE TABLE q1_sales_by_region 
             ( deptno NUMBER
             , deptname varchar2 (20)
             , quarterly_sales NUMBER           
               (10,2)
             , state varchar2 (2)
             ) 
       PARTITION BY LIST (state) 
               ( 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' )
               );

    输出

    CREATE TABLE q1_sales_by_region 
             ( deptno NUMBER
             , deptname varchar2 (20)
             , quarterly_sales NUMBER           
               (10,2)
             , state varchar2 (2)
             ) 
       /*PARTITION BY LIST (state) 
               ( 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' )
               )*/;

    输入:LIST PARTITION和SUBPARTITIONS

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

    输出

    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' )
       )*/;
  • PARTITION by RANGE

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

    输入:RANGE PARTITION(指定存储参数)

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

    输出

    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

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

    输出

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

    输出

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

PCTINCREASE

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

输入:TABLE和PCTINCREASE

CREATE TABLE divisions 
    (div_no     NUMBER(2), 
     div_name   VARCHAR2(14), 
     location   VARCHAR2(13) ) 
     STORAGE  ( INITIAL 100K  NEXT     50K 
                MINEXTENTS 1  MAXEXTENTS 50  PCTINCREASE 5);

输出

CREATE TABLE divisions 
    (div_no     NUMBER(2), 
     div_name   VARCHAR2(14), 
     location   VARCHAR2(13) ) 
     STORAGE  ( INITIAL 100K  NEXT     50K 
                MINEXTENTS 1  MAXEXTENTS 50  );

外键

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

必须使用“reference-clause(REFERENCE-子句)”建立外键约束。内联约束子句是列定义子句或对象属性子句的一部分。外部约束是关系属性子句或对象属性子句中的一部分。

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

Migration tool支持内联和外联外键约束,不包括REF约束,如下所示。

说明:

迁移工具不支持对没有指定数据类型的列进行内联和外联REF约束。不支持的语句将按原样复制。

  • 示例:外键和内联REF约束
    CREATE TABLE dept_20 (
        employee_id    NUMBER(4), 
        salary         NUMBER(7,2), 
        department_id  CONSTRAINT fk_deptno /* inline ref */
        REFERENCES departments(department_id)
     ); 
  • 示例:外键和外联REF约束
    CREATE TABLE dept_20 (
        employee_id    NUMBER(4), 
        salary         NUMBER(7,2), 
        department_id, 
        CONSTRAINT     fk_deptno /* out of line ref */
        FOREIGN KEY    (department_id) 
        REFERENCES departments(department_id) 
     ); 

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

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 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中使用外键和外联约束

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

输出

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

输入:在ALTER TABLE使用外键和内联约束

ALTER TABLE orders ADD ( cust_id INT
  CONSTRAINT fk_orders_cust
  REFERENCES customers(cust_id)
   ON DELETE SET NULL
   INITIALLY DEFERRED
  ENABLE NOVALIDATE );

输出

ALTER TABLE orders ADD ( cust_id INT
  /*CONSTRAINT fk_orders_cust
  REFERENCES customers(cust_id)
   ON DELETE SET NULL
   INITIALLY DEFERRED
  ENABLE NOVALIDATE*/ );

输入:在ALTER TABLE使用外键和添加约束

ALTER TABLE porders
ADD CONSTRAINT fk_orders_cust
  FOREIGN KEY (cust_id)
  REFERENCES customers(cust_id);

输出

/*ALTER TABLE porders
/*ADD /*CONSTRAINT fk_orders_cust
  FOREIGN KEY (cust_id)
  REFERENCES customers(cust_id)*/*/;*/

LONG数据类型

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

详情请参见PL/SQL中的LONG数据类型

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

CREATE TABLE project ( proj_cd INT
                       , proj_name VARCHAR2(32)
                       , dept_no INT
                       , proj_det LONG );

输出

CREATE TABLE project ( proj_cd INT
                       , proj_name VARCHAR2(32)
                       , dept_no INT
                       , proj_det TEXT );

创建与约束同名的表

说明:

如果同义词名称和主键约束名称不区分大小写,将以小写形式呈现。

输入

CREATE TABLE public.rp_t_transactiondetail
(
  policyno           VARCHAR2(22) NOT NULL,
  proposalno         VARCHAR2(22),
  businessnature     VARCHAR2(9),
  businessnaturename VARCHAR2(360),
  clausetype         VARCHAR2(9),
  carchecker         VARCHAR2(60),
  carkindcode        VARCHAR2(9),
  usenaturecode      VARCHAR2(9)
);

ALTER TABLE public.rp_t_transactiondetail ADD  CONSTRAINT rp_t_transactiondetail PRIMARY KEY (policyno);

输出

CREATE TABLE public.rp_t_transactiondetail
(
  policyno           VARCHAR2(22) NOT NULL,
  proposalno         VARCHAR2(22),
  businessnature     VARCHAR2(9),
  businessnaturename VARCHAR2(360),
  clausetype         VARCHAR2(9),
  carchecker         VARCHAR2(60),
  carkindcode        VARCHAR2(9),
  usenaturecode      VARCHAR2(9), CONSTRAINT mig_rp_t_transactiondetail PRIMARY KEY (policyno)
);

/*ALTER TABLE public.rp_t_transactiondetail /*ADD  CONSTRAINT rp_t_transactiondetail PRIMARY KEY (policyno)*/;*/
分享:

    相关文档

    相关产品

文档是否有解决您的问题?

提交成功!

非常感谢您的反馈,我们会继续努力做到更好!

反馈提交失败,请稍后再试!

*必选

请至少选择或填写一项反馈信息

字符长度不能超过200

提交反馈 取消

如您有其它疑问,您也可以通过华为云社区问答频道来与我们联系探讨

跳转到云社区