首页 > 代码库 > Oracle基础 表分区
Oracle基础 表分区
Oracle基础 表分区
一、表分区
（一）表分区的分类
1、范围分区（range）
2、散列分区（hash)
3、列表分区（list）
4、复合分区：范围-哈希(range-hash)、范围-列表（range-list）
（二）范围分区：
范围分区是应用范围比较广的分区方式，它是以列的值得范围来作为分区的划分条件，将记录存放到列值所在的range分区中。
例：按照时间创建表分区。
--创建表时建立分区表 CREATE TABLE drawlist( drawname VARCHAR2(20), drawtime DATE NOT NULL ) PARTITION BY range(drawtime)( --创建表分区，以drawtime为分区范围 PARTITION part_1 VALUES LESS THAN (to_date(‘2010-1-1‘,‘yyyy-mm-dd‘)), --定义2010-1-1以前的数据保存的分区，不包括2010-1-1 PARTITION part_2 VALUES LESS THAN (to_date(‘2011-1-1‘,‘yyyy-mm-dd‘)), --定义2011-1-1以前的数据分区 PARTITION part_3 VALUES LESS THAN (to_date(‘2012-1-1‘,‘yyyy-mm-dd‘)), PARTITION part_4 VALUES LESS THAN (maxvalue) --其他的值保存的分区 ) --插入数据，系统会自动将数据保存到对应的分区表中。 INSERT INTO drawlist SELECT ‘aaa‘,to_date(‘2009-10-20‘,‘yyyy-mm-dd‘) FROM dual UNION SELECT ‘bbb‘,to_date(‘2009-11-20‘,‘yyyy-mm-dd‘) FROM dual UNION SELECT ‘ccc‘,to_date(‘2009-12-20‘,‘yyyy-mm-dd‘) FROM dual UNION SELECT ‘ddd‘,to_date(‘2010-10-20‘,‘yyyy-mm-dd‘) FROM dual UNION SELECT ‘eee‘,to_date(‘2010-10-20‘,‘yyyy-mm-dd‘) FROM dual UNION SELECT ‘fff‘,to_date(‘2011-10-20‘,‘yyyy-mm-dd‘) FROM dual UNION SELECT ‘ggg‘,to_date(‘2012-10-20‘,‘yyyy-mm-dd‘) FROM dual --查询分区表 SELECT * FROM drawlist PARTITION (part_1); SELECT * FROM drawlist PARTITION (part_2); SELECT * FROM drawlist PARTITION (part_3); SELECT * FROM drawlist PARTITION (part_4);
（三）散列分区：
对于那些无法有效的划分范围的表，可以使用hash分区。hash分区会将数据平均的分配到指定的几个分区表中，由于数据被平均分配到不同的分区，减少查询时对数据块的竞争，这样对于提高性能还是会有一些帮助，列所在的分区是一句分区列的hash值自动分配，因此不能控制，也不知道哪条记录被放到哪个分区中，hash分区也可以支持多个依赖咧。建立散列分区最好使用2的冥次个分区表。比如2,4,8,16等。
例：
--创建表 CREATE TABLE drawlist( draw_id NUMBER, draw_name VARCHAR2(20) ) --创建散列分区 PARTITION BY HASH(draw_name) ( PARTITION p1 TABLESPACE USERS, PARTITION p2 TABLESPACE USERS, PARTITION p3 TABLESPACE USERS, PARTITION p4 TABLESPACE USERS ); --生成1000行数据 --查询各个分区表中的数据。 SELECT COUNT(*) FROM drawlist PARTITION (p1); SELECT COUNT(*) FROM drawlist PARTITION (p2); SELECT COUNT(*) FROM drawlist PARTITION (p3); SELECT COUNT(*) FROM drawlist PARTITION (p4);
（四）列表分区：
列表分区也需要指定列的值，其分区必须明确指定，该分区列只能有一个，不能像range或hash分区那样同时指定多个列作为分区依赖列，但它的单个分区对应值可以是多个。使用列表分区，必须确定分区列可能存在的值，一旦插入的列值不在分区范围内，则插入/更新就会失败，因此通常建议使用list分区时，要创建一个default分区存储哪些不在指定范围内的记录，类似range分区中的maxvalue分区。
CREATE TABLE area ( CODE NUMBER, NAME VARCHAR2(20) ) --创建列表分区 PARTITION BY LIST(CODE) ( PARTITION p1 VALUES(10,20,30), --指定当code值为10,20,30为第一分区 PARTITION p2 VALUES(40,50,60), --指定当code值为40,50,60为第二分区 PARTITION p_other VALUES(DEFAULT) --其他值为第三分区 ) select * from area PARTITION (p1); select * from area PARTITION (p2); select * from area PARTITION (p_other);
（五）复合分区
如果某表按照某列分区之后仍然较大，或者有一些其他的需求，还可以通过分区内再建子分区的方式将分区再分区，即组合分区的方式。
组合分区分为：
1、范围-哈希(range-hash)
语法：
PARTITION BY RANGE (列1） SUBPARTITION BY HASH(列2）（
PARTITION 分区名 VALUES LESS THAN (值)
TABLESPACE 表空间
)
2、范围-列表（range-list）
语法：
PARTITION BY RANGE (列1） SUBPARTITION BY LIST(列2）（
PARTITION 分区名 VALUES LESS THAN (值)
TABLESPACE 表空间（
SUBPARTITION 子分区名 VALUES（列表指定值...） TABLESPACE 表空间
）
)
二、分区维护
1、增加分区
语法：
ALTER TBALE 表名 ADD PARTITION 分区表名 VALUES LESS THAN(值）
例如：
--增加区间分区 ALTER TABLE drawlist ADD PARTITION p3 VALUES LESS THEN to_date(‘2013-1-1‘,‘yyyy-mm-dd‘) TABLESPACE USERS; --注意：插入的区间数据值不能小于原有的分区表的值。如果包含了maxvalues，必须删除原有分区表
2、删除分区
语法：
ALTER TABLE 表名 DROP PARTITION 分区表名
例：
--删除区间分区 ALTER TABLE drawlist DROP PARTITION p3 --删除分区表后，分区表中的数据也会一同删除
3、截断分区
删除当前分区中的数据，但是它并不会影响其他分区。
语法：
ALTER TABLE 表名 TRUNCATE PARTITION 分区表名
例：
--阶段分区表P3，清除分区表中的数据 ALTER TABLE drawlist TRUNCATE PARTITION p3
4、合并分区
将两个分区表中的数据合并到一个分区，被合并的分区将不复存在。注意：高界线的分区不能合并到低界线的分区中。比如将小于2009年的数据合并到小于2010年的分区中，反过来则不行。
语法：
ALTER TABLE 表名 MERGE PARTITIONS 分区表1，分区表2 INTO PARTITION 分区表2
例：
--将分区表p1合并到分区表p2 ALTER TABLE drawlist MERGE PARTITIONS p1,p2 INTO PARTITION p2
Oracle基础 表分区