range,hash,list分区
来源:互联网 发布:和日本人谈恋爱 知乎 编辑:程序博客网 时间:2024/05/17 22:16
A.创建range分区,一般用于日期化处理,range分区可以很好的管理基于日期来分区的数据
创建样本数据表
create table zhangxb (orderid number(10),name varchar2(10),ls_date date);
insert into zhangxb values (1,'ls1',to_date('1981-01-02','yyyy-mm-dd'));
insert into zhangxb values (1,'ls2',to_date('1998-01-03','yyyy-mm-dd'));
insert into zhangxb values (1,'ls3',to_date('1999-01-04','yyyy-mm-dd'));
insert into zhangxb values (1,'ls4',to_date('2000-01-05','yyyy-mm-dd'));
insert into zhangxb values (1,'ls5',to_date('2000-01-06','yyyy-mm-dd'));
insert into zhangxb values (1,'ls6',to_date('2001-01-07','yyyy-mm-dd'));
insert into zhangxb values (1,'ls7',to_date('2001-01-08','yyyy-mm-dd'));
insert into zhangxb values (1,'ls8',to_date('2002-01-09','yyyy-mm-dd'));
insert into zhangxb values (1,'ls9',to_date('2002-01-10','yyyy-mm-dd'));
insert into zhangxb values (1,'ls10',to_date('2011-01-11','yyyy-mm-dd'));
创建range分区表
create table zhangxb_part
partition by range (ls_date)
(
partition zhangxb_part_1999_1 values less than (to_date('1999-01-01','yyyy-mm-dd')) ,
partition zhangxb_part_2000_1 values less than (to_date('2000-01-01','yyyy-mm-dd')) ,
partition zhangxb_part_2001_1 values less than (to_date('2001-01-01','yyyy-mm-dd')) ,
partition zhangxb_part_2002_1 values less than (to_date('2002-01-01','yyyy-mm-dd')) ,
partition zhangxb_part_2003_1 values less than (to_date('2003-01-01','yyyy-mm-dd')) ,
partition other values less than (maxvalue)
)
as select * from zhangxb;
查看分区
select * from zhangxb_part partition (zhangxb_part_1999_1);
select * from zhangxb_part partition (zhangxb_part_2000_1);
select * from zhangxb_part partition (zhangxb_part_2001_1);
select * from zhangxb_part partition (zhangxb_part_2002_1);
select * from zhangxb_part partition (zhangxb_part_2003_1);
select * from zhangxb_part partition (other);
B.创建hash分区,利用hash函数打散某列使数据均匀分布,一般用于均衡I/O,缺点数据不容易管理,哈希分区不能DROP、SPLIT 以及MERGE分区
我们创建了拥有10个分区的哈希分区表“zhangxb_HASH”
LS@LEO> create table zhangxb_hash partition by hash(object_id) partitions 10 as select * from dba_objects;
hash分区所占用的区个数,看每个分区占用的个数都差不多,说明数据还是比较均匀分布的
缺点:hash列上数值不能有太多的重复值,否则会导致数据分布不均匀
select partition_name,count(*) from user_extents where segment_name='zhangxb_HASH' group by partition_name;
PARTITION_NAME COUNT(*)
------------------------------ ----------
SYS_P27 3
SYS_P26 3
SYS_P22 2
SYS_P28 3
SYS_P29 2
SYS_P21 2
SYS_P23 3
SYS_P25 3
SYS_P30 2
SYS_P24 3
select count(*) from zhangxb_hash;
COUNT(*)
----------
9860
C.创建list分区,一般用于数据可枚举,有限个值,可以考虑列表分区,例如国家名字,按州来分区
创建list分区表,我们按国家来分别存放在不同的州,每个州是一个分区
create table zhangxb_list
(
city_id NUMBER(5),
city_name VARCHAR2(30),
city_state VARCHAR2(20),
city_amount NUMBER(10)
)
partition by list (city_name)
(
partition asia VALUES('china','japan'),
partition europe VALUES ('germany','italy'),
partition africa VALUES('libya','brazil'),
partition other VALUES(DEFAULT) --默认分区
);
插入数据
insert into zhangxb_list values(1,'china','asia',100);
insert into zhangxb_list values(2,'germany','europe',101);
insert into zhangxb_list values(3,'libya','africa',102);
insert into zhangxb_list values(4,'zhangxb_city','other',103);
查看数据
LS@LEO> select * from zhangxb_list;
CITY_ID COUNTRY_NAME STATE CITY_AMOUNT
---------- ------------------------------ -------------------- -----------
1 china asia 100
2 germany europe 101
3 libya africa 102
4 zhangxb_city other 103
注:3个分区,没有好坏,只有合适不合适而已,要根据自己数据库的情况和业务分析选择
创建样本数据表
create table zhangxb (orderid number(10),name varchar2(10),ls_date date);
insert into zhangxb values (1,'ls1',to_date('1981-01-02','yyyy-mm-dd'));
insert into zhangxb values (1,'ls2',to_date('1998-01-03','yyyy-mm-dd'));
insert into zhangxb values (1,'ls3',to_date('1999-01-04','yyyy-mm-dd'));
insert into zhangxb values (1,'ls4',to_date('2000-01-05','yyyy-mm-dd'));
insert into zhangxb values (1,'ls5',to_date('2000-01-06','yyyy-mm-dd'));
insert into zhangxb values (1,'ls6',to_date('2001-01-07','yyyy-mm-dd'));
insert into zhangxb values (1,'ls7',to_date('2001-01-08','yyyy-mm-dd'));
insert into zhangxb values (1,'ls8',to_date('2002-01-09','yyyy-mm-dd'));
insert into zhangxb values (1,'ls9',to_date('2002-01-10','yyyy-mm-dd'));
insert into zhangxb values (1,'ls10',to_date('2011-01-11','yyyy-mm-dd'));
创建range分区表
create table zhangxb_part
partition by range (ls_date)
(
partition zhangxb_part_1999_1 values less than (to_date('1999-01-01','yyyy-mm-dd')) ,
partition zhangxb_part_2000_1 values less than (to_date('2000-01-01','yyyy-mm-dd')) ,
partition zhangxb_part_2001_1 values less than (to_date('2001-01-01','yyyy-mm-dd')) ,
partition zhangxb_part_2002_1 values less than (to_date('2002-01-01','yyyy-mm-dd')) ,
partition zhangxb_part_2003_1 values less than (to_date('2003-01-01','yyyy-mm-dd')) ,
partition other values less than (maxvalue)
)
as select * from zhangxb;
查看分区
select * from zhangxb_part partition (zhangxb_part_1999_1);
select * from zhangxb_part partition (zhangxb_part_2000_1);
select * from zhangxb_part partition (zhangxb_part_2001_1);
select * from zhangxb_part partition (zhangxb_part_2002_1);
select * from zhangxb_part partition (zhangxb_part_2003_1);
select * from zhangxb_part partition (other);
B.创建hash分区,利用hash函数打散某列使数据均匀分布,一般用于均衡I/O,缺点数据不容易管理,哈希分区不能DROP、SPLIT 以及MERGE分区
我们创建了拥有10个分区的哈希分区表“zhangxb_HASH”
LS@LEO> create table zhangxb_hash partition by hash(object_id) partitions 10 as select * from dba_objects;
hash分区所占用的区个数,看每个分区占用的个数都差不多,说明数据还是比较均匀分布的
缺点:hash列上数值不能有太多的重复值,否则会导致数据分布不均匀
select partition_name,count(*) from user_extents where segment_name='zhangxb_HASH' group by partition_name;
PARTITION_NAME COUNT(*)
------------------------------ ----------
SYS_P27 3
SYS_P26 3
SYS_P22 2
SYS_P28 3
SYS_P29 2
SYS_P21 2
SYS_P23 3
SYS_P25 3
SYS_P30 2
SYS_P24 3
select count(*) from zhangxb_hash;
COUNT(*)
----------
9860
C.创建list分区,一般用于数据可枚举,有限个值,可以考虑列表分区,例如国家名字,按州来分区
创建list分区表,我们按国家来分别存放在不同的州,每个州是一个分区
create table zhangxb_list
(
city_id NUMBER(5),
city_name VARCHAR2(30),
city_state VARCHAR2(20),
city_amount NUMBER(10)
)
partition by list (city_name)
(
partition asia VALUES('china','japan'),
partition europe VALUES ('germany','italy'),
partition africa VALUES('libya','brazil'),
partition other VALUES(DEFAULT) --默认分区
);
插入数据
insert into zhangxb_list values(1,'china','asia',100);
insert into zhangxb_list values(2,'germany','europe',101);
insert into zhangxb_list values(3,'libya','africa',102);
insert into zhangxb_list values(4,'zhangxb_city','other',103);
查看数据
LS@LEO> select * from zhangxb_list;
CITY_ID COUNTRY_NAME STATE CITY_AMOUNT
---------- ------------------------------ -------------------- -----------
1 china asia 100
2 germany europe 101
3 libya africa 102
4 zhangxb_city other 103
注:3个分区,没有好坏,只有合适不合适而已,要根据自己数据库的情况和业务分析选择
0 0
- range,hash,list分区
- range,list和Hash 分区的性能
- 【oracle】分区表:range分区,list分区,hash分区
- Oracle之range,hash,list分区现实应用及优缺点汇总
- Oracle之range,hash,list分区现实应用及优缺点汇总
- MySql从一窍不通到入门(七)分区策略:HASH/RANGE/LIST/KEY
- oracle的range,hash,list
- ORACLE:分区表range,hash,list
- 分区表及分区索引(5)--创建range-hash组合分区
- 创建range list和hash分区表
- Oracle 11gR2 List-Range分区实验
- 分区表及分区索引(6)--创建range-list组合分区
- oracle 分区,range-list,范围-列表复合分区
- 创建range-hash(范围-哈希)组合分区!
- 修改mysql hash 分区为range 分区,并删除一个分区
- 深入学习分区表及分区索引(5)--创建range-hash组合分区
- 深入学习分区表及分区索引(5)--创建range-hash组合分区(续)
- 创建range-list(范围-列表)组合分区!
- vc中关闭对话框dialog
- KING_UNITY学习之鼠标点击地面控制人物移动
- LeetCode 144:Binary Tree Preorder Traversal
- qpixmap 鼠标移动,局部放大
- 使用python重写上篇文章的内容分析工具
- range,hash,list分区
- php【基础学习七】string操作
- Android中string资源文件使用技巧
- css 文字长时不换行 显示...
- 周记——20151221
- List is a raw type. References to generic type List<E> should be parameterized
- 我希望的Golang的feature
- 总结学习VS2010的dll的创建与调用
- .h、.DLL和.lib关系