数据库技术学习 https://www.itkc8.com
表分区:
应用场景:如数据量比较大的表,比如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);
数据库技术学习 https://www.itkc8.com