Oracle基础—表分区

一:表分区的应用场景

  用于管理包含大量数据的表。

二:表分区的优点

  1.提高数据的可以性

  2.减少管理负担

  3.改善语句的性能

三:分区的方式:(区间分区、散列分区、列表分区、组合分区)

  1.区间分区(范围分区 range)

    创建区间分区的语法:

      PARTITION BY RANGE (列名)    --range表示分区的方式

      (

        partition 分区表名 values less than (值)

       [ tablespace 表空间]

      )  

        例:按照时间创建表分区。

技术分享
--创建表时建立分区表
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);
技术分享

  2.散列分区(hash):对于那些无法有效的划分范围的表,可以使用hash分区

    散列分区语法:

    partition by hash()

    (

      partition 分区表名 tablespace 表空间名

    )

  例:

 
技术分享
--创建表
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);
技术分享

  3.列表分区(list):可以将数据按照列的值分类

    列表分区语法:

    partition by list()

    (

      partition 分区表名 values(指定值1,指定值2……)

    )

    [tablespace 表空间名]

  例:

技术分享
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);
技术分享

  

  4.组合分区:就是前三个分区的组合

    优势:使分区表更加灵活

  (1).区间-散列组合分区语法:

    partition by range(列1) subpartition by hash(列2)

    (

      partition 分区表名 values less than(值)

     tablespace 表空间名;

    )

  (2).区间-列表组合分区语法:    

    partition by range(列1) subpartition by list(列2)

    (

      partition 分区表名 values less than(值)

     tablespace 表空间名(

      subpartition 自分区名 values(列表指定值1……)

    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.合并分区:   --高界限的分区不能合并到低界限的分区中

    语法:

    ALTER TABLE 表名 MERGE PARTITIONS 分区表1,分区表2 INTO  PARTITION 分区表2

--将分区表p1合并到分区表p2
ALTER TABLE drawlist MERGE PARTITIONS p1,p2 INTO PARTITION p2

 

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。