Oracle基础—表分区

时间：2021-11-07 21:30:23

一：表分区的应用场景

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

二：表分区的优点

　　1.提高数据的可以性

　　2.减少管理负担

　　3.改善语句的性能

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

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

　　　　创建区间分区的语法：

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

　　　　　　(

　　　　　　  partition 分区表名 values less than (值)

　　　　　　 [ tablespace 表空间]

　　　　　　)　　

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

Oracle基础—表分区
--创建表时建立分区表

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);
Oracle基础—表分区

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

　　　　散列分区语法：

　　　　partition by hash()

　　　　(

　　　　  partition 分区表名 tablespace 表空间名

　　　　)

　　例：

Oracle基础—表分区
--创建表

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);
Oracle基础—表分区

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

　　　　列表分区语法：

　　　　partition by list()

　　　　(

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

　　　　)

　　　　[tablespace 表空间名]

　　例：

Oracle基础—表分区
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);
Oracle基础—表分区

　　

　　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

相关文章