1. 首页
  2. IT资讯

Oracle12c分区新特性之TRUNCATEPARTITION和EXCHANGE PARTITION级联功能

TRUNCATE [SUB]PARTITION和EXCHANGE [SUB]PARTITION命令如今可以包括CASCADE子句,从而允许参照分区表向下级联这些操作。为确保该选项正常,相关外键也必须包括DELETE子句。

1.        设置

下面创建一个分区父表(T1)和一个参照的分区子表(T2)。每个分区被插入一行数据。

DROP TABLE t2 PURGE;

DROP TABLE t1 PURGE;

 

CREATE TABLE t1 (

  id           NUMBER,

  info        VARCHAR2(50),

  crt_dateDATE,

 CONSTRAINT t1_pk PRIMARY KEY (id)

)

PARTITION BY RANGE (crt_date)

(PARTITION part_2014 VALUES LESS THAN(TO_DATE(’01/01/2015′, ‘DD/MM/YYYY’)) TABLESPACE users,

 PARTITION part_2015 VALUES LESS THAN(TO_DATE(’01/01/2016′, ‘DD/MM/YYYY’)) TABLESPACE users);

 

CREATE TABLE t2 (

 id             NUMBER NOT NULL,

 t1_id          NUMBER NOT NULL,

  info           VARCHAR2(50),

  crt_date   DATE,

 CONSTRAINT t2_pk PRIMARY KEY (id),

  CONSTRAINTt2_t1_fk FOREIGN KEY (t1_id) REFERENCES t1 (id) ON DELETE CASCADE

)

PARTITION BY REFERENCE (t2_t1_fk);

 

INSERT INTO t1 VALUES (1, ‘t1 data’,TO_DATE(’05/05/2014′, ‘DD/MM/YYYY’));

INSERT INTO t1 VALUES (2, ‘t1 data’,TO_DATE(’05/05/2015′, ‘DD/MM/YYYY’));

 

INSERT INTO t2 VALUES (1, 1, ‘t2 data’,TO_DATE(’05/05/2014′, ‘DD/MM/YYYY’));

INSERT INTO t2 VALUES (2, 2, ‘t2 data’,TO_DATE(’05/05/2015′, ‘DD/MM/YYYY’));

COMMIT;

 

EXEC DBMS_STATS.gather_table_stats(USER,’t1′);

EXEC DBMS_STATS.gather_table_stats(USER,’t2′);

 

SELECT table_name,

      partition_name,

      num_rows

FROM  user_tab_partitions

ORDER BY 1,2;

 

TABLE_NAME           PARTITION_NAME         NUM_ROWS

——————– ——————————

T1                   PART_2014                     1

T1                   PART_2015                     1

T2                   PART_2014                     1

T2                   PART_2015                     1

 

SQL>

2.        TRUNCATE PARTITION … CASCADE

通过TRUNCATE PARTITION … CASCADE命令,我们可以清空父表分区和子表分区。

ALTER TABLE t1 TRUNCATE PARTITION part_2014CASCADE UPDATE INDEXES;

 

EXEC DBMS_STATS.gather_table_stats(USER,’t1′);

EXEC DBMS_STATS.gather_table_stats(USER, ‘t2’);

 

SELECT table_name,

      partition_name,

      num_rows

FROM  user_tab_partitions

ORDER BY 1,2;

 

TABLE_NAME           PARTITION_NAME         NUM_ROWS

——————– ——————————

T1                   PART_2014                     0

T1                   PART_2015                     1

T2                   PART_2014                     0

T2                   PART_2015                     1

 

SQL>

3.        EXCHANGE PARTITION … CASCADE

为了测试EXCHANGE PARTITION …CASCADE命令,创建如下非分区表。为确保层级功能正常,从交换级别开始往下的所有表必须存在。

DROP TABLE t2_temp;

DROP TABLE t1_temp;

 

CREATE TABLE t1_temp (

 id           NUMBER,

  info  VARCHAR2(50),

  crt_dateDATE,

 CONSTRAINT t1_temp_pk PRIMARY KEY (id)

);

 

CREATE TABLE t2_temp (

 id             NUMBER NOT NULL,

 t1_id          NUMBER NOT NULL,

  info    VARCHAR2(50),

  crt_date   DATE,

 CONSTRAINT t2_temp_pk PRIMARY KEY (id),

 CONSTRAINT t2_temp_t1_temp_fk FOREIGN KEY (t1_id) REFERENCES t1_temp(id) ON DELETE CASCADE

);

 

INSERT INTO t1_temp VALUES (2, ‘t1_temp data’,TO_DATE(’05/05/2015′, ‘DD/MM/YYYY’));

 

INSERT INTO t2_temp VALUES (2, 2, ‘t2_tempdata’, TO_DATE(’05/05/2015′, ‘DD/MM/YYYY’));

COMMIT;

如下交换父表和子表分区后,检查表中数据。

— 交换分区

ALTER TABLE t1

 EXCHANGE PARTITION part_2015

  WITHTABLE t1_temp

 CASCADE

  UPDATEINDEXES;

 

— 检查分区中的数据

COLUMN t1_info FORMAT A20

COLUMN t2_info FORMAT A20

 

SELECT t1.info AS t1_info,

      t2.info AS t2_info

FROM  t1

      JOIN t2 ON t2.t1_id = t1.id;

 

T1_info              T2_info

——————– ——————–

t1_temp data          t2_temp data

 

SQL>

 

— 检查临时表中的数据。

COLUMN t1_temp_info FORMAT A20

COLUMN t2_temp_info FORMAT A20

 

SELECT t1_temp.info AS t1_temp_info,

      t2_temp.info AS t2_temp_info

FROM  t1_temp

      JOIN t2_temp ON t2_temp.t1_id = t1_temp.id;

 

T1_TEMP_info         T2_TEMP_info

——————– ——————–

t1 data               t2 data

 

SQL>

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/8484829/viewspace-2120686/,如需转载,请注明出处,否则将追究法律责任。

主题测试文章,只做测试使用。发布者:深沉的少年,转转请注明出处:http://www.cxybcw.com/184830.html

联系我们

13687733322

在线咨询:点击这里给我发消息

邮件:1877088071@qq.com

工作时间:周一至周五,9:30-18:30,节假日休息

QR code