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语句。