Oracle_高级功能(6) 分区

偏执的太偏执、 2021-11-22 16:48 304阅读 0赞

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));

转载于:https://www.cnblogs.com/BradMiller/p/9279827.html