oracle根据分区移动,Oracle_高级功能(6) 分区

oracle分区表

1.分区表：

当表中的数据量不断增大，查询数据的速度就会变慢，应用程序的性能就会下降，这时就应该考虑对表进行分区。

表进行分区后，逻辑上表仍然是一张完整的表，只是将表中的数据在物理上存放到多个表空间(物理文件上)，

这样查询数据时，不至于每次都扫描整张表。

2.表分区的具体作用

Oracle的表分区功能通过改善可管理性、性能和可用性，从而为各式应用程序带来了极大的好处。

通常，分区可以使某些查询以及维护操作的性能大大提高。

此外,分区还可以极大简化常见的管理任务，分区是构建千兆字节数据系统或超高可用性系统的关键工具。

每个分区有自己的名称，还可以选择自己的存储特性。

从数据库管理员的角度来看，一个分区后的对象具有多个段，这些段既可进行集体管理，也可单独管理，

这就使数据库管理员在管理分区后的对象时有相当大的灵活性。

但是，从应用程序的角度来看，分区后的表与非分区表完全相同，使用sql dml命令访问分区后的表时，无需任何修改。

什么时候使用分区表?

1、表的大小超过2GB。

2、表中包含历史数据，新的数据被增加到新的分区中。

3.表分区的优缺点

优点：

改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索速度。

增强可用性：如果表的某个分区出现故障，表在其他分区的数据仍然可用；

维护方便：如果表的某个分区出现故障，需要修复数据，只修复该分区即可；

均衡I/O：可以把不同的分区映射到磁盘以平衡I/O，改善整个系统性能。

缺点：

分区表相关：已经存在的表没有方法可以直接转化为分区表。

4.表分区的几种类型及操作方法

4.1 范围分区：

范围分区将数据基于范围映射到每一个分区，这个范围是在创建分区时指定的分区键决定的。

当使用范围分区时，请考虑以下几个规则：

每一个分区都必须有一个values less then子句，它指定了一个不包括在该分区中的上限值。

所有分区，除了第一个，都会有一个隐式的下限值，这个值就是此分区的前一个分区的上限值。

在最高的分区中，maxvalue被定义。MAXVALUE代表了一个不确定的值。

这个值高于其它分区中的任何分区键的值，也可以理解为高于任何分区中指定的VALUE LESS THEN的值，同时包括空值。

例1：

假设有一个顾客表，表中有数据200行，将此表通过c_id进行分区，每个分区存储100行，

并且每个分区保存到单独的表空间中，这样数据文件就可以跨越多个物理磁盘。

create table part_range1

(

c_id number primary key,

name varchar2(30),

phone varchar2(15),

email varchar2(80),

status varchar2(1)

)

partition by range (c_id)

(

partition cus_part1 values less than (101) tablespace USERS,

partition cus_part2 values less than (201) tablespace TS_FIND

);

insert into part_range1 (c_id,name) values (1,‘name‘);

insert into part_range1 (c_id,name) values (100,‘name‘);

insert into part_range1 (c_id,name) values (101,‘name‘);

insert into part_range1 (c_id,name) values (200,‘name‘);

select * from part_range1;

select * from part_range1 partition (cus_part1);

select * from part_range1 partition (cus_part2);

insert into part_range1 (c_id,name) values (201,‘name‘);

给part_range1增加分区：

alter table part_range1 add partition cus_part3 values less than (301) tablespace ts_find;

例2：使用maxvalue扩展例1。

create table part_range2

(

c_id number primary key,

name varchar2(30),

phone varchar2(15),

email varchar2(80),

status varchar2(1)

)

partition by range (c_id)

(

partition cus_part1 values less than (101) tablespace USERS,

partition cus_part2 values less than (201) tablespace TS_FIND,

partition cus_part3 values less than (maxvalue) tablespace TS_FIND

);

insert into part_range2 (c_id,name) values (201,‘name‘);

insert into part_range2 (c_id,name) values (9999,‘name‘);

select * from part_range2;

select * from part_range2 partition (cus_part3);

注意：范围分区表使用了maxvalue后将不能在增加分区。

例3：销售订单表，按时间范围分区

create table part_range3

(

order_id number(7) not null primary key,

order_date date,

total_amount number,

custotmer_id number(7)

)

partition by range (order_date)

(

partition month01 values less than (to_date(‘2017-2-1‘,‘yyyy-mm-dd‘)) tablespace users,

partition month02 values less than (to_date(‘2017-3-1‘,‘yyyy-mm-dd‘)) tablespace ts_find,

partition month03 values less than (to_date(‘2017-4-1‘,‘yyyy-mm-dd‘)) tablespace ts_find

);

partition by range (order_date)

(

partition year15 values less than (to_date(‘2016-1-1‘,‘yyyy-mm-dd‘)) tablespace users,

partition year16 values less than (to_date(‘2017-1-1‘,‘yyyy-mm-dd‘)) tablespace ts_find,

partition year17 values less than (to_date(‘2018-1-1‘,‘yyyy-mm-dd‘)) tablespace ts_find

)；

4.2 列表分区：

该分区的特点是基于某个特定取值的列的取值来分区。

例1:问题投诉表，根据问题状态分区，状态取值：‘active‘,‘inactive‘

create table part_list1

(

problem_id number(7) not null primary key,

description varchar2(2000),

customer_id number(7),

date_entered date,

status varchar2(20)

)

partition by list (status)

(

partition active values (‘active‘) tablespace users,

partition inactive values (‘inactive‘) tablespace ts_find

);

--测试

insert into part_list1 (problem_id,status) values (11,‘active‘);

insert into part_list1 (problem_id,status) values (12,‘inactive‘);

select * from part_list1;

select * from part_list1 partition (active);

select * from part_list1 partition (inactive);

例2:北京人口，按行政区划分区

create table part_list2

(

id number(8) primary key ,

name varchar (20),

area varchar (10)

)

partition by list (area)

(

partition area01 values (‘东城‘,‘西城‘) tablespace users,

partition area02 values (‘海淀‘,‘昌平‘) tablespace ts_find

);

例3:按照余数分区--不支持

create table part_list3

(

id number(8) not null primary key,

des varchar2(2000)

)

partition by list (mod(id,3))

(

partition p1 values (0) tablespace users,

partition p2 values (1) tablespace ts_find,

partition p3 values (2) tablespace ts_find

);

4.3 散列分区：

在列值上使用散列算法，以确定将行放入哪个分区中。

当列的值没有合适的条件时，建议使用散列分区。

散列分区是通过指定分区编号来均匀分布数据的一种分区类型

(分区个数应该是2的次方，否则分布不均匀)。

hash分区最主要的机制是根据hash算法来计算具体某条纪录应该插入到哪个分区中。

例1：2个分区

create table part_hash1

(

col number(8),

inf varchar2(100)

)

partition by hash (col)

(

partition p1 tablespace users,

partition p2 tablespace ts_find

);

insert into part_hash1 values (1,‘b‘);

insert into part_hash1 values (2,‘b‘);

insert into part_hash1 values (3,‘b‘);

insert into part_hash1 values (4,‘b‘);

insert into part_hash1 values (5,‘b‘);

insert into part_hash1 values (6,‘b‘);

insert into part_hash1 values (7,‘b‘);

insert into part_hash1 values (8,‘b‘);

select * from part_hash1;

select * from part_hash1 partition(p1);

select * from part_hash1 partition(p2);

例2：仅指定分区个数的简单写法，分区名由系统指定

create table part_hash2

(

empno number (4),

ename varchar2 (30),

sal number

)

partition by hash (empno) partitions 4

store in (users,ts_find);

--查看系统指定的分区名

select * from user_tab_partitions where table_name=‘PART_HASH2‘;

--复制数据

insert into part_hash2 select empno,ename,sal from emp;

select * from part_hash2;

select * from part_hash2 partition(SYS_P42);

好处：

对于分区本身不需要定期的进行分区加入(范围分区和LIST分区需要定期的对新加入的值新建分区)

可以消除访问热点块及索引热点块，由于索引是排序后的结构，对于一列自增的列加入范围分区，

可能对索引的高位块进行频繁的数据插入，导致频繁的写入和分裂。

对于这样的索引如果加入散列分区索引即可消除。

限制：

分区不能太多，典型的大约1000个分区，那么在分区触发(谓词导致索引范围扫描)的并行访问操作时可能更慢，

因为有非常多额外的分区维护操作。

对于长期使用范围扫描的字段不适合散列分区，因为这样会导致多个分区扫描，而对于经常唯一扫描的字段适合建立HASH分区。

4.4 组合范围列表分区:(范围+列表分区)

这种分区是基于范围分区和列表分区，表首先按某列进行范围分区，

然后再按某列进行列表分区，分区之中的分区被称为子分区。

create table part_range_list

(

product_id varchar2(5),

sales_date date,

sales_cost number(10),

status varchar2(20)

)

partition by range(sales_date) subpartition by list (status)

(

partition p1 values less than(to_date(‘2017-02-01‘,‘yyyy-mm-dd‘)) tablespace users

(

subpartition p1sub1 values (‘active‘) tablespace users,

subpartition p1sub2 values (‘inactive‘) tablespace users

),

partition p2 values less than (to_date(‘2017-03-01‘,‘yyyy-mm-dd‘)) tablespace ts_find

(

subpartition p2sub1 values (‘active‘) tablespace ts_find,

subpartition p2sub2 values (‘inactive‘) tablespace ts_find

)

);

insert into part_range_list (product_id,sales_date,status) values (1,sysdate-480,‘active‘);

insert into part_range_list (product_id,sales_date,status) values (2,sysdate-481,‘inactive‘);

insert into part_range_list (product_id,sales_date,status) values (3,sysdate-451,‘active‘);

insert into part_range_list (product_id,sales_date,status) values (4,sysdate-450,‘inactive‘);

select * from part_range_list;

select * from part_range_list partition (p1);

select * from part_range_list partition (p2);

select * from part_range_list subpartition (p1sub1);

select * from part_range_list subpartition (p1sub2);

select * from part_range_list subpartition (p2sub1);

select * from part_range_list subpartition (p2sub2);

4.5 复合范围散列分区：(范围+散列分区)

这种分区是基于范围分区和散列分区，

表首先按某列进行范围分区，然后再按某列进行散列分区。

create table part_range_hash

(

transaction_id number primary key,

item_id number(8) not null,

item_description varchar2(300),

transaction_date date

)

partition by range(transaction_date) subpartition by hash(transaction_id)

subpartitions 2 store in (users,ts_find)

(

partition part_01 values less than(to_date(‘2017-01-01‘,‘yyyy-mm-dd‘)),

partition part_02 values less than(to_date(‘2018-01-01‘,‘yyyy-mm-dd‘))

);

5.分区相关数据字典

--查询所有的的分区表

select * from user_tables a where a.partitioned=‘YES‘

--显示分区表信息：

select * from dba_part_tables;

select * from all_part_tables;

select * from user_part_tables;

--显示分区信息：

select * from dba_tab_partitions

select * from all_tab_partitions

select * from user_tab_partitions

--显示子分区信息

select * from dba_tab_subpartitions

select * from all_tab_subpartitions

select * from user_tab_subpartitions

--显示分区列信息：

select * from dba_part_key_columns

select * from all_part_key_columns

select * from user_part_key_columns

--显示子分区列信息：

select * from dba_subpart_key_columns

select * from all_subpart_key_columns

select * from user_subpart_key_columns

6.表分区相关操作：

6.1 添加分区

给part_range1增加分区：

alter table part_range1 add partition cus_part3 values less than (301) tablespace ts_find;

insert into part_range1 (customer_id,name) values (201,‘name‘);

注意：以上添加的分区界限应该高于最后一个分区界限。

给part_range2增加分区：maxvalue

alter table part_range2 add partition cus_part4 values less than (301) tablespace ts_find;

--ORA-14074: 分区界限必须调整为高于最后一个分区界限

给组合范围列表分区part_range_list增加分区

alter table part_range_list add partition p3

values less than (to_date(‘2017-4-1‘,‘yyyy-mm-dd‘)) tablespace ts_find;

--查看

select * from user_part_tables where table_name=‘PART_RANGE_LIST‘;

select * from user_tab_partitions where table_name=‘PART_RANGE_LIST‘; --默认生成了一个子分区

select * from user_tab_subpartitions where table_name=‘PART_RANGE_LIST‘; --子分区名：SYS_SUBP65

select * from user_part_key_columns where name=‘PART_RANGE_LIST‘;

select * from user_subpart_key_columns where name=‘PART_RANGE_LIST‘;

检查发现p3有一个子分区，并且子分区的键值是DEFAULT，插数据验证：

insert into part_range_list (product_id,sales_date,sales_cost,status) values (111,to_date(‘2017-03-31‘,‘yyyy-mm-dd‘),1,‘active‘);

insert into part_range_list (product_id,sales_date,sales_cost,status) values (112,to_date(‘2017-03-31‘,‘yyyy-mm-dd‘),1,‘inactive‘);

select * from part_range_list;

select * from part_range_list partition(p3);

select * from part_range_list subpartition(SYS_SUBP49);

6.2 添加子分区

part_range_list表的p3分区添加子分区(要求：与P1子分区的键值一致)

alter table part_range_list modify partition p3 add subpartition p3sub1 values(‘active‘);

alter table part_range_list modify partition p3 add subpartition p3sub2 values(‘inactive‘);

--ORA-14621: 在 DEFAULT 子分区已存在时无法添加子分区

6.3 删除(DEFAULT)子分区：

alter table part_range_list drop subpartition SYS_SUBP49;

--ORA-14629: 无法删除一个分区中唯一的子分区

6.4 无法删除一个分区中唯一的子分区时将分区删除

alter table part_range_list drop partition p3;

6.5 正确的添加组合分区的方式：添加组合分区时将子分区一并添加

alter table part_range_list add partition p3 values less than (to_date(‘2017-4-1‘,‘yyyy-mm-dd‘)) tablespace ts_find

(subpartition p3sub1 values (‘active‘) tablespace ts_find,

subpartition p3sub2 values (‘inactive‘) tablespace ts_find);

--验证

insert into part_range_list (product_id,sales_date,sales_cost,status)

values (111,to_date(‘2017-03-31‘,‘yyyy-mm-dd‘),1,‘active‘);

insert into part_range_list (product_id,sales_date,sales_cost,status)

values (112,to_date(‘2017-03-31‘,‘yyyy-mm-dd‘),1,‘inactive‘);

select * from part_range_list;

select * from part_range_list partition(p3);

select * from part_range_list subpartition(p3sub1);

6.6 删除子分区

alter table part_range_list drop subpartition p3sub1;

--alter table part_range_list drop subpartition p3sub2;

6.7 删除分区：

alter table part_range_list drop partition p3;

alter table part_range_list drop partition p2;

alter table part_range_list drop partition p1;

--ORA-14083: 无法删除分区表的唯一分区

如果删除的分区是表中唯一的分区，那么此分区将不能被删除，要想删除此分区，必须删除表。

drop table part_range_list;

6.8 截断分区

截断某个分区是指删除某个分区中的数据，并不会删除分区，也不会删除其它分区中的数据。

当表中即使只有一个分区时，也可以截断该分区。

--再创建分区表part_range_list，并插入数据

insert into part_range_list (product_id,sales_date,sales_cost,status) values (111,to_date(‘2017-02-21‘,‘yyyy-mm-dd‘),1,‘active‘);

insert into part_range_list (product_id,sales_date,sales_cost,status) values (112,to_date(‘2017-02-21‘,‘yyyy-mm-dd‘),1,‘inactive‘);

select * from part_range_list;

select * from part_range_list partition(p2);

select * from part_range_list subpartition(p2sub1);

select * from part_range_list subpartition(p2sub2);

截断子分区：

alter table part_range_list truncate subpartition p2sub2;

截断分区：

alter table part_range_list truncate partition p2;

6.9 合并分区

合并分区是将相邻的分区合并成一个分区，结果分区将采用较高分区的界限。

注意：不能将分区合并到界限较低的分区。

select * from user_part_tables where table_name=‘PART_RANGE1‘;

select * from user_tab_partitions where table_name=‘PART_RANGE1‘;

select * from user_part_key_columns where name=‘PART_RANGE1‘;

select * from part_range1;

select * from part_range1 partition(cus_part1);

select * from part_range1 partition(cus_part2);

select * from part_range1 partition(cus_part3);

合并分区：

alter table part_range1 merge partitions cus_part1,cus_part2 into partition cus_part2;

--alter table part_range1 merge partitions cus_part3,cus_part2 into partition cus_part2;

ORA-14273: 必须首先指定下界分区

--alter table part_range1 merge partitions cus_part2,cus_part3 into partition cus_part2;

ORA-14275: 不能将下界分区作为结果分区重用

6.10 拆分分区

拆分分区将一个分区拆分两个新分区，拆分后原来分区不再存在。

alter table part_range1 split partition cus_part2 at(101) into (partition p1,partition p2);

--验证

select * from user_part_tables where table_name=‘PART_RANGE1‘;

select * from user_tab_partitions where table_name=‘PART_RANGE1‘;

select * from user_part_key_columns where name=‘PART_RANGE1‘;

select * from part_range1 partition(p1);

select * from part_range1 partition(p2);

select * from part_range1 partition(cus_part3);

注意：不能对hash类型的分区进行拆分。

select * from user_part_tables where table_name=‘PART_HASH1‘;

select * from user_tab_partitions where table_name=‘PART_HASH1‘;

select * from user_part_key_columns where name=‘PART_HASH1‘;

select * from part_hash1 partition(p2);

alter table part_hash1 split partition p2 at(4) into (partition p3,partition p4);

--ORA-14255: 未按范围, 列表, 组合范围或组合列表方法对表进行分区

6.11 重命名表分区

select * from user_tab_partitions where table_name=‘PART_RANGE1‘;

alter table part_range1 rename partition cus_part3 to p3;

6.12 分区表查询

--分区表查询

select * from part_range1;

--单分区查询

select * from part_range1 partition (p1);

--跨分区查询

select * from

(select * from part_range1 partition (p1)

union all

select * from part_range1 partition (p2)

union all

select * from part_range1 partition (p3));