表分区(学习笔记)

表分区：

应用场景：如数据量比较大的表，比如2G的表，可以分20块来查询肯定比不分块查询速度快

区间分区：常常用于日期字段的分区

less than 是不包括这（）里的值小于的意思

示例一、创建区间分区

--创建表
CREATE TABLE drawlist(
     draw_dt         DATE       NOT NULL
)
--创建表分区
PARTITION BY RANGE(draw_dt)(
          PARTITION part_1 VALUES LESS THAN (to_date('1/1/2009','dd-mm-yyyy')),
          PARTITION part_2 VALUES LESS THAN (to_date('1/1/2011','dd-mm-yyyy')),
          PARTITION part_3 VALUES LESS THAN(Maxvalue)
);
--表和分区要同时创建，不然无法创建分区

插入数据

--插入数据
INSERT INTO drawlist(draw_dt)VALUES(to_date('2008-12-31','yyyy-mm-dd'));
INSERT INTO drawlist(draw_dt)VALUES(to_date('2009-1-1','yyyy-mm-dd'));
INSERT INTO drawlist(draw_dt)VALUES(to_date('2009-6-6','yyyy-mm-dd'));
INSERT INTO drawlist(draw_dt)VALUES(to_date('2010-12-31','yyyy-mm-dd'));
INSERT INTO drawlist(draw_dt)VALUES(to_date('2009-6-6','yyyy-mm-dd'));
INSERT INTO drawlist(draw_dt)VALUES(to_date('2011-1-1','yyyy-mm-dd'));
INSERT INTO drawlist(draw_dt)VALUES(to_date('2011-5-5','yyyy-mm-dd'));

查询

--查询
SELECT * FROM drawlist;
SELECT * FROM drawlist PARTITION(part_1); --查询分区1
SELECT * FROM drawlist PARTITION(part_2); --查询分区2
SELECT * FROM drawlist PARTITION(part_3); --查询分区3

 

示例二、创建散列分区

散列分区提供了一种在指定数量的分区中均等地划分数据的方法

--创建表
CREATE TABLE hash_table(
             hash_no    INTEGER         NOT NULL
)
--创建散列分区表
PARTITION BY HASH(hash_no)(
          PARTITION part_1,
          PARTITION part_2,
          PARTITION part_3,
          PARTITION part_4
);
--利用数据生成器，生成500条记录
--查询

SELECT * FROM hash_table;
SELECT COUNT(*) FROM hash_table;
SELECT COUNT(*) FROM hash_table PARTITION(part_1);
SELECT COUNT(*) FROM hash_table PARTITION(part_2);
SELECT COUNT(*) FROM hash_table PARTITION(part_3);
SELECT COUNT(*) FROM hash_table PARTITION(part_4);

 列表分区:主要用区代码数据库如邮编，区号之类

示例三、创建列表分区

--创建表
CREATE TABLE area(
       CODE    INTEGER  NOT NULL,
       NAME    VARCHAR2(10)
)
--创建列表分区
PARTITION BY LIST(CODE)(
      PARTITION part_1 VALUES(102200,102202,102203),
      PARTITION part_2 VALUES(164300,164302,164303)
);
--使用数据生成器，插入记录
--查询
SELECT * FROM area;
SELECT * FROM area PARTITION(part_1);
SELECT * FROM area PARTITION(part_2);

示例四、组合分区--区间-散列分区

--区间散列
--创建表
CREATE TABLE range_hash(
          dt_date        DATE              NOT NULL,
          hash_no        INTEGER           NOT NULL
)
--创建区间散列分区
PARTITION BY RANGE(dt_date) SUBPARTITION BY HASH(hash_no)
(
         PARTITION part_1 VALUES LESS THAN(to_date('1/1/2009','dd-mm-yyyy')),
         PARTITION part_2 VALUES LESS THAN(to_date('1/1/2011','dd-mm-yyyy')),
         PARTITION part_3 VALUES LESS THAN(maxvalue)
       
);
--区间散列分区，先按区间分区再按散列分区，这时散列的意思不大，散列仍然要按照区间进行分区

示例五、组合分区--区间-列表分区

--区间列表
--创建表
CREATE TABLE range_list(
           dt_date        DATE              NOT NULL,
           CODE       INTEGER           NOT NULL

)
--创建区间列表分区
PARTITION BY RANGE(dt_date) SUBPARTITION BY LIST(CODE)(
  PARTITION part_1 VALUES LESS THAN(to_date('1/1/2009','dd-mm-yyyy'))(
             SUBPARTITION part1_list1 VALUES(102200),
             SUBPARTITION part1_list2 VALUES(164300)
  ),
   PARTITION part_2 VALUES LESS THAN(to_date('1/1/2011','dd-mm-yyyy'))(
             SUBPARTITION part2_list1 VALUES(102200),
             SUBPARTITION part2_list2 VALUES(164300)
  ),
   PARTITION part_3 VALUES LESS THAN(MAXVALUE)(
             SUBPARTITION part3_list1 VALUES(102200),
             SUBPARTITION part3_list2 VALUES(164300)
  )
);
--插入数据
INSERT INTO range_list(dt_date,code)VALUES(to_date('2008-12-31','yyyy-mm-dd'),102200);
INSERT INTO range_list(dt_date,code)VALUES(to_date('2008-12-31','yyyy-mm-dd'),164300);
INSERT INTO range_list(dt_date,code)VALUES(to_date('2010-12-31','yyyy-mm-dd'),102200);
INSERT INTO range_list(dt_date,code)VALUES(to_date('2010-12-31','yyyy-mm-dd'),164300);
INSERT INTO range_list(dt_date,code)VALUES(to_date('2011-12-31','yyyy-mm-dd'),102200);
INSERT INTO range_list(dt_date,code)VALUES(to_date('2009-12-31','yyyy-mm-dd'),164300);
--查询
SELECT * FROM range_list;
SELECT * FROM RANGE_list PARTITION(part_1);
SELECT * FROM range_list SUBPARTITION  (part1_list1);--查询子分区
SELECT * FROM range_list SUBPARTITION  (part1_list2);

 

高界限的分区不能合并到低界限的分区中

维护分区

-创建表
CREATE TABLE dlist(
       dl_date          DATE         NOT NULL
)
--创建区间分区
PARTITION BY RANGE(dl_date)(
          PARTITION part_1 VALUES LESS THAN (to_date('1/1/2009','dd-mm-yyyy')),
          PARTITION part_2 VALUES LESS THAN (to_date('1/1/2011','dd-mm-yyyy')),
          PARTITION part_3 VALUES LESS THAN(Maxvalue)
);
--插入数据
INSERT INTO dlist(dl_date)VALUES(to_date('2008-12-31','yyyy-mm-dd'));
INSERT INTO dlist(dl_date)VALUES(to_date('2009-1-1','yyyy-mm-dd'));
INSERT INTO dlist(dl_date)VALUES(to_date('2009-6-6','yyyy-mm-dd'));
INSERT INTO dlist(dl_date)VALUES(to_date('2010-12-31','yyyy-mm-dd'));
INSERT INTO dlist(dl_date)VALUES(to_date('2009-6-6','yyyy-mm-dd'));
INSERT INTO dlist(dl_date)VALUES(to_date('2011-1-1','yyyy-mm-dd'));
INSERT INTO dlist(dl_date)VALUES(to_date('2011-5-5','yyyy-mm-dd'));
INSERT INTO dlist(dl_date)VALUES(to_date('2011-12-31','yyyy-mm-dd'));
--查询
SELECT * FROM dlist;
SELECT * FROM dlist PARTITION(part_1);
SELECT * FROM dlist PARTITION(part_2);
SELECT * FROM dlist PARTITION(part_3);

增加分区

-增加分区表中时新增加的分区，必须高于已经有分于的最后一个分区界限
ALTER TABLE dlist ADD PARTITION part_4 VALUES LESS THAN(to_date('1/1/2012','dd-mm-yyyy'));
--要先删除PART_3，maxvalue
--删除分区表
ALTER TABLE dlist DROP PARTITION part_3;
--drop数据一起删除

--插入数据
INSERT INTO dlist(dl_date)VALUES(to_date('2011-5-5','yyyy-mm-dd'));
INSERT INTO dlist(dl_date)VALUES(to_date('2011-12-31','yyyy-mm-dd'));
--查询
SELECT * FROM dlist;
SELECT * FROM dlist PARTITION(part_1);
SELECT * FROM dlist PARTITION(part_2);
SELECT * FROM dlist PARTITION(part_4);

截断分区

--截断分区
ALTER TABLE dlist TRUNCATE PARTITION part_1;
--查询

SELECT * FROM dlist PARTITION(part_1);

合并分区

--合并分区
ALTER TABLE dlist MERGE PARTITIONS part_2,part_4 INTO PARTITION part_4;
--合并后的分区会消失
SELECT * FROM dlist PARTITION(part_2);
--会提示分区不存在
SELECT * FROM dlist PARTITION(part_4);