首页 > 代码库 > 表分区(学习笔记)
表分区(学习笔记)
表分区：
应用场景：如数据量比较大的表，比如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); --查询分区1SELECT * FROM drawlist PARTITION(part_2); --查询分区2SELECT * FROM drawlist PARTITION(part_3); --查询分区3
示例二、创建散列分区
--创建表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 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);
表分区(学习笔记)
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。