oracle partition 分区表
分区表概述:
分区表就是通过使用分区技术,将一张大表,拆分成多个表分区(独立的segment),从而提升数据访问的性能,以及日常的可维护性。
分区表中,每个分区的逻辑结构必须相同。如:列名、数据类型、
分区表中,每个分区的物理存储参数可以不同。如:各个分区所在的表空间。
对于应用而言完全透明,分区前后没有变化,不需要进行修改。
需注意:
1.虽然各个分区可以存放在不同的表空间中,但这些表空间所使用的块大小(block_size)必须一致。
2.除了包含LONG以及LONG RAW字段的表无法使用分区表外,其他表均可以使用分区,包括含有LOB字段的表。
分区表的优势:
(1)改善查询性能:对分区对象的查询可以仅搜索自己关心的分区,提高检索速度;
(2)方便数据管理:因为分区表的数据存储在多个部分中,所以按分区加载和删除数据比在大表中加载和删除数据更容易。
(3)方便备份恢复:因为分区表比原表要小,所以针对分区的备份和恢复方法要比备份和恢复整个表的方法多。
一、Oracle数据库提供对表或者分区的方法有几种:
(1)范围分区(range分区)
(2)散列分区(HASH分区)
(3)列表分区(list分区)
(4)复合分区(子分区)
1.范围分区(range分区)
行映射到基于列值范围的分区
range分区又称为范围分区,基于分区键值的范围将数据映射到所建立的分区上。这就是最通用的分区类型。
特点:
范围分区只要依据分区键定义时给出的键值范围,根据实际的取值,进行分区的选择,进而在相应分区中存储数据。
范围分区比较合适存在以数字为导向,方便进行数字范围划分的数据列。
范围分区的数据分布可能不均匀。
创建范围分区时,必须指定以下内容:
分区方法:range
分区列:标识分区边界的描述
使用range分区的时候,要记住几条规则:
1)每个分区都包含values less than字名,定义了分区的上层边界,任何等于和大于分区键值的二进制都被添加到下一个高层分区中。
2)所有的分区,除了第一个,如果低于values less than所定义的下层边界,都放在前面的分区中。
MAXVALUE可以用来定义最高层的分区。MAXVALUE表示了虚拟的无限值。
SQL> select count(*) from emp;
COUNT(*)
----------
14
SQL> create table emp1 as select * from emp;
Table created
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980/12/17 800.00 20
7499 ALLEN SALESMAN 7698 1981/2/20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981/2/22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981/4/2 2975.00 20
7654 MARTIN SALESMAN 7698 1981/9/28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30
7782 CLARK MANAGER 7839 1981/6/9 2450.00 10
7788 SCOTT ANALYST 7566 1987/4/19 3000.00 20
7839 KING PRESIDENT 1981/11/17 5000.00 10
7844 TURNER SALESMAN 7698 1981/9/8 1500.00 0.00 30
7876 ADAMS CLERK 7788 1987/5/23 1100.00 20
7900 JAMES CLERK 7698 1981/12/3 950.00 30
7902 FORD ANALYST 7566 1981/12/3 3000.00 20
7934 MILLER CLERK 7782 1982/1/23 1300.00 10
14 rows selected
SQL>CREATE TABLE SCOTT.EMP_RANGE
(
EMPNO NUMBER,
ENAME VARCHAR2(10 BYTE),
JOB VARCHAR2(9 BYTE),
MGR NUMBER,
HIRDATE DATE,
SAL NUMBER,
COMM NUMBER,
DEPTNO NUMBER
)
PARTITION BY RANGE (HIRDATE)
(
PARTITION EMP_RANGE_01 VALUES LESS THAN (TO_DATE(' 1981-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION EMP_RANGE_02 VALUES LESS THAN (TO_DATE(' 1982-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION EMP_RANGE_03 VALUES LESS THAN (MAXVALUE)
);
SQL> insert into emp_range select * from emp;
14 rows inserted
SQL> commit;
Commit complete
SQL> select table_name,partitioning_type,partition_count,status from dba_part_tables where table_name='EMP_RANGE';
TABLE_NAME PARTITIONING_TYPE PARTITION_COUNT STATUS
------------------------------ ----------------- --------------- --------
EMP_RANGE RANGE 3 VALID
SQL> select partition_name,num_rows,tablespace_name,segment_created from dba_tab_partitions where table_name='EMP_RANGE';
PARTITION_NAME NUM_ROWS TABLESPACE_NAME SEGMENT_CREATED
------------------------------ ---------- ------------------------------ ---------------
EMP_RANGE_01 USERS YES
EMP_RANGE_02 USERS YES
EMP_RANGE_03 USERS YES
SQL> select * from emp_range partition(emp_range_01);
EMPNO ENAME JOB MGR HIRDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ----------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980/12/17 800 20
2.HASH分区(hash partition)
HASH分区特点:
hash分区主要是通过hash算法确定相应数据行应该被存放到哪个分区中。
hash分区比较合列差异值很多的数据列。
HASH分区的注意事项:
对于HASH分区,无法控制一条数据在分区之间的具体分布。具体分布由hash算法决定。
对于hash分区,如果更改分区的数量,将导致所有数据在分区间的重新分布。
HASH分区定义规则:
在定义HASH分区时,其分区数量应为2的N次方,比如:2,4,8,16等。
Hash分区能够很容易对数据进行分区,因为语法很简单,很容易实现。在下面这种情况下,使用hash分区比range分区更好:
(1)事先不知道需要将多少数据映射到给定范围的时候。
(2)分区的范围大小很难确实,或者很难平衡的时候。
(3)Range分区使诗句得到不希望的聚集时。
(4)性能特性,如并行DML、分区剪枝和分区连接很重要的时候
创建散列分区时,必须指定以下信息:
分区方法:hash
分区列:
分区数量或单独的分区描述
分裂、删除和合并分区不能应用于hash分区,但是,hash分区能够合并和添加。
创建hash分区有两种方法:一种方法是指定分区数量,另一种方法是指定分区的名字,但两者不能同时指定。
方法一:指定分区数量:
CREATE TABLE SCOTT.EMP_hash_1
(
EMPNO NUMBER,
ENAME VARCHAR2(10 BYTE),
JOB VARCHAR2(9 BYTE),
MGR NUMBER,
HIRDATE DATE,
SAL NUMBER,
COMM NUMBER,
DEPTNO NUMBER
)
PARTITION BY hash (ename) partitions 2;
方法二:指定分区的名字:
CREATE TABLE SCOTT.EMP_hash_2
(
EMPNO NUMBER,
ENAME VARCHAR2(10 BYTE),
JOB VARCHAR2(9 BYTE),
MGR NUMBER,
HIRDATE DATE,
SAL NUMBER,
COMM NUMBER,
DEPTNO NUMBER
)
PARTITION BY hash (ename)
(partition hash_01,
partition hash_02);
示例:
SQL> CREATE TABLE SCOTT.EMP_hash_2
2 (
3 EMPNO NUMBER,
4 ENAME VARCHAR2(10 BYTE),
5 JOB VARCHAR2(9 BYTE),
6 MGR NUMBER,
7 HIRDATE DATE,
8 SAL NUMBER,
9 COMM NUMBER,
10 DEPTNO NUMBER
11 )
12 PARTITION BY hash (ename)
13 (partition hash_01,
14 partition hash_02);
Table created
SQL> insert into emp_hash_2 select * from emp;
14 rows inserted
SQL> select * from emp_hash_2
2 ;
EMPNO ENAME JOB MGR HIRDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ----------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 1981/2/20 1600 300 30
7521 WARD SALESMAN 7698 1981/2/22 1250 500 30
7654 MARTIN SALESMAN 7698 1981/9/28 1250 1400 30
7698 BLAKE MANAGER 7839 1981/5/1 2850 30
7844 TURNER SALESMAN 7698 1981/9/8 1500 0 30
7876 ADAMS CLERK 7788 1987/5/23 1100 20
7900 JAMES CLERK 7698 1981/12/3 950 30
7934 MILLER CLERK 7782 1982/1/23 1300 10
7369 SMITH CLERK 7902 1980/12/17 800 20
7566 JONES MANAGER 7839 1981/4/2 2975 20
7782 CLARK MANAGER 7839 1981/6/9 2450 10
7788 SCOTT ANALYST 7566 1987/4/19 3000 20
7839 KING PRESIDENT 1981/11/17 5000 10
7902 FORD ANALYST 7566 1981/12/3 3000 20
14 rows selected
SQL> select * from emp_hash_2 partition(HASH_01);
EMPNO ENAME JOB MGR HIRDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ----------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 1981/2/20 1600 300 30
7521 WARD SALESMAN 7698 1981/2/22 1250 500 30
7654 MARTIN SALESMAN 7698 1981/9/28 1250 1400 30
7698 BLAKE MANAGER 7839 1981/5/1 2850 30
7844 TURNER SALESMAN 7698 1981/9/8 1500 0 30
7876 ADAMS CLERK 7788 1987/5/23 1100 20
7900 JAMES CLERK 7698 1981/12/3 950 30
7934 MILLER CLERK 7782 1982/1/23 1300 10
8 rows selected
SQL> analyze table emp_hash_2 compute statistics;
Table analyzed
SQL> select partition_name,num_rows,tablespace_name,segment_created from dba_tab_partitions where table_name='EMP_HASH_2';
PARTITION_NAME NUM_ROWS TABLESPACE_NAME SEGMENT_CREATED
------------------------------ ---------- ------------------------------ ---------------
HASH_01 8 USERS YES
HASH_02 6 USERS YES
3.列表分区(list分区)
列表分区特点:
列表分区主要依据分区键定义时给出的取值列表,根据实际的取值,进行分区的选择,进而在相应分区中存储数据。
列表分区比较适合列唯一取值有限,且较为固定的数据列。如:员工表的部门列。
列表分区的数据分布可能不均匀。
列表分区定义规则:
1.在定义范围分区时,每个分区必须使用values('value01','value02')子句。表示该分区存储包含相关value值的数据行。
2.在定义范围分区时,最后一个分区可以是values(default)。表示该分区存储未在其他分区定义的数据行。
list分区可以控制将行映射到分区中去。可以在每个分区的键撒花姑娘定义离散的值。
不同range分区和hash分区,
range分区与分区相关联,为分区列假设了一个值的自然范围,故不可能将该值的范围以外的分区组织到一起。
hash分区时不允许对数据的划分进行控制,因为系统使用的是散列函数来划分数据。
list分区的优点在于按照自然的方式将无序和不行馆的数据集合分组。
list分区不支持多列分区,如果将表按列分区,那么分区键就只能有表的一个单独列组成。
range分区和hash分区可以对多列进行分区。
示例:
SQL> conn sys/oracle@rac
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0
Connected as sys@RAC AS SYSDBA
SQL> create tablespace list_01 datafile '+DATA' size 100M;
SQL> create tablespace list_02 datafile '+DATA' size 100M;
SQL> create tablespace list_03 datafile '+DATA' size 100M;
SQL> create tablespace list_04 datafile '+DATA' size 100M;
SQL> conn scott/scott@rac
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0
Connected as scott@rac
SQL> create table list_test(
2 transaction_id number primary key,
3 item_id number(8) not null,
4 item_description varchar2(300))
5 partition by list (item_id)
6 (partition partlist_01 values (1001,1002) tablespace list_01,
7 partition partlist_02 values (1003,1004) tablespace list_02,
8 partition partlist_03 values (1005) tablespace list_03,
9 partition values (default) tablespace list_04);
Table created
SQL> insert into list_test values (1,1001,'youqi');
1 row inserted
SQL> insert into list_test values (2,1002,'youqi');
1 row inserted
SQL> insert into list_test values (3,1003,'muliao');
1 row inserted
SQL> insert into list_test values (4,1004,'muliao');
1 row inserted
SQL> insert into list_test values (5,1005,'gangcai');
1 row inserted
SQL> insert into list_test values (6,1006,'gangcai');
1 row inserted
SQL> insert into list_test values (8,1008,'gangcai');
1 row inserted
SQL> commit;
Commit complete
SQL> select * from list_test;
TRANSACTION_ID ITEM_ID ITEM_DESCRIPTION
-------------- --------- --------------------------------------------------------------------------------
1 1001 youqi
2 1002 youqi
3 1003 muliao
4 1004 muliao
5 1005 gangcai
6 1006 gangcai
8 1008 gangcai
7 rows selected
SQL> select * from list_test partition(partlist_04);
TRANSACTION_ID ITEM_ID ITEM_DESCRIPTION
-------------- --------- --------------------------------------------------------------------------------
6 1006 gangcai
8 1008 gangcai
4.复合分区(子分区)
组合分区的特点:
组合分区中,主要通过在不同列上,使用“范围分区”,“列表分区”以及“HASH分区”不同组合方式,进而实现组合分区。
组合分区中,分区本身没有相应的segment,可以认为是一个逻辑容器,只有子分区拥有实际的segment,用于存放数据。
组合分区注意事项:
在11g之前,组合分区只要有两种组合方式:“range-hash”以及“range-list”。
在11g之后,组合分区新增了四种组合方式:“range-range”、“list-range”、“list-hash"以及”list-list“。
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'))
);
关于表分区的一些日常维护性操作:
(1)增加分区(add)
(2)移动分区(move)
(3)截断分区(truncate)
(4)删除分区(drop)
(5)拆分分区(split)
(6)合并分区(merge)----hash分区不适用
(7)交换分区(exchange)
(8)收缩分区(coalesce)---仅适用于hash分区
测试数据来源于scott下的emp表。
--创建分区表TEST_RANGE_PARTITION
SQL>CREATE TABLE SCOTT.EMP_RANGE
(
EMPNO NUMBER(4,0),
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4,0),
HIRDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2,0)
)
PARTITION BY RANGE (SAL)
(partition "TEST_RANGE_SAL_01" values less than (1000),
partition "TEST_RANGE_SAL_02" values less than (2000),
partition "TEST_RANGE_SAL_03" values less than (3000),
partition "TEST_RANGE_SAL_MAX" values less than (MAXVALUE)
);
SQL> insert into emp_range select * from emp;
14 rows inserted
SQL> commit;
Commit complete
通过下面的方法,了解关于上面创建分区表的数据分布基本情况。
---查询分区表各分区的条件以及数据库分布情况
---可以看到此时NUM_ROWS列为空,主要是因为表的统计信息未收集导致的。
SQL> select a.table_name,partitioning_type,partition_name,high_value,num_rows from user_part_tables a,user_tab_partitions b where a.table_name=b.table_name and a.table_name='EMP_RANGE';
TABLE_NAME PARTITIONING_TYPE PARTITION_NAME HIGH_VALUE NUM_ROWS
------------------------------ ----------------- ------------------------------ -------------------------------------------------------------------------------- ----------
EMP_RANGE RANGE TEST_RANGE_SAL_01 1000
EMP_RANGE RANGE TEST_RANGE_SAL_02 2000
EMP_RANGE RANGE TEST_RANGE_SAL_03 3000
EMP_RANGE RANGE TEST_RANGE_SAL_MAX MAXVALUE
SQL> analyze table EMP_RANGE compute statistics;
Table analyzed
SQL> select a.table_name,partitioning_type,partition_name,high_value,num_rows from user_part_tables a,user_tab_partitions b where a.table_name=b.table_name and a.table_name='EMP_RANGE';
TABLE_NAME PARTITIONING_TYPE PARTITION_NAME HIGH_VALUE NUM_ROWS
------------------------------ ----------------- ------------------------------ -------------------------------------------------------------------------------- ----------
EMP_RANGE RANGE TEST_RANGE_SAL_01 1000 2
EMP_RANGE RANGE TEST_RANGE_SAL_02 2000 6
EMP_RANGE RANGE TEST_RANGE_SAL_03 3000 3
EMP_RANGE RANGE TEST_RANGE_SAL_MAX MAXVALUE 3
2.增加分区维护操作(add)
增加分区维护操作,主要针对当前分区表进行添加新分区的操作。
当分区表存在默认条件分区,如:range分区表的maxvalue分区,list分区表的default分区,此时增加分区操作会报错。
下面通过增加分区操作,直接为测试表增加分区TEST_RANGE_SAL_04
SQL> alter table emp_range add partition test_range_sal_04 values less than (4000);
alter table emp_range add partition test_range_sal_04 values less than (4000)
ORA-14074: 分区界限必须调整为高于最后一个分区界限
可以看到针对存在默认条件的分区表,无法执行增加分区操作。
解决办法:
1.删除原默认条件分区,待增加分区后,再重新添加默认条件分区。
2.使用拆分分区(split)的方式。
SQL> alter table emp_range drop partition test_range_sal_max;
Table altered
SQL> analyze table emp_range compute statistics;
Table analyzed
SQL> select a.table_name,partitioning_type,partition_name,high_value,num_rows from user_part_tables a,user_tab_partitions b where a.table_name=b.table_name and a.table_name='EMP_RANGE';
TABLE_NAME PARTITIONING_TYPE PARTITION_NAME HIGH_VALUE NUM_ROWS
------------------------------ ----------------- ------------------------------ -------------------------------------------------------------------------------- ----------
EMP_RANGE RANGE TEST_RANGE_SAL_01 1000 2
EMP_RANGE RANGE TEST_RANGE_SAL_02 2000 6
EMP_RANGE RANGE TEST_RANGE_SAL_03 3000 3
SQL> alter table emp_range add partition test_range_sal_04 values less than (4000);
Table altered
SQL> alter table emp_range add partition test_range_sal_05 values less than (maxvalue);
Table altered
SQL> analyze table emp_range compute statistics;
Table analyzed
SQL> select a.table_name,partitioning_type,partition_name,high_value,num_rows from user_part_tables a,user_tab_partitions b where a.table_name=b.table_name and a.table_name='EMP_RANGE';
TABLE_NAME PARTITIONING_TYPE PARTITION_NAME HIGH_VALUE NUM_ROWS
------------------------------ ----------------- ------------------------------ -------------------------------------------------------------------------------- ----------
EMP_RANGE RANGE TEST_RANGE_SAL_01 1000 2
EMP_RANGE RANGE TEST_RANGE_SAL_02 2000 6
EMP_RANGE RANGE TEST_RANGE_SAL_03 3000 3
EMP_RANGE RANGE TEST_RANGE_SAL_04 4000 0
EMP_RANGE RANGE TEST_RANGE_SAL_05 MAXVALUE 0
需要注意的是:对于默认条件的分区进行删除,其数据不会分布到其他分区,而是删除数据。因此在生产环境使用需慎重。
3.移动分区维护操作(move)
移动分区操作,只要是将分区从一个表空间迁移至另一个表空间中
查看当前分区对应的表空间情况
SQL> select table_name,partition_name,tablespace_name from user_tab_partitions where table_name='EMP_RANGE';
TABLE_NAME PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
EMP_RANGE TEST_RANGE_SAL_01 USERS
EMP_RANGE TEST_RANGE_SAL_02 USERS
EMP_RANGE TEST_RANGE_SAL_03 USERS
EMP_RANGE TEST_RANGE_SAL_04 USERS
EMP_RANGE TEST_RANGE_SAL_05 USERS
SQL> alter table emp_range move partition TEST_RANGE_SAL_02 tablespace LIST_01;
Table altered
SQL> select table_name,partition_name,tablespace_name from user_tab_partitions where table_name='EMP_RANGE';
TABLE_NAME PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
EMP_RANGE TEST_RANGE_SAL_01 USERS
EMP_RANGE TEST_RANGE_SAL_02 LIST_01
EMP_RANGE TEST_RANGE_SAL_03 USERS
EMP_RANGE TEST_RANGE_SAL_04 USERS
EMP_RANGE TEST_RANGE_SAL_05 USERS
4.截断分区维护操作(truncate)
截断分区维护操作,性对于传统的delete操作,删除数据的效率会更高,而且会降低高水位线。
---查看当前测试表分区情况及分区中的记录数
SQL> select table_name,partition_name,tablespace_name,num_rows from user_tab_partitions where table_name='EMP_RANGE';
TABLE_NAME PARTITION_NAME TABLESPACE_NAME NUM_ROWS
------------------------------ ------------------------------ ------------------------------ ----------
EMP_RANGE TEST_RANGE_SAL_01 USERS 2
EMP_RANGE TEST_RANGE_SAL_02 LIST_01 6
EMP_RANGE TEST_RANGE_SAL_03 USERS 3
EMP_RANGE TEST_RANGE_SAL_04 USERS 0
EMP_RANGE TEST_RANGE_SAL_05 USERS 0
---执行截断分区操作
SQL> alter table EMP_RANGE truncate partition TEST_RANGE_SAL_02;
Table truncated
---重新手机最新的测试表的统计信息
SQL> analyze table EMP_RANGE compute statistics;
Table analyzed
---验证截断后,分区的记录数的变化
SQL> select table_name,partition_name,tablespace_name,num_rows from user_tab_partitions where table_name='EMP_RANGE';
TABLE_NAME PARTITION_NAME TABLESPACE_NAME NUM_ROWS
------------------------------ ------------------------------ ------------------------------ ----------
EMP_RANGE TEST_RANGE_SAL_01 USERS 2
EMP_RANGE TEST_RANGE_SAL_02 LIST_01 0
EMP_RANGE TEST_RANGE_SAL_03 USERS 3
EMP_RANGE TEST_RANGE_SAL_04 USERS 0
EMP_RANGE TEST_RANGE_SAL_05 USERS 0
从上面的演示中可以看到,通过truncate操作,测试表EMP_RANGE_SAL_02分区数据被清空。
5.删除分区维护操作(drop)
对于分区的删除操作,需要注意,在删除分区后,分区所记录的数据,不会重分布至其他分区中,而是一并删除
---检查当前分区表的分区情况,以及数据的分布情况
SQL> select table_name,partition_name,tablespace_name,num_rows from user_tab_partitions where table_name='EMP_RANGE';
TABLE_NAME PARTITION_NAME TABLESPACE_NAME NUM_ROWS
------------------------------ ------------------------------ ------------------------------ ----------
EMP_RANGE TEST_RANGE_SAL_01 USERS 2
EMP_RANGE TEST_RANGE_SAL_02 LIST_01 0
EMP_RANGE TEST_RANGE_SAL_03 USERS 3
EMP_RANGE TEST_RANGE_SAL_04 USERS 0
EMP_RANGE TEST_RANGE_SAL_05 USERS 0
---执行分区的删除操作
SQL> alter table EMP_RANGE drop partition TEST_RANGE_SAL_03;
Table altered
---再次检查分区表的分区情况,以及数据的分布情况
SQL> select table_name,partition_name,tablespace_name,num_rows from user_tab_partitions where table_name='EMP_RANGE';
TABLE_NAME PARTITION_NAME TABLESPACE_NAME NUM_ROWS
------------------------------ ------------------------------ ------------------------------ ----------
EMP_RANGE TEST_RANGE_SAL_01 USERS 2
EMP_RANGE TEST_RANGE_SAL_02 LIST_01 0
EMP_RANGE TEST_RANGE_SAL_04 USERS 0
EMP_RANGE TEST_RANGE_SAL_05 USERS 0
6.查分分区维护操作(split)
在“增加分区维护操作“部分,提高了对于存在默认条件的分区表增加分区的两种方法,这里讲介绍通过拆分分区的方法来增加分区。
注意:在目标分区拆分后,被拆分的分区会按照拆分规则,将数据进行重新分布。
示例:
SQL> CREATE TABLE SCOTT.EMP_RANGE
2 (
3 EMPNO NUMBER(4,0),
4 ENAME VARCHAR2(10),
5 JOB VARCHAR2(9),
6 MGR NUMBER(4,0),
7 HIRDATE DATE,
8 SAL NUMBER(7,2),
9 COMM NUMBER(7,2),
10 DEPTNO NUMBER(2,0)
11 )
12 PARTITION BY RANGE (SAL)
13 (partition "TEST_RANGE_SAL_01" values less than (1000),
14 partition "TEST_RANGE_SAL_02" values less than (2000),
15 partition "TEST_RANGE_SAL_03" values less than (3000),
16 partition "TEST_RANGE_SAL_MAX" values less than (MAXVALUE)
17 );
Table created
SQL> insert into emp_range select * from emp;
14 rows inserted
SQL> commit;
Commit complete
--重新收集测试表的统计信息
SQL> analyze table emp_range compute statistics;
Table analyzed
--此时查看,数据在分区间的分布情况
SQL> select table_name,partition_name,tablespace_name,num_rows from user_tab_partitions where table_name='EMP_RANGE';
TABLE_NAME PARTITION_NAME TABLESPACE_NAME NUM_ROWS
------------------------------ ------------------------------ ------------------------------ ----------
EMP_RANGE TEST_RANGE_SAL_01 USERS 2
EMP_RANGE TEST_RANGE_SAL_02 USERS 6
EMP_RANGE TEST_RANGE_SAL_03 USERS 3
EMP_RANGE TEST_RANGE_SAL_MAX USERS 3
--查看TEST_RANGE_SAL_MAX的具体数据信息
SQL> select * from emp_range partition(TEST_RANGE_SAL_MAX);
EMPNO ENAME JOB MGR HIRDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7788 SCOTT ANALYST 7566 1987/4/19 3000.00 20
7839 KING PRESIDENT 1981/11/17 5000.00 10
7902 FORD ANALYST 7566 1981/12/3 3000.00 20
需求:
将SAL>=3000且SAL<=4000的数据放入新的分区TEST_RANGE_SAL_04
将SAL>=4000的数据保留在分区TEST_RANGE_SAL_MAX中。
--针对目标分区,执行拆分分区维护操作
--依据上面的需求,将数据拆分至分区TEST_RANGE_SAL_04以及TEST_RANGE_SAL_MAX中。
SQL> alter table emp_range split partition TEST_RANGE_SAL_MAX at (4000) into (partition TEST_RANGE_SAL_04,partition TEST_RANGE_SAL_MAX);
Table altered
--查看此时测试分区表的分区情况,以及数据分布情况
SQL> select table_name,partition_name,tablespace_name,num_rows from user_tab_partitions where table_name='EMP_RANGE';
TABLE_NAME PARTITION_NAME TABLESPACE_NAME NUM_ROWS
------------------------------ ------------------------------ ------------------------------ ----------
EMP_RANGE TEST_RANGE_SAL_01 USERS 2
EMP_RANGE TEST_RANGE_SAL_02 USERS 6
EMP_RANGE TEST_RANGE_SAL_03 USERS 3
EMP_RANGE TEST_RANGE_SAL_04 USERS
EMP_RANGE TEST_RANGE_SAL_MAX USERS
--重新收集测试表的统计信息
SQL> analyze table emp_range compute statistics;
Table analyzed
--查看此时测试分区表的分区情况,以及数据分布情况
SQL> select table_name,partition_name,tablespace_name,num_rows from user_tab_partitions where table_name='EMP_RANGE';
TABLE_NAME PARTITION_NAME TABLESPACE_NAME NUM_ROWS
------------------------------ ------------------------------ ------------------------------ ----------
EMP_RANGE TEST_RANGE_SAL_01 USERS 2
EMP_RANGE TEST_RANGE_SAL_02 USERS 6
EMP_RANGE TEST_RANGE_SAL_03 USERS 3
EMP_RANGE TEST_RANGE_SAL_04 USERS 2
EMP_RANGE TEST_RANGE_SAL_MAX USERS 1
--验证分区中实际的数据内容
SQL> select * from emp_range partition(TEST_RANGE_SAL_04);
EMPNO ENAME JOB MGR HIRDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7788 SCOTT ANALYST 7566 1987/4/19 3000.00 20
7902 FORD ANALYST 7566 1981/12/3 3000.00 20
SQL> select * from emp_range partition(TEST_RANGE_SAL_MAX);
EMPNO ENAME JOB MGR HIRDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7839 KING PRESIDENT 1981/11/17 5000.00 10
7.合并分区维护操作(merge)
合并分区操作,主要是将不同的分区,通过分区合并,进行整合。
需要注意:
1)对于list分区,合并的分区无限制要求
2)对于range分区,合并的分区必须相邻,否则无法进行合并操作。
3)对于hash分区,无法进行合并操作。
此外,对于range分区,下限值由边界值较低的分区决定,上限值由边界值较高的分区决定。
演示实例:
通过合并分区技术,将测试表的分区test_range_sal_01以及分区test_range_sal_02进行合并,具体如下:
----查看当前分区表的分区情况:
SQL> select table_name,partition_name,tablespace_name,num_rows from user_tab_partitions where table_name='EMP_RANGE';
TABLE_NAME PARTITION_NAME TABLESPACE_NAME NUM_ROWS
------------------------------ ------------------------------ ------------------------------ ----------
EMP_RANGE TEST_RANGE_SAL_01 USERS 2
EMP_RANGE TEST_RANGE_SAL_02 USERS 6
EMP_RANGE TEST_RANGE_SAL_03 USERS 3
EMP_RANGE TEST_RANGE_SAL_04 USERS 2
EMP_RANGE TEST_RANGE_SAL_MAX USERS 1
SQL> alter table emp_range merge partitions test_range_sal_01,test_range_sal_02 into partition test_range_sal_00;
Table altered
SQL> select table_name,partition_name,tablespace_name,num_rows from user_tab_partitions where table_name='EMP_RANGE';
TABLE_NAME PARTITION_NAME TABLESPACE_NAME NUM_ROWS
------------------------------ ------------------------------ ------------------------------ ----------
EMP_RANGE TEST_RANGE_SAL_00 USERS
EMP_RANGE TEST_RANGE_SAL_03 USERS 3
EMP_RANGE TEST_RANGE_SAL_04 USERS 2
EMP_RANGE TEST_RANGE_SAL_MAX USERS 1
SQL> analyze table emp_range compute statistics;
Table analyzed
SQL> select table_name,partition_name,tablespace_name,num_rows from user_tab_partitions where table_name='EMP_RANGE';
TABLE_NAME PARTITION_NAME TABLESPACE_NAME NUM_ROWS
------------------------------ ------------------------------ ------------------------------ ----------
EMP_RANGE TEST_RANGE_SAL_00 USERS 8
EMP_RANGE TEST_RANGE_SAL_03 USERS 3
EMP_RANGE TEST_RANGE_SAL_04 USERS 2
EMP_RANGE TEST_RANGE_SAL_MAX USERS 1
8.交换分区维护操作(exchange)
交换分区技术,主要是将一个非分区表的数据同“一个分区表的一个分区”进行数据交换。支持双向交换,既可以从分区表的分区中迁移到分分区表,也可以从非分区表迁移至分区表的分区中。
原则上,非分区表的结构、数据分布等,要复合分区表的目标分区的定义规则。
示例:
---清空测试分区表的数据
SQL> truncate table emp_range;
Table truncated
SQL> select table_name,partition_name,tablespace_name,num_rows from user_tab_partitions where table_name='EMP_RANGE';
TABLE_NAME PARTITION_NAME TABLESPACE_NAME NUM_ROWS
------------------------------ ------------------------------ ------------------------------ ----------
EMP_RANGE TEST_RANGE_SAL_00 USERS 8
EMP_RANGE TEST_RANGE_SAL_03 USERS 3
EMP_RANGE TEST_RANGE_SAL_04 USERS 2
EMP_RANGE TEST_RANGE_SAL_MAX USERS 1
SQL> analyze table emp_range compute statistics;
Table analyzed
SQL> select table_name,partition_name,tablespace_name,num_rows from user_tab_partitions where table_name='EMP_RANGE';
TABLE_NAME PARTITION_NAME TABLESPACE_NAME NUM_ROWS
------------------------------ ------------------------------ ------------------------------ ----------
EMP_RANGE TEST_RANGE_SAL_00 USERS 0
EMP_RANGE TEST_RANGE_SAL_03 USERS 0
EMP_RANGE TEST_RANGE_SAL_04 USERS 0
EMP_RANGE TEST_RANGE_SAL_MAX USERS 0
--创建一张基于emp表,sal<2000的测试非分区表emp_test
SQL> create table emp_test as select * from emp where sal < 2000;
Table created
SQL> select count(*) from emp_test;
COUNT(*)
----------
8
--执行分区交换动作
SQL> alter table emp_range exchange partition test_range_sal_00 with table emp_test;
Table altered
SQL> select table_name,partition_name,tablespace_name,num_rows from user_tab_partitions where table_name='emp_range';
TABLE_NAME PARTITION_NAME TABLESPACE_NAME NUM_ROWS
------------------------------ ------------------------------ ------------------------------ ----------
SQL> select table_name,partition_name,tablespace_name,num_rows from user_tab_partitions where table_name='EMP_RANGE';
TABLE_NAME PARTITION_NAME TABLESPACE_NAME NUM_ROWS
------------------------------ ------------------------------ ------------------------------ ----------
EMP_RANGE TEST_RANGE_SAL_00 USERS
EMP_RANGE TEST_RANGE_SAL_03 USERS 0
EMP_RANGE TEST_RANGE_SAL_04 USERS 0
EMP_RANGE TEST_RANGE_SAL_MAX USERS 0
SQL> analyze table emp_range compute statistics;
Table analyzed
SQL> select table_name,partition_name,tablespace_name,num_rows from user_tab_partitions where table_name='EMP_RANGE';
TABLE_NAME PARTITION_NAME TABLESPACE_NAME NUM_ROWS
------------------------------ ------------------------------ ------------------------------ ----------
EMP_RANGE TEST_RANGE_SAL_00 USERS 8
EMP_RANGE TEST_RANGE_SAL_03 USERS 0
EMP_RANGE TEST_RANGE_SAL_04 USERS 0
EMP_RANGE TEST_RANGE_SAL_MAX USERS 0
SQL> select count(*) from emp_test;
COUNT(*)
----------
0
---再次执行分区交换动作,查看分区表和非分区表的记录变化
SQL> alter table emp_range exchange partition test_range_sal_00 with table emp_test;
Table altered
SQL> analyze table emp_range compute statistics;
Table analyzed
SQL> select table_name,partition_name,tablespace_name,num_rows from user_tab_partitions where table_name='EMP_RANGE';
TABLE_NAME PARTITION_NAME TABLESPACE_NAME NUM_ROWS
------------------------------ ------------------------------ ------------------------------ ----------
EMP_RANGE TEST_RANGE_SAL_00 USERS 0
EMP_RANGE TEST_RANGE_SAL_03 USERS 0
EMP_RANGE TEST_RANGE_SAL_04 USERS 0
EMP_RANGE TEST_RANGE_SAL_MAX USERS 0
SQL> select count(*) from emp_test;
COUNT(*)
----------
8
可以看到,此时分区表的数据又再次转移回非分区表,证明了前面所述,分区交换技术,即可以从分区表中迁移到非分区表,也可以从非分区表迁移至分区表的分区中。
--若非分区表的数据,不符合分区表的规则,此时交换分区会抛出ora-14099的错误
SQL> truncate table emp_test;
Table truncated
SQL> insert into emp_test select * from emp;
14 rows inserted
SQL> commit;
Commit complete
SQL> alter table emp_range exchange partition test_range_sal_00 with table emp_test;
alter table emp_range exchange partition test_range_sal_00 with table emp_test
ORA-14099: 表中不是所有行都符合所指定的分区
解决办法:
通过without validation子句,可以避免数据校验,而交换成功,但会存在与分区规则不符的数据,因此该方法需慎重。
SQL> alter table emp_range exchange partition test_range_sal_00 with table emp_test without validation;
Table altered
SQL> select table_name,partition_name,tablespace_name,num_rows from user_tab_partitions where table_name='EMP_RANGE';
TABLE_NAME PARTITION_NAME TABLESPACE_NAME NUM_ROWS
------------------------------ ------------------------------ ------------------------------ ----------
EMP_RANGE TEST_RANGE_SAL_00 USERS 8
EMP_RANGE TEST_RANGE_SAL_03 USERS 0
EMP_RANGE TEST_RANGE_SAL_04 USERS 0
EMP_RANGE TEST_RANGE_SAL_MAX USERS 0
SQL> analyze table emp_range compute statistics;
Table analyzed
SQL> select table_name,partition_name,tablespace_name,num_rows from user_tab_partitions where table_name='EMP_RANGE';
TABLE_NAME PARTITION_NAME TABLESPACE_NAME NUM_ROWS
------------------------------ ------------------------------ ------------------------------ ----------
EMP_RANGE TEST_RANGE_SAL_00 USERS 14
EMP_RANGE TEST_RANGE_SAL_03 USERS 0
EMP_RANGE TEST_RANGE_SAL_04 USERS 0
EMP_RANGE TEST_RANGE_SAL_MAX USERS 0
若打算采用交换分区的方法,以实现分区表到分区表的转换,可以采用先创建一个只有默认条件的单一分区的表分区,在分区交换数据后,根据实际需要,通过前面提到的“拆分分区”的方法进行分区操作。
9.收缩分区维护操作(coalesce)
收缩分区维护操作,仅仅可以在hash分区以及组合分区的hash子分区上进行使用。
通过使用收缩分区技术,可以收缩当前hash分区的分区数量。
对于hash分区的数据,在收缩过程中,oracle会自动完成数据在分区键的重分布。
示例:
SQL> CREATE TABLE SCOTT.EMP_HASH
2 (
3 EMPNO NUMBER(4,0),
4 ENAME VARCHAR2(10),
5 JOB VARCHAR2(9),
6 MGR NUMBER(4,0),
7 HIRDATE DATE,
8 SAL NUMBER(7,2),
9 COMM NUMBER(7,2),
10 DEPTNO NUMBER(2,0)
11 )
12 PARTITION BY hash(ename)
13 (
14 partition employee_part01,
15 partition employee_part02
16 );
Table created
SQL> insert into emp_hash select * from emp;
14 rows inserted
SQL> commit;
Commit complete
SQL> analyze table emp_hash compute statistics;
Table analyzed
SQL> select table_name,partition_name,tablespace_name,num_rows from user_tab_partitions where table_name='EMP_HASH';
TABLE_NAME PARTITION_NAME TABLESPACE_NAME NUM_ROWS
------------------------------ ------------------------------ ------------------------------ ----------
EMP_HASH EMPLOYEE_PART01 USERS 8
EMP_HASH EMPLOYEE_PART02 USERS 6
SQL> alter table emp_hash coalesce partition;
Table altered
SQL> select table_name,partition_name,tablespace_name,num_rows from user_tab_partitions where table_name='EMP_HASH';
TABLE_NAME PARTITION_NAME TABLESPACE_NAME NUM_ROWS
------------------------------ ------------------------------ ------------------------------ ----------
EMP_HASH EMPLOYEE_PART01 USERS 8
需要注意:
当hash分区中只有一个分区时,此时无法进行收缩操作。
SQL> alter table emp_hash coalesce partition;
alter table emp_hash coalesce partition
ORA-14285: 不能 COALESCE (结合) 此散列分区的表或索引的唯一分区
分区表概述:
分区表就是通过使用分区技术,将一张大表,拆分成多个表分区(独立的segment),从而提升数据访问的性能,以及日常的可维护性。
分区表中,每个分区的逻辑结构必须相同。如:列名、数据类型、
分区表中,每个分区的物理存储参数可以不同。如:各个分区所在的表空间。
对于应用而言完全透明,分区前后没有变化,不需要进行修改。
需注意:
1.虽然各个分区可以存放在不同的表空间中,但这些表空间所使用的块大小(block_size)必须一致。
2.除了包含LONG以及LONG RAW字段的表无法使用分区表外,其他表均可以使用分区,包括含有LOB字段的表。
分区表的优势:
(1)改善查询性能:对分区对象的查询可以仅搜索自己关心的分区,提高检索速度;
(2)方便数据管理:因为分区表的数据存储在多个部分中,所以按分区加载和删除数据比在大表中加载和删除数据更容易。
(3)方便备份恢复:因为分区表比原表要小,所以针对分区的备份和恢复方法要比备份和恢复整个表的方法多。
一、Oracle数据库提供对表或者分区的方法有几种:
(1)范围分区(range分区)
(2)散列分区(HASH分区)
(3)列表分区(list分区)
(4)复合分区(子分区)
1.范围分区(range分区)
行映射到基于列值范围的分区
range分区又称为范围分区,基于分区键值的范围将数据映射到所建立的分区上。这就是最通用的分区类型。
特点:
范围分区只要依据分区键定义时给出的键值范围,根据实际的取值,进行分区的选择,进而在相应分区中存储数据。
范围分区比较合适存在以数字为导向,方便进行数字范围划分的数据列。
范围分区的数据分布可能不均匀。
创建范围分区时,必须指定以下内容:
分区方法:range
分区列:标识分区边界的描述
使用range分区的时候,要记住几条规则:
1)每个分区都包含values less than字名,定义了分区的上层边界,任何等于和大于分区键值的二进制都被添加到下一个高层分区中。
2)所有的分区,除了第一个,如果低于values less than所定义的下层边界,都放在前面的分区中。
MAXVALUE可以用来定义最高层的分区。MAXVALUE表示了虚拟的无限值。
SQL> select count(*) from emp;
COUNT(*)
----------
14
SQL> create table emp1 as select * from emp;
Table created
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980/12/17 800.00 20
7499 ALLEN SALESMAN 7698 1981/2/20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981/2/22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981/4/2 2975.00 20
7654 MARTIN SALESMAN 7698 1981/9/28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30
7782 CLARK MANAGER 7839 1981/6/9 2450.00 10
7788 SCOTT ANALYST 7566 1987/4/19 3000.00 20
7839 KING PRESIDENT 1981/11/17 5000.00 10
7844 TURNER SALESMAN 7698 1981/9/8 1500.00 0.00 30
7876 ADAMS CLERK 7788 1987/5/23 1100.00 20
7900 JAMES CLERK 7698 1981/12/3 950.00 30
7902 FORD ANALYST 7566 1981/12/3 3000.00 20
7934 MILLER CLERK 7782 1982/1/23 1300.00 10
14 rows selected
SQL>CREATE TABLE SCOTT.EMP_RANGE
(
EMPNO NUMBER,
ENAME VARCHAR2(10 BYTE),
JOB VARCHAR2(9 BYTE),
MGR NUMBER,
HIRDATE DATE,
SAL NUMBER,
COMM NUMBER,
DEPTNO NUMBER
)
PARTITION BY RANGE (HIRDATE)
(
PARTITION EMP_RANGE_01 VALUES LESS THAN (TO_DATE(' 1981-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION EMP_RANGE_02 VALUES LESS THAN (TO_DATE(' 1982-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION EMP_RANGE_03 VALUES LESS THAN (MAXVALUE)
);
SQL> insert into emp_range select * from emp;
14 rows inserted
SQL> commit;
Commit complete
SQL> select table_name,partitioning_type,partition_count,status from dba_part_tables where table_name='EMP_RANGE';
TABLE_NAME PARTITIONING_TYPE PARTITION_COUNT STATUS
------------------------------ ----------------- --------------- --------
EMP_RANGE RANGE 3 VALID
SQL> select partition_name,num_rows,tablespace_name,segment_created from dba_tab_partitions where table_name='EMP_RANGE';
PARTITION_NAME NUM_ROWS TABLESPACE_NAME SEGMENT_CREATED
------------------------------ ---------- ------------------------------ ---------------
EMP_RANGE_01 USERS YES
EMP_RANGE_02 USERS YES
EMP_RANGE_03 USERS YES
SQL> select * from emp_range partition(emp_range_01);
EMPNO ENAME JOB MGR HIRDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ----------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980/12/17 800 20
2.HASH分区(hash partition)
HASH分区特点:
hash分区主要是通过hash算法确定相应数据行应该被存放到哪个分区中。
hash分区比较合列差异值很多的数据列。
HASH分区的注意事项:
对于HASH分区,无法控制一条数据在分区之间的具体分布。具体分布由hash算法决定。
对于hash分区,如果更改分区的数量,将导致所有数据在分区间的重新分布。
HASH分区定义规则:
在定义HASH分区时,其分区数量应为2的N次方,比如:2,4,8,16等。
Hash分区能够很容易对数据进行分区,因为语法很简单,很容易实现。在下面这种情况下,使用hash分区比range分区更好:
(1)事先不知道需要将多少数据映射到给定范围的时候。
(2)分区的范围大小很难确实,或者很难平衡的时候。
(3)Range分区使诗句得到不希望的聚集时。
(4)性能特性,如并行DML、分区剪枝和分区连接很重要的时候
创建散列分区时,必须指定以下信息:
分区方法:hash
分区列:
分区数量或单独的分区描述
分裂、删除和合并分区不能应用于hash分区,但是,hash分区能够合并和添加。
创建hash分区有两种方法:一种方法是指定分区数量,另一种方法是指定分区的名字,但两者不能同时指定。
方法一:指定分区数量:
CREATE TABLE SCOTT.EMP_hash_1
(
EMPNO NUMBER,
ENAME VARCHAR2(10 BYTE),
JOB VARCHAR2(9 BYTE),
MGR NUMBER,
HIRDATE DATE,
SAL NUMBER,
COMM NUMBER,
DEPTNO NUMBER
)
PARTITION BY hash (ename) partitions 2;
方法二:指定分区的名字:
CREATE TABLE SCOTT.EMP_hash_2
(
EMPNO NUMBER,
ENAME VARCHAR2(10 BYTE),
JOB VARCHAR2(9 BYTE),
MGR NUMBER,
HIRDATE DATE,
SAL NUMBER,
COMM NUMBER,
DEPTNO NUMBER
)
PARTITION BY hash (ename)
(partition hash_01,
partition hash_02);
示例:
SQL> CREATE TABLE SCOTT.EMP_hash_2
2 (
3 EMPNO NUMBER,
4 ENAME VARCHAR2(10 BYTE),
5 JOB VARCHAR2(9 BYTE),
6 MGR NUMBER,
7 HIRDATE DATE,
8 SAL NUMBER,
9 COMM NUMBER,
10 DEPTNO NUMBER
11 )
12 PARTITION BY hash (ename)
13 (partition hash_01,
14 partition hash_02);
Table created
SQL> insert into emp_hash_2 select * from emp;
14 rows inserted
SQL> select * from emp_hash_2
2 ;
EMPNO ENAME JOB MGR HIRDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ----------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 1981/2/20 1600 300 30
7521 WARD SALESMAN 7698 1981/2/22 1250 500 30
7654 MARTIN SALESMAN 7698 1981/9/28 1250 1400 30
7698 BLAKE MANAGER 7839 1981/5/1 2850 30
7844 TURNER SALESMAN 7698 1981/9/8 1500 0 30
7876 ADAMS CLERK 7788 1987/5/23 1100 20
7900 JAMES CLERK 7698 1981/12/3 950 30
7934 MILLER CLERK 7782 1982/1/23 1300 10
7369 SMITH CLERK 7902 1980/12/17 800 20
7566 JONES MANAGER 7839 1981/4/2 2975 20
7782 CLARK MANAGER 7839 1981/6/9 2450 10
7788 SCOTT ANALYST 7566 1987/4/19 3000 20
7839 KING PRESIDENT 1981/11/17 5000 10
7902 FORD ANALYST 7566 1981/12/3 3000 20
14 rows selected
SQL> select * from emp_hash_2 partition(HASH_01);
EMPNO ENAME JOB MGR HIRDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ----------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 1981/2/20 1600 300 30
7521 WARD SALESMAN 7698 1981/2/22 1250 500 30
7654 MARTIN SALESMAN 7698 1981/9/28 1250 1400 30
7698 BLAKE MANAGER 7839 1981/5/1 2850 30
7844 TURNER SALESMAN 7698 1981/9/8 1500 0 30
7876 ADAMS CLERK 7788 1987/5/23 1100 20
7900 JAMES CLERK 7698 1981/12/3 950 30
7934 MILLER CLERK 7782 1982/1/23 1300 10
8 rows selected
SQL> analyze table emp_hash_2 compute statistics;
Table analyzed
SQL> select partition_name,num_rows,tablespace_name,segment_created from dba_tab_partitions where table_name='EMP_HASH_2';
PARTITION_NAME NUM_ROWS TABLESPACE_NAME SEGMENT_CREATED
------------------------------ ---------- ------------------------------ ---------------
HASH_01 8 USERS YES
HASH_02 6 USERS YES
3.列表分区(list分区)
列表分区特点:
列表分区主要依据分区键定义时给出的取值列表,根据实际的取值,进行分区的选择,进而在相应分区中存储数据。
列表分区比较适合列唯一取值有限,且较为固定的数据列。如:员工表的部门列。
列表分区的数据分布可能不均匀。
列表分区定义规则:
1.在定义范围分区时,每个分区必须使用values('value01','value02')子句。表示该分区存储包含相关value值的数据行。
2.在定义范围分区时,最后一个分区可以是values(default)。表示该分区存储未在其他分区定义的数据行。
list分区可以控制将行映射到分区中去。可以在每个分区的键撒花姑娘定义离散的值。
不同range分区和hash分区,
range分区与分区相关联,为分区列假设了一个值的自然范围,故不可能将该值的范围以外的分区组织到一起。
hash分区时不允许对数据的划分进行控制,因为系统使用的是散列函数来划分数据。
list分区的优点在于按照自然的方式将无序和不行馆的数据集合分组。
list分区不支持多列分区,如果将表按列分区,那么分区键就只能有表的一个单独列组成。
range分区和hash分区可以对多列进行分区。
示例:
SQL> conn sys/oracle@rac
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0
Connected as sys@RAC AS SYSDBA
SQL> create tablespace list_01 datafile '+DATA' size 100M;
SQL> create tablespace list_02 datafile '+DATA' size 100M;
SQL> create tablespace list_03 datafile '+DATA' size 100M;
SQL> create tablespace list_04 datafile '+DATA' size 100M;
SQL> conn scott/scott@rac
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0
Connected as scott@rac
SQL> create table list_test(
2 transaction_id number primary key,
3 item_id number(8) not null,
4 item_description varchar2(300))
5 partition by list (item_id)
6 (partition partlist_01 values (1001,1002) tablespace list_01,
7 partition partlist_02 values (1003,1004) tablespace list_02,
8 partition partlist_03 values (1005) tablespace list_03,
9 partition values (default) tablespace list_04);
Table created
SQL> insert into list_test values (1,1001,'youqi');
1 row inserted
SQL> insert into list_test values (2,1002,'youqi');
1 row inserted
SQL> insert into list_test values (3,1003,'muliao');
1 row inserted
SQL> insert into list_test values (4,1004,'muliao');
1 row inserted
SQL> insert into list_test values (5,1005,'gangcai');
1 row inserted
SQL> insert into list_test values (6,1006,'gangcai');
1 row inserted
SQL> insert into list_test values (8,1008,'gangcai');
1 row inserted
SQL> commit;
Commit complete
SQL> select * from list_test;
TRANSACTION_ID ITEM_ID ITEM_DESCRIPTION
-------------- --------- --------------------------------------------------------------------------------
1 1001 youqi
2 1002 youqi
3 1003 muliao
4 1004 muliao
5 1005 gangcai
6 1006 gangcai
8 1008 gangcai
7 rows selected
SQL> select * from list_test partition(partlist_04);
TRANSACTION_ID ITEM_ID ITEM_DESCRIPTION
-------------- --------- --------------------------------------------------------------------------------
6 1006 gangcai
8 1008 gangcai
4.复合分区(子分区)
组合分区的特点:
组合分区中,主要通过在不同列上,使用“范围分区”,“列表分区”以及“HASH分区”不同组合方式,进而实现组合分区。
组合分区中,分区本身没有相应的segment,可以认为是一个逻辑容器,只有子分区拥有实际的segment,用于存放数据。
组合分区注意事项:
在11g之前,组合分区只要有两种组合方式:“range-hash”以及“range-list”。
在11g之后,组合分区新增了四种组合方式:“range-range”、“list-range”、“list-hash"以及”list-list“。
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'))
);
关于表分区的一些日常维护性操作:
(1)增加分区(add)
(2)移动分区(move)
(3)截断分区(truncate)
(4)删除分区(drop)
(5)拆分分区(split)
(6)合并分区(merge)----hash分区不适用
(7)交换分区(exchange)
(8)收缩分区(coalesce)---仅适用于hash分区
测试数据来源于scott下的emp表。
--创建分区表TEST_RANGE_PARTITION
SQL>CREATE TABLE SCOTT.EMP_RANGE
(
EMPNO NUMBER(4,0),
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4,0),
HIRDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2,0)
)
PARTITION BY RANGE (SAL)
(partition "TEST_RANGE_SAL_01" values less than (1000),
partition "TEST_RANGE_SAL_02" values less than (2000),
partition "TEST_RANGE_SAL_03" values less than (3000),
partition "TEST_RANGE_SAL_MAX" values less than (MAXVALUE)
);
SQL> insert into emp_range select * from emp;
14 rows inserted
SQL> commit;
Commit complete
通过下面的方法,了解关于上面创建分区表的数据分布基本情况。
---查询分区表各分区的条件以及数据库分布情况
---可以看到此时NUM_ROWS列为空,主要是因为表的统计信息未收集导致的。
SQL> select a.table_name,partitioning_type,partition_name,high_value,num_rows from user_part_tables a,user_tab_partitions b where a.table_name=b.table_name and a.table_name='EMP_RANGE';
TABLE_NAME PARTITIONING_TYPE PARTITION_NAME HIGH_VALUE NUM_ROWS
------------------------------ ----------------- ------------------------------ -------------------------------------------------------------------------------- ----------
EMP_RANGE RANGE TEST_RANGE_SAL_01 1000
EMP_RANGE RANGE TEST_RANGE_SAL_02 2000
EMP_RANGE RANGE TEST_RANGE_SAL_03 3000
EMP_RANGE RANGE TEST_RANGE_SAL_MAX MAXVALUE
SQL> analyze table EMP_RANGE compute statistics;
Table analyzed
SQL> select a.table_name,partitioning_type,partition_name,high_value,num_rows from user_part_tables a,user_tab_partitions b where a.table_name=b.table_name and a.table_name='EMP_RANGE';
TABLE_NAME PARTITIONING_TYPE PARTITION_NAME HIGH_VALUE NUM_ROWS
------------------------------ ----------------- ------------------------------ -------------------------------------------------------------------------------- ----------
EMP_RANGE RANGE TEST_RANGE_SAL_01 1000 2
EMP_RANGE RANGE TEST_RANGE_SAL_02 2000 6
EMP_RANGE RANGE TEST_RANGE_SAL_03 3000 3
EMP_RANGE RANGE TEST_RANGE_SAL_MAX MAXVALUE 3
2.增加分区维护操作(add)
增加分区维护操作,主要针对当前分区表进行添加新分区的操作。
当分区表存在默认条件分区,如:range分区表的maxvalue分区,list分区表的default分区,此时增加分区操作会报错。
下面通过增加分区操作,直接为测试表增加分区TEST_RANGE_SAL_04
SQL> alter table emp_range add partition test_range_sal_04 values less than (4000);
alter table emp_range add partition test_range_sal_04 values less than (4000)
ORA-14074: 分区界限必须调整为高于最后一个分区界限
可以看到针对存在默认条件的分区表,无法执行增加分区操作。
解决办法:
1.删除原默认条件分区,待增加分区后,再重新添加默认条件分区。
2.使用拆分分区(split)的方式。
SQL> alter table emp_range drop partition test_range_sal_max;
Table altered
SQL> analyze table emp_range compute statistics;
Table analyzed
SQL> select a.table_name,partitioning_type,partition_name,high_value,num_rows from user_part_tables a,user_tab_partitions b where a.table_name=b.table_name and a.table_name='EMP_RANGE';
TABLE_NAME PARTITIONING_TYPE PARTITION_NAME HIGH_VALUE NUM_ROWS
------------------------------ ----------------- ------------------------------ -------------------------------------------------------------------------------- ----------
EMP_RANGE RANGE TEST_RANGE_SAL_01 1000 2
EMP_RANGE RANGE TEST_RANGE_SAL_02 2000 6
EMP_RANGE RANGE TEST_RANGE_SAL_03 3000 3
SQL> alter table emp_range add partition test_range_sal_04 values less than (4000);
Table altered
SQL> alter table emp_range add partition test_range_sal_05 values less than (maxvalue);
Table altered
SQL> analyze table emp_range compute statistics;
Table analyzed
SQL> select a.table_name,partitioning_type,partition_name,high_value,num_rows from user_part_tables a,user_tab_partitions b where a.table_name=b.table_name and a.table_name='EMP_RANGE';
TABLE_NAME PARTITIONING_TYPE PARTITION_NAME HIGH_VALUE NUM_ROWS
------------------------------ ----------------- ------------------------------ -------------------------------------------------------------------------------- ----------
EMP_RANGE RANGE TEST_RANGE_SAL_01 1000 2
EMP_RANGE RANGE TEST_RANGE_SAL_02 2000 6
EMP_RANGE RANGE TEST_RANGE_SAL_03 3000 3
EMP_RANGE RANGE TEST_RANGE_SAL_04 4000 0
EMP_RANGE RANGE TEST_RANGE_SAL_05 MAXVALUE 0
需要注意的是:对于默认条件的分区进行删除,其数据不会分布到其他分区,而是删除数据。因此在生产环境使用需慎重。
3.移动分区维护操作(move)
移动分区操作,只要是将分区从一个表空间迁移至另一个表空间中
查看当前分区对应的表空间情况
SQL> select table_name,partition_name,tablespace_name from user_tab_partitions where table_name='EMP_RANGE';
TABLE_NAME PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
EMP_RANGE TEST_RANGE_SAL_01 USERS
EMP_RANGE TEST_RANGE_SAL_02 USERS
EMP_RANGE TEST_RANGE_SAL_03 USERS
EMP_RANGE TEST_RANGE_SAL_04 USERS
EMP_RANGE TEST_RANGE_SAL_05 USERS
SQL> alter table emp_range move partition TEST_RANGE_SAL_02 tablespace LIST_01;
Table altered
SQL> select table_name,partition_name,tablespace_name from user_tab_partitions where table_name='EMP_RANGE';
TABLE_NAME PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
EMP_RANGE TEST_RANGE_SAL_01 USERS
EMP_RANGE TEST_RANGE_SAL_02 LIST_01
EMP_RANGE TEST_RANGE_SAL_03 USERS
EMP_RANGE TEST_RANGE_SAL_04 USERS
EMP_RANGE TEST_RANGE_SAL_05 USERS
4.截断分区维护操作(truncate)
截断分区维护操作,性对于传统的delete操作,删除数据的效率会更高,而且会降低高水位线。
---查看当前测试表分区情况及分区中的记录数
SQL> select table_name,partition_name,tablespace_name,num_rows from user_tab_partitions where table_name='EMP_RANGE';
TABLE_NAME PARTITION_NAME TABLESPACE_NAME NUM_ROWS
------------------------------ ------------------------------ ------------------------------ ----------
EMP_RANGE TEST_RANGE_SAL_01 USERS 2
EMP_RANGE TEST_RANGE_SAL_02 LIST_01 6
EMP_RANGE TEST_RANGE_SAL_03 USERS 3
EMP_RANGE TEST_RANGE_SAL_04 USERS 0
EMP_RANGE TEST_RANGE_SAL_05 USERS 0
---执行截断分区操作
SQL> alter table EMP_RANGE truncate partition TEST_RANGE_SAL_02;
Table truncated
---重新手机最新的测试表的统计信息
SQL> analyze table EMP_RANGE compute statistics;
Table analyzed
---验证截断后,分区的记录数的变化
SQL> select table_name,partition_name,tablespace_name,num_rows from user_tab_partitions where table_name='EMP_RANGE';
TABLE_NAME PARTITION_NAME TABLESPACE_NAME NUM_ROWS
------------------------------ ------------------------------ ------------------------------ ----------
EMP_RANGE TEST_RANGE_SAL_01 USERS 2
EMP_RANGE TEST_RANGE_SAL_02 LIST_01 0
EMP_RANGE TEST_RANGE_SAL_03 USERS 3
EMP_RANGE TEST_RANGE_SAL_04 USERS 0
EMP_RANGE TEST_RANGE_SAL_05 USERS 0
从上面的演示中可以看到,通过truncate操作,测试表EMP_RANGE_SAL_02分区数据被清空。
5.删除分区维护操作(drop)
对于分区的删除操作,需要注意,在删除分区后,分区所记录的数据,不会重分布至其他分区中,而是一并删除
---检查当前分区表的分区情况,以及数据的分布情况
SQL> select table_name,partition_name,tablespace_name,num_rows from user_tab_partitions where table_name='EMP_RANGE';
TABLE_NAME PARTITION_NAME TABLESPACE_NAME NUM_ROWS
------------------------------ ------------------------------ ------------------------------ ----------
EMP_RANGE TEST_RANGE_SAL_01 USERS 2
EMP_RANGE TEST_RANGE_SAL_02 LIST_01 0
EMP_RANGE TEST_RANGE_SAL_03 USERS 3
EMP_RANGE TEST_RANGE_SAL_04 USERS 0
EMP_RANGE TEST_RANGE_SAL_05 USERS 0
---执行分区的删除操作
SQL> alter table EMP_RANGE drop partition TEST_RANGE_SAL_03;
Table altered
---再次检查分区表的分区情况,以及数据的分布情况
SQL> select table_name,partition_name,tablespace_name,num_rows from user_tab_partitions where table_name='EMP_RANGE';
TABLE_NAME PARTITION_NAME TABLESPACE_NAME NUM_ROWS
------------------------------ ------------------------------ ------------------------------ ----------
EMP_RANGE TEST_RANGE_SAL_01 USERS 2
EMP_RANGE TEST_RANGE_SAL_02 LIST_01 0
EMP_RANGE TEST_RANGE_SAL_04 USERS 0
EMP_RANGE TEST_RANGE_SAL_05 USERS 0
6.查分分区维护操作(split)
在“增加分区维护操作“部分,提高了对于存在默认条件的分区表增加分区的两种方法,这里讲介绍通过拆分分区的方法来增加分区。
注意:在目标分区拆分后,被拆分的分区会按照拆分规则,将数据进行重新分布。
示例:
SQL> CREATE TABLE SCOTT.EMP_RANGE
2 (
3 EMPNO NUMBER(4,0),
4 ENAME VARCHAR2(10),
5 JOB VARCHAR2(9),
6 MGR NUMBER(4,0),
7 HIRDATE DATE,
8 SAL NUMBER(7,2),
9 COMM NUMBER(7,2),
10 DEPTNO NUMBER(2,0)
11 )
12 PARTITION BY RANGE (SAL)
13 (partition "TEST_RANGE_SAL_01" values less than (1000),
14 partition "TEST_RANGE_SAL_02" values less than (2000),
15 partition "TEST_RANGE_SAL_03" values less than (3000),
16 partition "TEST_RANGE_SAL_MAX" values less than (MAXVALUE)
17 );
Table created
SQL> insert into emp_range select * from emp;
14 rows inserted
SQL> commit;
Commit complete
--重新收集测试表的统计信息
SQL> analyze table emp_range compute statistics;
Table analyzed
--此时查看,数据在分区间的分布情况
SQL> select table_name,partition_name,tablespace_name,num_rows from user_tab_partitions where table_name='EMP_RANGE';
TABLE_NAME PARTITION_NAME TABLESPACE_NAME NUM_ROWS
------------------------------ ------------------------------ ------------------------------ ----------
EMP_RANGE TEST_RANGE_SAL_01 USERS 2
EMP_RANGE TEST_RANGE_SAL_02 USERS 6
EMP_RANGE TEST_RANGE_SAL_03 USERS 3
EMP_RANGE TEST_RANGE_SAL_MAX USERS 3
--查看TEST_RANGE_SAL_MAX的具体数据信息
SQL> select * from emp_range partition(TEST_RANGE_SAL_MAX);
EMPNO ENAME JOB MGR HIRDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7788 SCOTT ANALYST 7566 1987/4/19 3000.00 20
7839 KING PRESIDENT 1981/11/17 5000.00 10
7902 FORD ANALYST 7566 1981/12/3 3000.00 20
需求:
将SAL>=3000且SAL<=4000的数据放入新的分区TEST_RANGE_SAL_04
将SAL>=4000的数据保留在分区TEST_RANGE_SAL_MAX中。
--针对目标分区,执行拆分分区维护操作
--依据上面的需求,将数据拆分至分区TEST_RANGE_SAL_04以及TEST_RANGE_SAL_MAX中。
SQL> alter table emp_range split partition TEST_RANGE_SAL_MAX at (4000) into (partition TEST_RANGE_SAL_04,partition TEST_RANGE_SAL_MAX);
Table altered
--查看此时测试分区表的分区情况,以及数据分布情况
SQL> select table_name,partition_name,tablespace_name,num_rows from user_tab_partitions where table_name='EMP_RANGE';
TABLE_NAME PARTITION_NAME TABLESPACE_NAME NUM_ROWS
------------------------------ ------------------------------ ------------------------------ ----------
EMP_RANGE TEST_RANGE_SAL_01 USERS 2
EMP_RANGE TEST_RANGE_SAL_02 USERS 6
EMP_RANGE TEST_RANGE_SAL_03 USERS 3
EMP_RANGE TEST_RANGE_SAL_04 USERS
EMP_RANGE TEST_RANGE_SAL_MAX USERS
--重新收集测试表的统计信息
SQL> analyze table emp_range compute statistics;
Table analyzed
--查看此时测试分区表的分区情况,以及数据分布情况
SQL> select table_name,partition_name,tablespace_name,num_rows from user_tab_partitions where table_name='EMP_RANGE';
TABLE_NAME PARTITION_NAME TABLESPACE_NAME NUM_ROWS
------------------------------ ------------------------------ ------------------------------ ----------
EMP_RANGE TEST_RANGE_SAL_01 USERS 2
EMP_RANGE TEST_RANGE_SAL_02 USERS 6
EMP_RANGE TEST_RANGE_SAL_03 USERS 3
EMP_RANGE TEST_RANGE_SAL_04 USERS 2
EMP_RANGE TEST_RANGE_SAL_MAX USERS 1
--验证分区中实际的数据内容
SQL> select * from emp_range partition(TEST_RANGE_SAL_04);
EMPNO ENAME JOB MGR HIRDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7788 SCOTT ANALYST 7566 1987/4/19 3000.00 20
7902 FORD ANALYST 7566 1981/12/3 3000.00 20
SQL> select * from emp_range partition(TEST_RANGE_SAL_MAX);
EMPNO ENAME JOB MGR HIRDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7839 KING PRESIDENT 1981/11/17 5000.00 10
7.合并分区维护操作(merge)
合并分区操作,主要是将不同的分区,通过分区合并,进行整合。
需要注意:
1)对于list分区,合并的分区无限制要求
2)对于range分区,合并的分区必须相邻,否则无法进行合并操作。
3)对于hash分区,无法进行合并操作。
此外,对于range分区,下限值由边界值较低的分区决定,上限值由边界值较高的分区决定。
演示实例:
通过合并分区技术,将测试表的分区test_range_sal_01以及分区test_range_sal_02进行合并,具体如下:
----查看当前分区表的分区情况:
SQL> select table_name,partition_name,tablespace_name,num_rows from user_tab_partitions where table_name='EMP_RANGE';
TABLE_NAME PARTITION_NAME TABLESPACE_NAME NUM_ROWS
------------------------------ ------------------------------ ------------------------------ ----------
EMP_RANGE TEST_RANGE_SAL_01 USERS 2
EMP_RANGE TEST_RANGE_SAL_02 USERS 6
EMP_RANGE TEST_RANGE_SAL_03 USERS 3
EMP_RANGE TEST_RANGE_SAL_04 USERS 2
EMP_RANGE TEST_RANGE_SAL_MAX USERS 1
SQL> alter table emp_range merge partitions test_range_sal_01,test_range_sal_02 into partition test_range_sal_00;
Table altered
SQL> select table_name,partition_name,tablespace_name,num_rows from user_tab_partitions where table_name='EMP_RANGE';
TABLE_NAME PARTITION_NAME TABLESPACE_NAME NUM_ROWS
------------------------------ ------------------------------ ------------------------------ ----------
EMP_RANGE TEST_RANGE_SAL_00 USERS
EMP_RANGE TEST_RANGE_SAL_03 USERS 3
EMP_RANGE TEST_RANGE_SAL_04 USERS 2
EMP_RANGE TEST_RANGE_SAL_MAX USERS 1
SQL> analyze table emp_range compute statistics;
Table analyzed
SQL> select table_name,partition_name,tablespace_name,num_rows from user_tab_partitions where table_name='EMP_RANGE';
TABLE_NAME PARTITION_NAME TABLESPACE_NAME NUM_ROWS
------------------------------ ------------------------------ ------------------------------ ----------
EMP_RANGE TEST_RANGE_SAL_00 USERS 8
EMP_RANGE TEST_RANGE_SAL_03 USERS 3
EMP_RANGE TEST_RANGE_SAL_04 USERS 2
EMP_RANGE TEST_RANGE_SAL_MAX USERS 1
8.交换分区维护操作(exchange)
交换分区技术,主要是将一个非分区表的数据同“一个分区表的一个分区”进行数据交换。支持双向交换,既可以从分区表的分区中迁移到分分区表,也可以从非分区表迁移至分区表的分区中。
原则上,非分区表的结构、数据分布等,要复合分区表的目标分区的定义规则。
示例:
---清空测试分区表的数据
SQL> truncate table emp_range;
Table truncated
SQL> select table_name,partition_name,tablespace_name,num_rows from user_tab_partitions where table_name='EMP_RANGE';
TABLE_NAME PARTITION_NAME TABLESPACE_NAME NUM_ROWS
------------------------------ ------------------------------ ------------------------------ ----------
EMP_RANGE TEST_RANGE_SAL_00 USERS 8
EMP_RANGE TEST_RANGE_SAL_03 USERS 3
EMP_RANGE TEST_RANGE_SAL_04 USERS 2
EMP_RANGE TEST_RANGE_SAL_MAX USERS 1
SQL> analyze table emp_range compute statistics;
Table analyzed
SQL> select table_name,partition_name,tablespace_name,num_rows from user_tab_partitions where table_name='EMP_RANGE';
TABLE_NAME PARTITION_NAME TABLESPACE_NAME NUM_ROWS
------------------------------ ------------------------------ ------------------------------ ----------
EMP_RANGE TEST_RANGE_SAL_00 USERS 0
EMP_RANGE TEST_RANGE_SAL_03 USERS 0
EMP_RANGE TEST_RANGE_SAL_04 USERS 0
EMP_RANGE TEST_RANGE_SAL_MAX USERS 0
--创建一张基于emp表,sal<2000的测试非分区表emp_test
SQL> create table emp_test as select * from emp where sal < 2000;
Table created
SQL> select count(*) from emp_test;
COUNT(*)
----------
8
--执行分区交换动作
SQL> alter table emp_range exchange partition test_range_sal_00 with table emp_test;
Table altered
SQL> select table_name,partition_name,tablespace_name,num_rows from user_tab_partitions where table_name='emp_range';
TABLE_NAME PARTITION_NAME TABLESPACE_NAME NUM_ROWS
------------------------------ ------------------------------ ------------------------------ ----------
SQL> select table_name,partition_name,tablespace_name,num_rows from user_tab_partitions where table_name='EMP_RANGE';
TABLE_NAME PARTITION_NAME TABLESPACE_NAME NUM_ROWS
------------------------------ ------------------------------ ------------------------------ ----------
EMP_RANGE TEST_RANGE_SAL_00 USERS
EMP_RANGE TEST_RANGE_SAL_03 USERS 0
EMP_RANGE TEST_RANGE_SAL_04 USERS 0
EMP_RANGE TEST_RANGE_SAL_MAX USERS 0
SQL> analyze table emp_range compute statistics;
Table analyzed
SQL> select table_name,partition_name,tablespace_name,num_rows from user_tab_partitions where table_name='EMP_RANGE';
TABLE_NAME PARTITION_NAME TABLESPACE_NAME NUM_ROWS
------------------------------ ------------------------------ ------------------------------ ----------
EMP_RANGE TEST_RANGE_SAL_00 USERS 8
EMP_RANGE TEST_RANGE_SAL_03 USERS 0
EMP_RANGE TEST_RANGE_SAL_04 USERS 0
EMP_RANGE TEST_RANGE_SAL_MAX USERS 0
SQL> select count(*) from emp_test;
COUNT(*)
----------
0
---再次执行分区交换动作,查看分区表和非分区表的记录变化
SQL> alter table emp_range exchange partition test_range_sal_00 with table emp_test;
Table altered
SQL> analyze table emp_range compute statistics;
Table analyzed
SQL> select table_name,partition_name,tablespace_name,num_rows from user_tab_partitions where table_name='EMP_RANGE';
TABLE_NAME PARTITION_NAME TABLESPACE_NAME NUM_ROWS
------------------------------ ------------------------------ ------------------------------ ----------
EMP_RANGE TEST_RANGE_SAL_00 USERS 0
EMP_RANGE TEST_RANGE_SAL_03 USERS 0
EMP_RANGE TEST_RANGE_SAL_04 USERS 0
EMP_RANGE TEST_RANGE_SAL_MAX USERS 0
SQL> select count(*) from emp_test;
COUNT(*)
----------
8
可以看到,此时分区表的数据又再次转移回非分区表,证明了前面所述,分区交换技术,即可以从分区表中迁移到非分区表,也可以从非分区表迁移至分区表的分区中。
--若非分区表的数据,不符合分区表的规则,此时交换分区会抛出ora-14099的错误
SQL> truncate table emp_test;
Table truncated
SQL> insert into emp_test select * from emp;
14 rows inserted
SQL> commit;
Commit complete
SQL> alter table emp_range exchange partition test_range_sal_00 with table emp_test;
alter table emp_range exchange partition test_range_sal_00 with table emp_test
ORA-14099: 表中不是所有行都符合所指定的分区
解决办法:
通过without validation子句,可以避免数据校验,而交换成功,但会存在与分区规则不符的数据,因此该方法需慎重。
SQL> alter table emp_range exchange partition test_range_sal_00 with table emp_test without validation;
Table altered
SQL> select table_name,partition_name,tablespace_name,num_rows from user_tab_partitions where table_name='EMP_RANGE';
TABLE_NAME PARTITION_NAME TABLESPACE_NAME NUM_ROWS
------------------------------ ------------------------------ ------------------------------ ----------
EMP_RANGE TEST_RANGE_SAL_00 USERS 8
EMP_RANGE TEST_RANGE_SAL_03 USERS 0
EMP_RANGE TEST_RANGE_SAL_04 USERS 0
EMP_RANGE TEST_RANGE_SAL_MAX USERS 0
SQL> analyze table emp_range compute statistics;
Table analyzed
SQL> select table_name,partition_name,tablespace_name,num_rows from user_tab_partitions where table_name='EMP_RANGE';
TABLE_NAME PARTITION_NAME TABLESPACE_NAME NUM_ROWS
------------------------------ ------------------------------ ------------------------------ ----------
EMP_RANGE TEST_RANGE_SAL_00 USERS 14
EMP_RANGE TEST_RANGE_SAL_03 USERS 0
EMP_RANGE TEST_RANGE_SAL_04 USERS 0
EMP_RANGE TEST_RANGE_SAL_MAX USERS 0
若打算采用交换分区的方法,以实现分区表到分区表的转换,可以采用先创建一个只有默认条件的单一分区的表分区,在分区交换数据后,根据实际需要,通过前面提到的“拆分分区”的方法进行分区操作。
9.收缩分区维护操作(coalesce)
收缩分区维护操作,仅仅可以在hash分区以及组合分区的hash子分区上进行使用。
通过使用收缩分区技术,可以收缩当前hash分区的分区数量。
对于hash分区的数据,在收缩过程中,oracle会自动完成数据在分区键的重分布。
示例:
SQL> CREATE TABLE SCOTT.EMP_HASH
2 (
3 EMPNO NUMBER(4,0),
4 ENAME VARCHAR2(10),
5 JOB VARCHAR2(9),
6 MGR NUMBER(4,0),
7 HIRDATE DATE,
8 SAL NUMBER(7,2),
9 COMM NUMBER(7,2),
10 DEPTNO NUMBER(2,0)
11 )
12 PARTITION BY hash(ename)
13 (
14 partition employee_part01,
15 partition employee_part02
16 );
Table created
SQL> insert into emp_hash select * from emp;
14 rows inserted
SQL> commit;
Commit complete
SQL> analyze table emp_hash compute statistics;
Table analyzed
SQL> select table_name,partition_name,tablespace_name,num_rows from user_tab_partitions where table_name='EMP_HASH';
TABLE_NAME PARTITION_NAME TABLESPACE_NAME NUM_ROWS
------------------------------ ------------------------------ ------------------------------ ----------
EMP_HASH EMPLOYEE_PART01 USERS 8
EMP_HASH EMPLOYEE_PART02 USERS 6
SQL> alter table emp_hash coalesce partition;
Table altered
SQL> select table_name,partition_name,tablespace_name,num_rows from user_tab_partitions where table_name='EMP_HASH';
TABLE_NAME PARTITION_NAME TABLESPACE_NAME NUM_ROWS
------------------------------ ------------------------------ ------------------------------ ----------
EMP_HASH EMPLOYEE_PART01 USERS 8
需要注意:
当hash分区中只有一个分区时,此时无法进行收缩操作。
SQL> alter table emp_hash coalesce partition;
alter table emp_hash coalesce partition
ORA-14285: 不能 COALESCE (结合) 此散列分区的表或索引的唯一分区