12c新特性之Truncating a Partition with the Cascade Option

原创黄宸宁 2015-05-05

542

truncate操作分区表分区时，可以通过cascade选项来进行级联操作，实验步骤如下:
1. 创建测试表

SQL> 
SQL> create table master_ref_tbl
  2  (
  3    id1 number,
  4    id2 number,
  5    constraint master_ref_tbl_pk primary key(id1)
  6  )
  7  partition by range(id1)
  8  subpartition by range(id2)
  9  (
 10    partition p1 values less than(10)
 11    (
 12      subpartition s1 values less than(5),
 13      subpartition s2 values less than(10)
 14    ),
 15    partition p2 values less than(20)
 16    (
 17      subpartition s3 values less than(15),
 18      subpartition s4 values less than(20)
 19    )
 20  );
Table created.
SQL> 
SQL> create table child_ref_tbl1
  2  (
  3    id1 number not null,
  4    id2 number not null,
  5    constraint child_ref_tbl1_pk primary key(id1),
  6    constraint child_ref_tbl1_fk foreign key (id2) 
  7    references master_ref_tbl(id1) ON DELETE CASCADE
  8  )
  9  partition by REFERENCE(child_ref_tbl1_fk);
Table created.
SQL> 
SQL> 
SQL> 
SQL> create table child_ref_tbl2
  2  (
  3    id1 number not null,
  4    id2 number not null,
  5    constraint child_ref_tbl2_pk primary key(id1),
  6    constraint child_ref_tbl2_fk foreign key (id2) 
  7    references master_ref_tbl(id1) ON DELETE CASCADE
  8  )
  9  partition by REFERENCE(child_ref_tbl2_fk);
Table created.
SQL> 
SQL> 
SQL> 
SQL> 
SQL> begin
 2 for i in 0..19
 3 loop
 4 insert into master_ref_tbl values(i, i);
 5 insert into child_ref_tbl1 values(i, i);
 6 insert into child_ref_tbl2 values(i, i);
 7 end loop;
 8 
 9 commit;
 10 end;
 11 /
PL/SQL procedure successfully completed.
SQL> 
SQL> exec dbms_stats.gather_table_stats(user, 'MASTER_REF_TBL');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats(user, 'CHILD_REF_TBL1');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats(user, 'CHILD_REF_TBL2');
PL/SQL procedure successfully completed.
SQL> 
SQL> 
SQL> 

SQL> 
SQL> set lines 200
SQL> col table_name for a30
SQL> 
SQL> col partition_name for a30
SQL> select table_name, partition_name, num_rows 
 2 from dba_tab_partitions
 3 where table_name='MASTER_REF_TBL'
 4 order by partition_position;
TABLE_NAME PARTITION_NAME NUM_ROWS
------------------------------ ------------------------------ ----------
MASTER_REF_TBL P1 10
MASTER_REF_TBL P2 10
SQL> 
SQL> 
SQL> 
SQL> select table_name, partition_name, subpartition_name, num_rows 
 2 from dba_tab_subpartitions
 3 where table_name='MASTER_REF_TBL'
 4 order by partition_name, subpartition_position;
TABLE_NAME PARTITION_NAME SUBPARTITION_NAME NUM_ROWS
------------------------------ ------------------------------ -------------------- ----------
MASTER_REF_TBL P1 S1 5
MASTER_REF_TBL P1 S2 5
MASTER_REF_TBL P2 S3 5
MASTER_REF_TBL P2 S4 5
SQL> 
SQL> 
SQL> 
SQL> select table_name, partition_name, num_rows 
 2 from dba_tab_partitions
 3 where table_name in ('CHILD_REF_TBL1', 'CHILD_REF_TBL2')
 4 order by table_name, partition_position;
TABLE_NAME PARTITION_NAME NUM_ROWS
------------------------------ ------------------------------ ----------
CHILD_REF_TBL1 S1 5
CHILD_REF_TBL1 S2 5
CHILD_REF_TBL1 S3 5
CHILD_REF_TBL1 S4 5
CHILD_REF_TBL2 S1 5
CHILD_REF_TBL2 S2 5
CHILD_REF_TBL2 S3 5
CHILD_REF_TBL2 S4 5
8 rows selected.
SQL> 
SQL>

2. 测试分区级别的cascade

SQL> 
SQL> alter table master_ref_tbl TRUNCATE PARTITION p1 CASCADE;
Table truncated.
SQL> 
SQL> 
SQL> 
SQL> exec dbms_stats.gather_table_stats(user, 'MASTER_REF_TBL');
PL/SQL procedure successfully completed.
SQL> 
SQL> 
SQL> exec dbms_stats.gather_table_stats(user, 'CHILD_REF_TBL1');
PL/SQL procedure successfully completed.
SQL> 
SQL> 
SQL> exec dbms_stats.gather_table_stats(user, 'CHILD_REF_TBL2');
PL/SQL procedure successfully completed.
SQL> 
SQL> 

SQL> 
SQL> 
SQL> select table_name, partition_name, num_rows 
  2  from dba_tab_partitions
  3  where table_name='MASTER_REF_TBL'
  4  order by partition_position;
TABLE_NAME                     PARTITION_NAME                   NUM_ROWS
------------------------------ ------------------------------ ----------
MASTER_REF_TBL                 P1                                      0
MASTER_REF_TBL                 P2                                     10
SQL> 
SQL> 
SQL> select table_name, partition_name, subpartition_name, num_rows 
  2  from dba_tab_subpartitions
  3  where table_name='MASTER_REF_TBL'
  4  order by partition_name, subpartition_position;
TABLE_NAME                     PARTITION_NAME                 SUBPARTITION_NAME      NUM_ROWS
------------------------------ ------------------------------ -------------------- ----------
MASTER_REF_TBL                 P1                             S1                            0
MASTER_REF_TBL                 P1                             S2                            0
MASTER_REF_TBL                 P2                             S3                            5
MASTER_REF_TBL                 P2                             S4                            5
SQL> 
SQL> select table_name, partition_name, num_rows 
  2  from dba_tab_partitions
  3  where table_name in ('CHILD_REF_TBL1', 'CHILD_REF_TBL2')
  4  order by table_name, partition_position;
TABLE_NAME                     PARTITION_NAME                   NUM_ROWS
------------------------------ ------------------------------ ----------
CHILD_REF_TBL1                 S1                                      0
CHILD_REF_TBL1                 S2                                      0
CHILD_REF_TBL1                 S3                                      5
CHILD_REF_TBL1                 S4                                      5
CHILD_REF_TBL2                 S1                                      0
CHILD_REF_TBL2                 S2                                      0
CHILD_REF_TBL2                 S3                                      5
CHILD_REF_TBL2                 S4                                      5
8 rows selected.
SQL> 
SQL>

3. 测试子分区级别的cascade

SQL> 
SQL> alter table master_ref_tbl TRUNCATE SUBPARTITION s4 CASCADE;
Table truncated.
SQL> 
SQL> exec dbms_stats.gather_table_stats(user, 'MASTER_REF_TBL');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats(user, 'CHILD_REF_TBL1');
PL/SQL procedure successfully completed.
SQL>  exec dbms_stats.gather_table_stats(user, 'CHILD_REF_TBL2');
PL/SQL procedure successfully completed.
SQL> 

SQL> 
SQL> select table_name, partition_name, num_rows 
  2  from dba_tab_partitions
  3  where table_name='MASTER_REF_TBL'
  4  order by partition_position;
TABLE_NAME                     PARTITION_NAME                   NUM_ROWS
------------------------------ ------------------------------ ----------
MASTER_REF_TBL                 P1                                      0
MASTER_REF_TBL                 P2                                      5
SQL> 
SQL> select table_name, partition_name, subpartition_name, num_rows 
  2  from dba_tab_subpartitions
  3  where table_name='MASTER_REF_TBL'
  4  order by partition_name, subpartition_position;
TABLE_NAME                     PARTITION_NAME                 SUBPARTITION_NAME      NUM_ROWS
------------------------------ ------------------------------ -------------------- ----------
MASTER_REF_TBL                 P1                             S1                            0
MASTER_REF_TBL                 P1                             S2                            0
MASTER_REF_TBL                 P2                             S3                            5
MASTER_REF_TBL                 P2                             S4                            0
SQL> 
SQL> 
SQL> select table_name, partition_name, num_rows 
  2  from dba_tab_partitions
  3  where table_name in ('CHILD_REF_TBL1', 'CHILD_REF_TBL2')
  4  order by table_name, partition_position;
TABLE_NAME                     PARTITION_NAME                   NUM_ROWS
------------------------------ ------------------------------ ----------
CHILD_REF_TBL1                 S1                                      0
CHILD_REF_TBL1                 S2                                      0
CHILD_REF_TBL1                 S3                                      5
CHILD_REF_TBL1                 S4                                      0
CHILD_REF_TBL2                 S1                                      0
CHILD_REF_TBL2                 S2                                      0
CHILD_REF_TBL2                 S3                                      5
CHILD_REF_TBL2                 S4                                      0
8 rows selected.
SQL> 
SQL>

4. 非分区表的子表的cascade

SQL> 
SQL> create table child_ref_tbl3
  2  (
  3    id1 number not null,
  4    id2 number not null,
  5    constraint child_ref_tbl3_pk primary key(id1),
  6    constraint child_ref_tbl3_fk foreign key (id2) 
  7    references master_ref_tbl(id1) ON DELETE CASCADE
  8  );
Table created.
SQL> 
SQL> alter index master_ref_tbl_pk rebuild;
Index altered.
SQL> 
SQL> insert into child_ref_tbl3
  2  values(10,10);
1 row created.
SQL> 
SQL> commit;
Commit complete.
SQL> 
SQL> 

--partition p1 is empty so nothing happens
SQL> 
SQL> 
SQL> alter table master_ref_tbl TRUNCATE PARTITION p1 CASCADE;
Table truncated.
SQL> 

--subpartition s4 is empty so nothing happens
SQL> alter table master_ref_tbl TRUNCATE SUBPARTITION s4 CASCADE;
Table truncated.
SQL> alter table master_ref_tbl TRUNCATE PARTITION p2 CASCADE;
alter table master_ref_tbl TRUNCATE PARTITION p2 CASCADE
            *
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys

SQL> 
SQL> alter table master_ref_tbl TRUNCATE SUBPARTITION s3 CASCADE;
alter table master_ref_tbl TRUNCATE SUBPARTITION s3 CASCADE
            *
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys

SQL> 
/*
当对分区或则子分区做truncate cascade的操作时，如果有子表是非分区表，
当要操作的分区或则子分区中包含了非分区表中的数据时，会报ORA-02266错误

当对分区或则子分区做truncate cascade的操作时，如果有子表是非分区表，
且truncate cascade操作的分区或则子分区中和非分区表中没有数据时，不会报错。
*/

SQL> 
SQL> TRUNCATE TABLE master_ref_tbl CASCADE;
Table truncated.
SQL> 
SQL> 
SQL> select count(*) from master_ref_tbl
  2  union all
  3  select count(*) from child_ref_tbl1
  4  union all
  5  select count(*) from child_ref_tbl2
  6  union all
  7  select count(*) from child_ref_tbl3;
  COUNT(*)
----------
         0
         0
         0
         0
SQL> 
/*
分区表级别的truncate cascade的操作，无论子表是分区表还是非分区表，
都会关联执行truncate cascade
对于exchange操作依然实用
*/

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

12c新特性之Truncating a Partition with the Cascade Option

评论