首页 > 代码库 > 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基础 表分区