Oracle表分区合并

来源：互联网发布：域名污染编辑：程序博客网时间：2024/05/21 02:49

在看一篇介绍表分区的文章时，产生了一个疑问，如何表的分区处于不同表空间，那么合并后的分区是属于哪个表空间的？

下面进行实验：

实验1：

创建不同表空间的hash分区

SQL> CREATE TABLE HASH_TABLE

2 (
3 COL NUMBER(8),
4 INF VARCHAR2(100)
5 )
6 PARTITION BY HASH (COL)
7 ( PARTITION PART01 TABLESPACE users,
8 PARTITION PART02 TABLESPACE test)
9 ;

SQL> select PARTITION_NAME,PARTITION_POSITION, TABLESPACE_NAME from USER_TAB_PARTITIONS where table_name='HASH_TABLE';

PARTITION_NAME PARTITION_POSITION TABLESPACE_NAME
------------------------------ ------------------ ------------------------------
PART01 1 USERS
PART02 2 TEST

SQL>ALTER TABLE HASH_TABLE MERGE PARTITIONS PART01,PART02 INTO PARTITION PART02;

SQL> ALTER TABLE HASH_TABLE MERGE PARTITIONS PART01,PART02 INTO PARTITION PART02;
ALTER TABLE HASH_TABLE MERGE PARTITIONS PART01,PART02 INTO PARTITION PART02
*
ERROR at line 1:
ORA-14255: table is not partitioned by Range, List, Composite Range or
Composite List method

结论：hash分区表不能进行合并。

实验2：

创建不同表空间的范围分区

CREATE TABLE CUSTOMER
(
CUSTOMER_ID NUMBER NOT NULL PRIMARY KEY
)
PARTITION BY RANGE (CUSTOMER_ID)
(
PARTITION CUS_PART1 VALUES LESS THAN (10) TABLESPACE users,
PARTITION CUS_PART2 VALUES LESS THAN (20) TABLESPACE test
);

SQL> select PARTITION_NAME,PARTITION_POSITION, TABLESPACE_NAME from USER_TAB_PARTITIONS where table_name='CUSTOMER';

PARTITION_NAME PARTITION_POSITION TABLESPACE_NAME
------------------------------ ------------------ ------------------------------
CUS_PART1 1 USERS
CUS_PART2 2 TEST

SQL> ALTER TABLE CUSTOMER MERGE PARTITIONS CUS_PART1,CUS_PART2 INTO PARTITION CUS_PART1;
ALTER TABLE CUSTOMER MERGE PARTITIONS CUS_PART1,CUS_PART2 INTO PARTITION CUS_PART1
*
ERROR at line 1:
ORA-14275: cannot reuse lower-bound partition as resulting partition

结论1：分区不能向小范围的分区合并，只能向大范围合并

SQL>ALTER TABLE CUSTOMER MERGE PARTITIONS CUS_PART1,CUS_PART2 INTO PARTITION CUS_PART2;

SQL> select PARTITION_NAME,PARTITION_POSITION, TABLESPACE_NAME from USER_TAB_PARTITIONS where table_name='CUSTOMER';

PARTITION_NAME PARTITION_POSITION TABLESPACE_NAME
------------------------------ ------------------ ------------------------------
CUS_PART2 1 SYSTEM

结论2：未指定表空间分区合并后，生成的分区表空间为用户默认表空间

SQL> ALTER TABLE CUSTOMER MERGE PARTITIONS CUS_PART1,CUS_PART2 INTO PARTITION CUS_PART2 tablespace users;

SQL> select PARTITION_NAME,PARTITION_POSITION, TABLESPACE_NAME from USER_TAB_PARTITIONS where table_name='CUSTOMER';

PARTITION_NAME PARTITION_POSITION TABLESPACE_NAME
------------------------------ ------------------ ------------------------------

CUS_PART2 1 USERS

结论3：通过语句修改为合并后的分区指定表空间

实验3：

测试非相邻分区是否可以合并

SQL> CREATE TABLE CUSTOMER
2 (
3 CUSTOMER_ID NUMBER NOT NULL PRIMARY KEY
4 )
5 PARTITION BY RANGE (CUSTOMER_ID)
6 (
7 PARTITION CUS_PART1 VALUES LESS THAN (10) TABLESPACE users,
8 PARTITION CUS_PART2 VALUES LESS THAN (20) TABLESPACE test ，
9 PARTITION CUS_PART3 VALUES LESS THAN (30) TABLESPACE test
10 );

Table created.

SQL> select PARTITION_NAME,PARTITION_POSITION, TABLESPACE_NAME from USER_TAB_PARTITIONS where table_name='CUSTOMER';

PARTITION_NAME PARTITION_POSITION TABLESPACE_NAME
------------------------------ ------------------ ------------------------------
CUS_PART1 1 USERS
CUS_PART2 2 TEST
CUS_PART3 3 TEST

SQL> ALTER TABLE CUSTOMER MERGE PARTITIONS CUS_PART1,CUS_PART3 INTO PARTITION CUS_PART3 tablespace users;
ALTER TABLE CUSTOMER MERGE PARTITIONS CUS_PART1,CUS_PART3 INTO PARTITION CUS_PART3 tablespace users
*
ERROR at line 1:
ORA-14274: partitions being merged are not adjacent

结论：非相邻表分区不能进行合并

0 0