第10部分 oracle11g的表分区

一、分区表

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