分区表的总结

分区表

通过本章节的学习，您可以学到以下几个问题

1、 了解分区表的概念

2、 清楚分区表的用途

3、 了解分区表的分类

4、 了解分区表的操作

 

在我们使用普通表的过程中会有数据量特别大的表，当一个表的数据超过过2000万条或占用2G空间时，建议建立分区表

 

分区表的分类：

Range(范围)分区

Hash(哈希)分区

List(列表)分区

以及组合分区：Range-Hash,Range-List

 

分区的优点：

 

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

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

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

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

 

执行概要

分区功能可以提高许多应用程序的可管理性、性能与可用性。通过分区功能，可以将表、索引和索引组织表进一步细分为段，从而能够更精确地管理和访问这些数据库对象。Orac#提供了种类繁多的分区方案以满足每种业务要求。而且，因为sql语句中分区是完全透明的，所以该功能几乎可应用于任何应用程序。

 

分区功能的优势

分区功能通过改善可管理性、性能和可用性，从而为各式应用程序带来了极大的好处。通常，分区可以使某些查询以及维护操作的性能大大提高。此外,分区还可以极大简化常见的管理任务。通过分区,数据库设计人员和管理员能够解决前沿应用程序带来的一些难题。分区是构建千兆字节数据系统或超高可用性系统的关键工具。

 

分区功能的基本知识

分区功能能够将表、索引或索引组织表进一步细分为段。这些数据库对象的段叫做分区。每个分区有自己的名称，还可以选择自己的存储特性。

从数据库管理员的角度来看，一个分区后的对象具有多个段，这些段既可进行集体管理，也可单独管理。这就使数据库管理员在管理分区后的对象时有相当大的灵活性。但是，从应用程序的角度来看，分区后的表与非分区表完全相同，使#S#D#命令访问分区后的表时，无需任何修改,表的分区是通过“分区键”来实现的，分区键指的是一些列，这些列决定了某一行所在的分区。Orac#数据#1#提供了六项技术用于对表进行分区：

#范围分区

每个分区都由一个分区键值范围指定（对于一个以日期列作为分区键的表，“2005年1月”分区包含分区键值为从“2005年1月1日”到“2005年1月31日”的行）。

 

范围分区表示例：

 

假设有一个企业的销售数据比较大，可以作一个范围分区表

 

创建四个表空间，为分区表所用，应该把分区放在不同的物理设备上，性能更好，可我的测试环境是同一个物理设备

 

SQL> create tablespace ts_xiaos2009q1 datafile

'/oradata/dbnms/ts_xiaos2009q1.dbf' size 50M;

Tablespace created.

 

SQL> create tablespace ts_xiaos2009q2

datafile '/oradata/dbnms/ts_xiaos2009q2.dbf' size 50M;

Tablespace created.

 

SQL> create tablespace ts_xiaos2009q3 datafile

'/oradata/dbnms/ts_xiaos2009q3.dbf' size 50M;

Tablespace created.

 

SQL> create tablespace ts_xiaos2009q4 datafile

'/oradata/dbnms/ts_xiaos2009q4.dbf' size 50M;

Tablespace created.

 

创建表

SQL> CREATE TABLE dyx_part_test1

  2  (id NUMBER,

  3  name varchar2(20),

  4  shuliang number,

  5  danjia number,

  6  xiaos_date DATE NOT NULL )

  7  PARTITION BY RANGE (xiaos_date)

  8  (PARTITION xiaoss2009_q1

  9  VALUES LESS THAN (TO_DATE('2009-04-01','YYYY-MM-DD'))

 10  TABLESPACE ts_xiaos2009q1,

 11  PARTITION xiaoss2009_q2

 12  VALUES LESS THAN (TO_DATE('2009-07-01','YYYY-MM-DD'))

 13  TABLESPACE ts_xiaos2009q2,

 14  PARTITION xiaoss2009_q3

 15  VALUES LESS THAN (TO_DATE('2009-10-01','YYYY-MM-DD'))

 16  TABLESPACE ts_xiaos2009q3,

 17  PARTITION xiaoss2009_q4

 18  VALUES LESS THAN (TO_DATE('2010-01-01','YYYY-MM-DD'))

 19  TABLESPACE ts_xiaos2009q4);

 

Table created.

 

插入数据

 

SQL> insert into dyx_part_test1 values

(1,'可口可乐',100,3.5,to_date('2009-03-05','yyyy-mm-dd'));

1 row created.

 

SQL>insert into dyx_part_test1 values

(2,'肯德基',100,10.5,to_date('2009-06-05','yyyy-mm-dd'));

1 row created.

 

SQL>

SQL> insert into dyx_part_test1 values

(3,'正林3A500g',100,15,to_date('2009-09-05','yyyy-mm-dd'));

1 row created.

 

SQL>

SQL> insert into dyx_part_test1 values

(4,'冰红茶',100,3,to_date('2009-11-05','yyyy-mm-dd'));

1 row created.

 

SQL>

SQL> insert into dyx_part_test1 values

(5,'橙',100,3.5,to_date('2010-04-05','yyyy-mm-dd'));

insert into dyx_part_test1 values

(5,'橙',100,3.5,to_date('2010-04-05','yyyy-mm-dd'))

            *

ERROR at line 1:

ORA-14400: inserted partition key does not map to any partition

 

注：如果不能符全分区的数据不会插入，会报ORA-14400错误

 

查看：

SQL> select * from DYX_PART_Test1 partition(XIAOSS2009_Q3);

 

        ID NAME                   SHULIANG     DANJIA XIAOS_DATE

---------- -------------------- ---------- ---------- ------------

         3 正林3A500g                  100         15 05-SEP-09

 

#列表分区

每个分区都由一个分区键值列表指定（对于一个地区列作为分区键的表，“北美”分区可能包含值“加拿大”“美国”和“墨西哥”）。

 

示例如下：

 

SQL> create table dyx_part_test2(

  2  id number(4),

  3  name varchar2(30),

  4  loca varchar2(30))

  5  partition by list (loca)

  6  (partition p1 values ('北京') tablespace ts_xiaos2009q1,

  7  partition p2 values ('上海','天津','重庆') tablespace ts_xiaos2009q2,

  8  partition p3 values ('广东','福建') tablespace ts_xiaos2009q3,

  9  partition p0 values (default) tablespace users

 10  );

 

Table created.

 

SQL> insert into dyx_part_test2 values (1,'戴永新','甘肃');

1 row created.

 

SQL> insert into dyx_part_test2 values (2,'云中子','北京');

1 row created.

 

SQL> insert into dyx_part_test2 values (3,'广成子','天津');

1 row created.

 

SQL> insert into dyx_part_test2 values (4,'南极仙翁','河北');

1 row created.

 

SQL> insert into dyx_part_test2 values (5,'太已真人','上海');

1 row created.

 

SQL> insert into dyx_part_test2 values (6,'惧刘孙','广东');

1 row created.

 

SQL> insert into dyx_part_test2 values (7,'元始天尊','重庆');

1 row created.

 

SQL> insert into dyx_part_test2 values (8,'南级战神','北京');

1 row created.

 

SQL> insert into dyx_part_test2 values (9,'五大战神','福建');

1 row created.

 

SQL> select * from dyx_part_test2;

 

        ID NAME                           LOCA

---------- ------------------------------ ------------------------------

         2 云中子                         北京

         8 南级战神                       北京

         3 广成子                         天津

         5 太已真人                       上海

         7 元始天尊                       重庆

         6 惧刘孙                         广东

         9 五大战神                       福建

         1 戴永新                         甘肃

         4 南极仙翁                       河北

 

9 rows selected.

 

SQL> select * from DYX_PART_Test2 partition(p2);

 

        ID NAME                           LOCA

---------- ------------------------------ ------------------------------

         3 广成子                         天津

         5 太已真人                       上海

         7 元始天尊                       重庆

        

SQL> select * from DYX_PART_Test2 partition(p0);

 

        ID NAME                           LOCA

---------- ------------------------------ ------------------------------

         1 戴永新                         甘肃

         4 南极仙翁                       河北

         3 广成子                         天津市

 

 

#散列分区

将散列算法用于分区键来确定指定行所在的分区，通过指定分区编号来均匀分布数据的一种分区类型，因为通过在I/O设备上进行散列分区，

使得这些分区大小一致。

 

示例：

 

create table dyx_part_test3

(id number primary key,

class_id number(8) not null,

name varchar2(300),

finished_date date

)

partition by hash(id)

(

partition p01 tablespace ts_xiaos2009q1,

partition p02 tablespace ts_xiaos2009q2,

partition p03 tablespace ts_xiaos2009q3

);

 

insert into dyx_part_test3 values

(3,12, '上等的虎皮',to_date('2009-05-30','yyyy-mm-dd'));

insert into dyx_part_test3 values

(1,13, '虎皮',to_date('2009-05-30','yyyy-mm-dd'));

insert into dyx_part_test3 values

(200,15, '中等的虎皮',to_date('2009-05-30','yyyy-mm-dd'));

insert into dyx_part_test3 values

(230,19, '猫虎皮',to_date('2009-05-30','yyyy-mm-dd'));

 

SQL> select * from dyx_part_test3;

 

        ID   CLASS_ID NAME                 FINISHED_DAT

---------- ---------- -------------------- ------------

       200         15 中等的虎皮           30-MAY-09

         3         12 上等的虎皮           30-MAY-09

         1         13 虎皮                 30-MAY-09

       230         19 猫虎皮               30-MAY-09

      

      

SQL> select * from dyx_part_test3;

 

        ID   CLASS_ID NAME                 FINISHED_DAT

---------- ---------- -------------------- ------------

       200         15 中等的虎皮           30-MAY-09

         3         12 上等的虎皮           30-MAY-09

         1         13 虎皮                 30-MAY-09

       230         19 猫虎皮               30-MAY-09

 

SQL> select * from DYX_PART_Test3 partition(p02);

 

        ID   CLASS_ID NAME                 FINISHED_DAT

---------- ---------- -------------------- ------------

         3         12 上等的虎皮           30-MAY-09

         1         13 虎皮                 30-MAY-09

 

SQL> select * from DYX_PART_Test3 partition(p01);

 

        ID   CLASS_ID NAME                 FINISHED_DAT

---------- ---------- -------------------- ------------

       200         15 中等的虎皮           30-MAY-09

 

SQL> select * from DYX_PART_Test3 partition(p03);

 

        ID   CLASS_ID NAME                 FINISHED_DAT

---------- ---------- -------------------- ------------

       230         19 猫虎皮               30-MAY-09

 

#组合分区

 

1、范围散列(hash)分区

范围和散列分区技术的组合，通过该组合，首先对表进行范围分区，然后针对每个单独的范围分区再使用散列分区技术进一步细分。

索引组织表只能进行范围分区。

 

示例：

SQL> create table dyx_part_test4(

  2  id number,

  3  name varchar2(30),

  4  finished_date date)

  5  partition by range (finished_date)

  6  subpartition by hash (id)

  7  subpartitions 2

  8  (partition p1 values less than (to_date('20090501','YYYYMMDD')) tablespace ts_xiaos2009q1,

  9  partition p2 values less than (to_date('20091001','YYYYMMDD')) tablespace ts_xiaos2009q2,

 10  partition e3 values less than (maxvalue) tablespace ts_xiaos2009q3);

 

Table created.

 

SQL> insert into dyx_part_test4 values

(100,'红色的黑土',to_date('20090603','yyyymmdd'));

 

1 row created.

 

SQL> insert into dyx_part_test4 values

(101,'分区表测试',to_date('20090403','yyyymmdd'));

 

1 row created.

 

SQL> insert into dyx_part_test4 values

(105,'分区表规划',to_date('20090703','yyyymmdd'));

 

1 row created.

 

SQL> insert into dyx_part_test4 values

(1044,'stream实施',to_date('20091103','yyyymmdd'));

 

1 row created.

 

SQL> insert into dyx_part_test4 values

(1400,'oracle设计',to_date('20090603','yyyymmdd'));

 

1 row created.

 

SQL> insert into dyx_part_test4 values

(1070,'数据库规划',to_date('20090303','yyyymmdd'));

 

1 row created.

 

SQL>

SQL> commit;

 

Commit complete.

 

SQL> select * from dyx_part_test4;

 

        ID NAME                 FINISHED_DAT

---------- -------------------- ------------

       101 分区表测试           03-APR-09

      1070 数据库规划           03-MAR-09

       100 红色的黑土           03-JUN-09

       105 分区表规划           03-JUL-09

      1400 oracle设计           03-JUN-09

      1044 stream实施           03-NOV-09

 

6 rows selected.

 

SQL> select * from DYX_PART_Test4 partition(p2);

 

        ID NAME                 FINISHED_DAT

---------- -------------------- ------------

       100 红色的黑土           03-JUN-09

       105 分区表规划           03-JUL-09

      1400 oracle设计           03-JUN-09

 

SQL> select * from DYX_PART_Test4 partition(p1);

 

        ID NAME                 FINISHED_DAT

---------- -------------------- ------------

       101 分区表测试           03-APR-09

      1070 数据库规划           03-MAR-09

 

SQL> select * from DYX_PART_Test4 partition(e3);

 

        ID NAME                 FINISHED_DAT

---------- -------------------- ------------

      1044 stream实施           03-NOV-09

     

 

 

2、范围列表分区

范围和列表分区技术的组合，通过该组合，首先对表进行范围分区，然后针对每个单独的范围分区再使用列表分区技术进一步细分。

 

示例：

CREATE TABLE dyx_part_test5(

id NUMBER(6),

name VARCHAR2(20),

quyu VARCHAR2(30),

cretetime NUMBER(9,2))

PARTITION BY RANGE (cretetime)

SUBPARTITION BY LIST (quyu)

SUBPARTITION TEMPLATE

(SUBPARTITION east VALUES ('CHINA', 'JAPAN', 'INDIA', 'THAILAND'),

SUBPARTITION west VALUES ('AMERICA', 'GERMANY', 'ITALY', 'SWITZERLAND'),

SUBPARTITION other VALUES (DEFAULT))

(PARTITION p1 VALUES LESS THAN (1000),

PARTITION p2 VALUES LESS THAN (2500),

PARTITION p3 VALUES LESS THAN (MAXVALUE));

 

insert into dyx_part_test5 values (1,'戴永新','china',300);

 

insert into dyx_part_test5 values (2,'我是我','GERMANY',2650);

insert into dyx_part_test5 values (75,'oracle','JAPAN',6952);

insert into dyx_part_test5 values (34,'IBM','SWITZERLAND',2100);

insert into dyx_part_test5 values (43,'SUN','CHINA',320);

insert into dyx_part_test5 values (876,'DELL','AMERICA',986);

insert into dyx_part_test5 values (866,'SYSBASE','INDIA',6352);

 

 

SQL> insert into dyx_part_test4 values

(100,'红色的黑土',to_date('20090603','yyyymmdd'));

 

1 row created.

 

SQL> insert into dyx_part_test4 values

(101,'分区表测试',to_date('20090403','yyyymmdd'));

 

1 row created.

 

SQL> insert into dyx_part_test4 values

(105,'分区表规划',to_date('20090703','yyyymmdd'));

 

1 row created.

 

SQL> insert into dyx_part_test4 values

(1044,'stream实施',to_date('20091103','yyyymmdd'));

 

1 row created.

 

SQL> insert into dyx_part_test4 values

(1400,'oracle设计',to_date('20090603','yyyymmdd'));

 

1 row created.

 

SQL> insert into dyx_part_test4 values

(1070,'数据库规划',to_date('20090303','yyyymmdd'));

 

1 row created.

 

SQL>

SQL> commit;

 

Commit complete.

 

SQL> select * from dyx_part_test4;

 

        ID NAME                 FINISHED_DAT

---------- -------------------- ------------

       101 分区表测试           03-APR-09

      1070 数据库规划           03-MAR-09

       100 红色的黑土           03-JUN-09

       105 分区表规划           03-JUL-09

      1400 oracle设计           03-JUN-09

      1044 stream实施           03-NOV-09

 

6 rows selected.

 

SQL> select * from DYX_PART_Test5 partition(p1);

 

        ID NAME                 FINISHED_DAT

---------- -------------------- ------------

       100 红色的黑土           03-JUN-09

       105 分区表规划           03-JUL-09

      1400 oracle设计           03-JUN-09

 

SQL> select * from DYX_PART_Test4 partition(p1);

 

        ID NAME                 FINISHED_DAT

---------- -------------------- ------------

       101 分区表测试           03-APR-09

      1070 数据库规划           03-MAR-09

 

SQL> select * from DYX_PART_Test4 partition(e3);

 

        ID NAME                 FINISHED_DAT

---------- -------------------- ------------

      1044 stream实施           03-NOV-09