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) values (101,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, 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‘); 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‘, partition year16 values less than (to_date(‘2017-1-1‘, 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,status) values (2,sysdate-481,‘inactive‘); insert into part_range_list (product_id,status) values (3,sysdate-451,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‘); 注意:以上添加的分区界限应该高于最后一个分区界限。 给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_cost,status) values (111,to_date(‘2017-03-31‘,‘yyyy-mm-dd‘),1,status) values (112,‘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,status) values (111,status) values (112,‘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,to_date(‘2017-02-21‘,‘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));
(编辑:李大同)
【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!
|