表分区

1.         什么是表分区

对于数据量很大的表，Oracle引入表分区的机制，这也是大型数据库系统的主要特点之一。表分区就是把一张表的数据按某种分区标准存储在不同位置，不同磁盘（磁盘阵列）。但是对于操作表的DML 语句来说分区是透明的，不需要考虑的。

 

2.         表分区的优势

查询效率方面：用户可以执行查询，只访问表中的特定分区，提高查询效率。例如一个拥有大量数据的具有日期字段表，在没有分区的情况下查询数据需要扫描整个表的数据，或者扫描大部分数据。如果对这个表数据按日期进行分区，那么按日期的查询效率就会有很大提高。如果查询指定日期范围，则数据库只会到对应的分区检索数据，而不会扫描所有。

 

安全方面：将不同的分区存储在不同的磁盘驱动器（磁盘阵列），可以增加数据的吞吐量，如果没有分区则表数据通常分布在一个磁盘上，分区以后可以把表数据分布在多个磁盘驱动器上，一个驱动器查找数据总是比不上几个磁盘驱动器同时查找数据。另外不同磁盘的分区好可以提高访问性能和安全性，如果一个磁盘驱动器出现介质故障无法读取，最多失去表的部分数据，不会影响其它磁盘分区的数据。

管理方面：将不同的分区存储在不同的磁盘（驱动器），可以独立地备份和恢复每个分区。没必要备份火夫所有数据，提高管理的灵活性和效率。

 

3.         如何进行表分区

表分区必须在表创建时候进行，Oracle表分区类型主要有以下几种：

分区方法

范围分区

散列分区

列表分区

复合分区

范围分区：

以表中的一个列或一组列的值的范围分区，范围分区的分区标准字段需要是能够比较大小的字段，如数字，日期等。

 

范围分区的语法：

CREATE TABLE tabNam (

…

)

PARTITION BY RANGE (column_name)

(

  PARTITION part1 VALUE LESS THAN(range1),

  PARTITION part2 VALUE LESS THAN(range2),

  ...

  [PARTITION partN VALUE LESS THAN(MAXVALUE)]

);

 

案例：

      对SALES2表创建成分区表，分区标准字段SALES_DATE， 2003-01-01 之前的数据放在p1分区， 2003-01-01 到 2004-01-01 年的数据在P2分区， 2004-01-01 年以后的数据在P3分区。

CREATE TABLE SALES2 (

PRODUCT_ID VARCHAR2(5),

SALES_DATE DATE NOT NULL,

SALES_COST NUMBER(10))

PARTITION BY RANGE (SALES_DATE)

(

  PARTITION P1 VALUES LESS THAN (DATE ‘ 2003-01-01 ’),

  PARTITION P2 VALUES LESS THAN (DATE ‘ 2004-01-01 ’),

  PARTITION P3 VALUES LESS THAN (MAXVALUE)

);

 

如果分区标准字段是不能比较大小的字符数据应该如何分区呢？这时要考虑散列分区。通过在分区键上执行HASH函数决定存储的分区，将数据平均地分布到不同的分区，平均比较重要，因为如果是范围分区可能会出现局部数据臃肿的情况，如上面范围分区，可能出项2004年以后数据非常多的情况，这样导致P3分区数据非常多，这样对查询不利。

 

散列分区语法：

CREATE TABLE tabName (

…

)

PARTITION BY HASH (column_name)

PARTITIONS number_of_partitions;

或

CREATE TABLE tabName (

…

)

PARTITION BY HASH (column_name)

( PARTITION part1 [TABLESPACE tbs1],

  PARTITION part2 [TABLESPACE tbs2],

  ...

  PARTITION partN [TABLESPACE tbsN]);

从语法可以看出我们在进行分区时可以进一步指定表空间，这样其实是对磁盘阵列的优化。当然所有的表分区定义时都可以指定表分区。

案例：

CREATE TABLE Employee

(

    Employee_ID varchar2 (5),

    Employee_Name varchar2(20),

    Department varchar2 (10)

)

PARTITION BY HASH (Department)

(

    Partition D1,

    Partition D2,

    Partition D3

); 

 

列表分区：

允许用户将不相关的数据组织在一起，适合那种分区标准字段值基数很小的情况（基数小就是字段的不同值的个数很少，可以一一枚举）。

列表分区的语法：

CREATE TABLE tabName (

…

)

PARTITION BY LIST (column_name)

(

  PARTITION part1 VALUES (values_list1),

  PARTITION part2 VALUES (values_list2),

  ...

  PARTITION partN VALUES (DEFAULT)

);

案例：

CREATE TABLE Employee

(

    Emp_ID number (4),

    Emp_Name varchar2 (14),

    Emp_Address varchar2 (15)

)

PARTITION BY LIST (Emp_Address)

(

    Partition north values (‘芝加哥'),

    Partition west values (‘旧金山’, ‘洛杉矶'),

    Partition south values (‘亚特兰大’, ‘达拉斯’, ‘休斯顿'),

    Partition east values (‘纽约’, ‘波斯顿')

);

上面语句根据地址字段分了north、west、south、east  四个区，如果插入数据地址是‘亚特兰大’, ‘达拉斯’, ‘休斯顿任意一个则该数据存放在south分区。

 

复合分区：

范围分区与散列分区或列表分区的组合，可以综合两种分区的优势。复合分区有父分区和子分区。

复合分区的语法：

CREATE TABLE tabName (

…

)

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 SALES

(

    PRODUCT_ID VARCHAR2 (5),

    SALES_DATE DATE NOT NULL,

    SALES_COST NUMBER (10)

)

PARTITION BY RANGE (SALES_DATE)

SUBPARTITION BY HASH (PRODUCT_ID)

SUBPARTITIONS 5

(

    PARTITION S1 VALUES LESS THAN (TO_DATE(‘01/4月/2001',

    'DD/MON/YYYY')),

    PARTITION S2 VALUES LESS THAN (TO_DATE(‘01/7月/2001',

    'DD/MON/YYYY')),

    PARTITION S3 VALUES LESS THAN (TO_DATE(‘01/9月/2001',

    'DD/MON/YYYY')),

    PARTITION S4 VALUES LESS THAN (MAXVALUE)

);

上例SALES表按范围分了S1—S4四个父分区，每个父分区下又分了5个子分区，因此一共20个分区。

 

任务：

Ø         对于一个已经存在的非分区表，因为数据量越来越大，影响了查询效率分区，因此决定对这个表改成分区表，该怎么办？以 scott 的 emp 表为例 按工资sal列进行分区，写出分区过程中用到的SQL语句。