一、分区表
(1)允许用户将一个表分成多个分区
(2)用户可以执行查询,只访问表中的特定分区
(3)将不同的分区存储在不同的磁盘,提高访问性能和安全性
(4)可以独立地备份和恢复每个分区
目的:对于大数据量的表,比如一个表数据超过上亿条的身份证信息,如果查询一个人的信息,从上亿条中进行检索会非常慢,此时就需要用到分区表进行查询,会极大提高查询速度。
二、传统的表分区的类型(范围分区、散列分区、列表分区、复合分区)
1、范围分区:RANGE
-
范围分区-以表中的一个列或一组列的值的范围分区
-
范围分区的语法:
PARTITION BY RANGE (column_name)
(
PARTITION part1 VALUE LESS THAN(range1),
PARTITION part2 VALUE LESS THAN(range2),
...
[PARTITION partN VALUE LESS THAN(MAXVALUE)]
);
(1)创建分区表
create table sales( product_id varchar2(5),sales_count number(10,2) ) partition by range(sales_count) ( partition p1 values less than (1000),//——p1<1000 partition p2 values less than (2000),//——1000=<p2<2000 partition p3 values less than (3000) //——2000=<p3<3000 );
(2)查看分区表:
SQL>select * from user_tab_partitions u where table_name='SALES';
(3)插入数据,进行测试;插入普通表和分区表的方式是一样的
SQL>insert into sales values('1',600);
SQL>insert into sales values('2',1000);
SQL>insert into sales values('3',2300);
SQL>insert into sales values('4',6000);——超过范围值,就无法插入
SQL> select * from sales partition(p1);——查看
(4)增加一个分区:
SQL> alter table sales add partition p4 values less than(maxvalue);//无穷大
2、散列分区(补足范围分区的弊端)——HASH分区
-
允许用户对不具有逻辑范围的数据进行分区
-
通过在分区键上执行HASH函数决定存储的分区
-
将数据平均地分布到不同的分区
语法:
PARTITION BY HASH (column_name)
PARTITIONS number_of_partitions;
或
PARTITION BY HASH (column_name)
( PARTITION part1 [TABLESPACE tbs1],
PARTITION part2 [TABLESPACE tbs2],
...
PARTITION partN [TABLESPACE tbsN]
);
(1)创建散列分区表
create table my_emp( empno number,ename varchar2(10) ) partition by hash(empno) ( partition p1,partition p2 )
SQL> select * from user_tab_partitions u where table_name='MY_EMP';
查看分区中的值:SQL>select * from my_emp partition(p1);
3、列表分区-LIST
-
允许用户将不相关的数据组织在一起
语法:
PARTITION BY LIST (column_name)
(
PARTITION part1 VALUES (values_list1),
PARTITION part2 VALUES (values_list2),
...
PARTITION partN VALUES (DEFAULT)
);
(1)创建列表分区:
create table personCity( id number,name varchar2(10),city varchar2(10) ) partition by list(city) ( partition east values('kaifeng','shangqiu'), partition western values('luoyang'), partition south values('xuchang'), partition north values('xinxiang') )
(2)插入数据
insert into personcity values(1,'A','kaifeng'); insert into personcity values(2,'B','shangqiu'); insert into personcity values(3,'C','luoyang');
(3)查看数据分区表
select * from personcity partition(eastern);
4、复合分区
-
范围分区与散列分区,或范围分区与列表分区的组合
-
语法并不难,最重要的是知道如何使用
语法:
PARTITION BY RANGE (column_name1)
SUBPARTITION BY HASH (column_name2)
SUBPARTITIONS number_of_partitions
(
PARTITION part1 VALUE LESS THAN(range1),
PARTITION part2 VALUE LESS THAN(range2),
...
PARTITION partN VALUE LESS THAN(MAXVALUE)
);
例子:
create table student ( sno number,sname varchar2(10) ) partition by range(sno) subpartition by hash(sname) subpartitions 4 ( partition p1 values less than(1000), partition p2 values less than(2000), partition p3 values less than(maxvalue) )
通过常用的方法,只能看到range 3个大分区,但是每个分区中的4个子分区是无法查看的
可以查询子分区的方法,在EM中查看
三、11g新增的表分区的类型——引用分区
-
引用分区:基于由外键引用的父表的分区的方法,它依赖已有的父表子表的关系,子表通过外键关联到父表,进而继承了父表的分区方式而不需自己创建,子表还继承了父表的维护操作。
1,主表是范围分区,子表是引用分区
2,主表是列表分区,子表是引用分区
3,主表是散列分区,子表是引用分区
例子:
1、 创建父表(以主表为范围分区进行展示)
create table student( stu_id number primary key,stu_name varchar2(10),grade varchar2(10) ) partition by range(stu_id) ( partition par_stu1 values less than (1000), partition par_stu2 values less than (2000), partition par_stu3 values less than (maxvalue) )
2、创建字表(创建外键的即为子表)
create table score( id number primary key,stu_id number not null,cource_name varchar2(20), score number, constraint fk_score foreign key(stu_id) references student(stu_id) ) partition by reference(fk_score); ---表示引用分区,引用外键(fk_score)建立的分区
外键是参照了student的stu_id这一列,而在student的stu_id这一列已经有了3个range分区了。而字表score进行了一个引用分区,引用的是fk_score这个外键,对应的是student表中的stu_id列。因此,子表同样会创建和父表一样的分区(即引用分区)
主表(父表)student
子表:score
查询:SQL> select * from user_tab_partitions u where u.table_name in('STUDENT','SCORE');
虽然HIGH_VALUE值,score中是空的,但是是和student的high_value对应的,是继承关系(根据下面的例子)
例子:通过例子可以看出子表的引用分区和父表的分区是一致的
SQL>inset into student values(600,'AA','grade 1'); SQL>inset into student values(1200,'BB','grade 2'); SQL>inset into score values(1,600,'Math','80'); SQL>inset into score values(2,1200,'English','90');
四、11g新增的表分区的类型——间隔分区
-
间隔分区:可以完全自动地根据间隔阈值创建范围分区,它是范围分区的扩展 。
在数据仓库中有广泛的应用
create table sale_detail ( sale_detail_id number,product_id number,quantity number,sale_date date ) partition by range(sale_date) interval(numtoyminterval(1,'MONTH')) ————interval自动增长 ( partition p_201006 values less than(to_date('20100601','yyyymmdd')) );
interval——自动增长
select * from user_tab_partitions u where u.table_name in('SALE_DETAIL');
插入新数据:
SQL>insert into sale_detail values(1,100,20,to_date('20100121','yyyymmdd'));此句小于6.1号,会插入到分区p_201006中 SQL>insert into sale_detail values(2,100,30,to_date('20100621','yyyymmdd')); SQL>insert into sale_detail values(3,100,30,to_date('20100721','yyyymmdd'));
查看:
SQL>select * from sale_detail partition(p_201006);
再次查看分区:发现自动创建了2个分区,且范围为7.1号,8.1号。原因是定义自动增长时,按照一个月一个月的增加。
如果突然增加到11月,则只会顺延增加到 12月份的分区
SQL>insert into sale_detail values(3,100,30,to_date('20101121','yyyymmdd'));
五、11g新增的表分区的类型——基于虚拟列的分区
-
基于虚拟列的分区:把分区建立在某个虚拟列上,即建立在函数或表达式的计算结果上,来完成某种任务
1、创建表:
create table sale( sale_id number primary key,product_id number,price number, quantity number,sale_date date, total_price as(price * quantity)virtual————通过vitual指定虚拟列 ) partition by range(total_price) ( partition p_1000 values less than(1000), partition p_2000 values less than(2000), partition p_3000 values less than(maxvalue) )
2、插入数据
SQL>insert into sale(sale_id,product_id,price,quantity,sale_date)values(1,100,15,20,sysdate);
3、查看,因为15*20=300,所以计入了p_1000分区表
六、11g新增的表分区的类型——系统分区
-
系统分区:不指定分区列,由ORACLE来完成分区的控制和管理,它没有了范围分区或列表分区的界限。
1、创建表
SQL>create table person( id number,name varchar2(10) ) partition by system( partition p1,partition p2,partition p3 )
2、测试数据
SQL> insert into person partition(p1) values(1,'A'); SQL> insert into person partition(p2) values(2,'B'); SQL> insert into person partition(p3) values(3,'C');
3、查看分区
SQL> select * from person partition(P1); SQL> select * from person partition(P2); SQL> select * from person;
七、操作已分区的表
-
在已分区的表中插入数据与操作普通表完全相同(貌似系统分区除外,需要制定分区),Oracle会自动将数据保存到对应的分区查询、修改和删除分区表时可以显式指定要操作的分区
-
查询、修改和删除分区表时可以显式指定要操作的分区
INSERT INTO SALES3 VALUES (‘P001’, ’02-3月-2001', 2000); INSERT INTO SALES3 VALUES (‘P002’, ’10-5月-2001', 2508); INSERT INTO SALES3 VALUES (‘P003’, ’05-7月-2001', 780); INSERT INTO SALES3 VALUES (‘P004’, ’12-9月-2001', 1080); SELECT * FROM SALES3 PARTITION (P3); DELETE FROM SALES3 PARTITION (P2);
八、分区维护操作
-
分区维护操作修改已分区表的分区。
-
分区维护的类型:
计划事件 - 定期删除最旧的分区
非计划事件 - 解决应用程序或系统问题
-
分区维护操作有:
1、添加分区——在最后一个分区之后添加新分区
SQL> ALTER TABLE SALES ADD PARTITION P4 VALUES LESS THAN (4000);
2、删除分区——删除一个指定的分区,分区的数据也随之删除
SQL> ALTER TABLE SALES DROP PARTITION P4;
3、截断分区——删除指定分区中的所有记录
SQL> ALTER TABLE SALES TRUNCATE PARTITION P3;
4、合并分区——将范围分区或复合分区的两个相邻分区连接起来
SQL> ALTER TABLE SALES MERGE PARTITIONS S1, S2 INTO PARTITION S2;
5、拆分分区——将一个大分区中的记录拆分到两个分区中
SQL> ALTER TABLE SALES SPLIT PARTITION P2 AT (1500)INTO (PARTITION P21, PARTITION P22);
文章评论