当表越来越大时,可以考虑使用分区表,分区表一般用于大型数据库
普通表是单表单段,分区表是单表多段,每个段称为一个分区。分区表可以把不同段放在不同的表空间中。
访问数据时一般会在某个分区中找数据。不会对表的所有分区进行查找【例如计费信息按月分区,当查12月的分区时不会从1-11月的分区找数据。】
分区可以存储在不同的磁盘,提高访问性能和安全性
可以独立地备份和恢复每个分区
1 分区方法
范围分区 散列分区 列表分区 复合分区
1.1 范围分区
以表中的一个列或一组列的值的范围分区
用于分区的字段可以是数值型、日期型、字符型
例如:移动通话记录能够按照月份来分区
优点:便于管理
范围分区的语法:
PARTITION BY RANGE (column_name)
(
PARTITION part1 VALUES LESS THAN(range1),
PARTITION part2 VALUES LESS THAN(range2),
...
[PARTITION partN VALUES LESS THAN(MAXVALUE)]
);
eg1:
SQL> CREATE TABLE Sales
( Product_ID varchar2 (5), Sales_Cost number (10)
)
PARTITION BY RANGE (Sales_Cost) //根据 Sales_Cost 创建分区
(
PARTITION P1 VALUES LESS THAN (1000), //包含销售成本低于1000
的所有产品的值
PARTITION P2 VALUES LESS THAN (2000),
PARTITION P3 VALUES LESS THAN (3000) //p1 p2 p3 是分区的名称
);
eg2:
SQL> 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)
);
1.2 散列分区[hash分区]
允许用户对不具有逻辑范围的数据进行分区
例如:数据分布不均匀,1月份10条记录,2月份1千万记录。这时使用范围分区没有意义.
Oracle利用自己的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]);
eg1:
sql> create table employee
(
employee_id varchar2(5),
employee_name varchar2(20),
department varchar2(10)
)
partition by hash(department) //在表employee上创建分区键department
(
partition D1, //D1,D2,D3是分区的名字,创建了三个分区
partition D2,
partition D3,
);
)
2 表分区的类型
2.1 列表分区
允许用户将不相关的数据组织在一起
列中的值是有限的若干个,例如:部门、城市
列表分区的语法:
PARTITION BY LIST (column_name)
(
PARTITION part1 VALUES (values_list1),
PARTITION part2 VALUES (values_list2),
...
PARTITION partN VALUES (DEFAULT)
);
SQL> CREATE TABLE Employee1
(
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是分区的名称
2.2 复合分区
范围分区与散列分区或列表分区的组合,range+hash、range+list
复合分区的语法:
先根据范围对数据进行分区,然后再这些分区内创建散列子分区。
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)
);
eg:
SQL> CREATE TABLE SALES
(
PRODUCT_ID VARCHAR2 (5), SALES_DATE DATE NOT NULL,
SALES_COST NUMBER (10)
)
PARTITION BY RANGE (SALES_DATE) //在表的 Sales_Date列中创建范围分区
SUBPARTITION BY HASH (PRODUCT_ID) //在表的 Product_ID列创建散列子分区
SUBPARTITIONS 5 //在每个范围分区中创建 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)
);
s1,s2,s3,s4 是创建的四个范围分区的名称
先按照Sales_Date进行范围分区,共创建4个分区s1,s2,s3,s4;再在每个分区中分别创建5个Hash子分区,共4*5=20个分区
3 操纵已分区的表
分区表的方便之处在于,所有的select与dml操作不需要指定分区名,只要知道表名就可以。Oracle会自动地判断需要的数据在那个分区中。
INSERT INTO SALES VALUES ('P001', 2000);
INSERT INTO SALES VALUES ('P002', 2508);
INSERT INTO SALES VALUES ('P003', 780);
INSERT INTO SALES VALUES ('P004', 1080);
查询、修改和删除分区表时可以显式指定要操作的分区,不过一般这样做没有什么意义。
SELECT * FROM SALES3 PARTITION (P3);
DELETE FROM SALES3 PARTITION (P2);
4. 分区维护操作
分区维护操作修改已分区表的分区。
分区维护的类型:
计划事件 - 定期删除最旧的分区,如定期删除通话记录中最旧的记录
非计划事件 - 最初分区的不够合理,比如,范围分区时把分区弄得太大,我们需要拆分一下。或者分区太小,需要合并多个分区。
分区维护操作有:
添加分区
删除分区
截断分区
合并分区
拆分分区
创建好的分区
SQL> CREATE TABLE Sales
( Product_ID varchar2 (5), Sales_Cost number (10)
)
PARTITION BY RANGE (Sales_Cost) //根据 Sales_Cost 创建分区
(
PARTITION P1 VALUES LESS THAN (1000), //包含销售成本低于1000
的所有产品的值
PARTITION P2 VALUES LESS THAN (2000),
PARTITION P3 VALUES LESS THAN (3000) //p1 p2 p3 是分区的名称
);
4.1 维护分区
添加分区 – 在最后一个分区之后添加新分区
SQL> ALTER TABLE SALES
ADD PARTITION P4 VALUES LESS THAN (4000);
删除分区 – 删除一个指定的分区,分区的数据也随之删除
SQL> ALTER TABLE SALES DROP PARTITION P4;
截断分区 – 删除指定分区中的所有记录
SQL> ALTER TABLE SALES TRUNCATE PARTITION P3;
合并分区 - 将范围分区或复合分区的两个相邻分区连接起来
SQL> ALTER TABLE SALES
MERGE PARTITIONS P1, P2 INTO PARTITION P12;
拆分分区 - 将一个大分区中的记录拆分到两个分区中
SQL> ALTER TABLE SALES SPLIT PARTITION P12 AT (1500)
INTO (PARTITION P1, PARTITION P2);
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/23754390/viewspace-670169/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/23754390/viewspace-670169/