目录:
1、数值范围分区(数值范围之分)
2、时间范围分区(时间范围之分)
3、列表分区(男女之分)
4、哈希分区(分区数量为2的次幂,2、4、8、16……)
5、范围列表分区(时间+男女)
6、范围哈希分区
直接复制出来在Oralce执行执行就清楚了
--数值范围分区
create table pt_range_test1(
pid number(10),
pname varchar2(30)
) partition by range(pid)(
partition p1 values less than(1000),
partition p2 values less than(2000),
partition p3 values less than(maxvalue)
) enable row movement;
--删除表结构
drop table pt_range_test1 purge
drop table pt_range_test1
--删除表内容
truncate table pt_range_test1
delete from pt_range_test1
--插入
insert into pt_range_test1 (pid, pname) values (1, '瑶瑶');
insert into pt_range_test1 (pid, pname) values (1500, '倩倩');
insert into pt_range_test1 (pid, pname) values (null, '优优');
commit;
--查询分区
select * from user_tab_partitions where table_name= 'PT_RANGE_TEST1'
--分区查询所有数据
select 'P1' 分区名, t.* from pt_range_test1 partition (p1) t union all
select 'P2' 分区名, t.* from pt_range_test1 partition (p2) t union all
select 'P3' 分区名, t.* from pt_range_test1 partition (p3) t
/*
1 P1 1 瑶瑶
2 P2 1500 倩倩
3 P3 优优
*/
select * from pt_range_test1 partition (p1) union all
select * from pt_range_test1 partition (p2) union all
select * from pt_range_test1 partition (p3)
--不分区查询所有数据(有公司设置权限约定在测试环境下分区表一定要使用分区进行查询,减少损耗)
select * from pt_range_test1
------------------------------------------------------------------------------------
--时间范围分区
create table pt_range_test2(
pid number(10),
pname varchar2(30),
create_date date
) partition by range(create_date)(
partition p1 values less than(to_date('2020-01-01', 'YYYY-MM-DD')),
partition p2 values less than(to_date('2021-01-01', 'YYYY-MM-DD')),
partition p3 values less than(maxvalue)
) enable row movement;
--删表
drop table pt_range_test2
--插入
insert into pt_range_test2 (pid, pname, create_date) values (1, '瑶瑶',to_date('2019-01-01', 'YYYY-MM-DD'));
insert into pt_range_test2 (pid, pname, create_date) values (1500, '倩倩',to_date('2020-01-01', 'YYYY-MM-DD'));
insert into pt_range_test2 (pid, pname, create_date) values (null, '优优',to_date('2023-01-01', 'YYYY-MM-DD'));
commit;
--查询分区(注意:表名需要大写)
select * from user_tab_partitions where table_name= 'PT_RANGE_TEST2'
--分区查询所有数据
select 'P1' 分区名, t.* from pt_range_test2 partition (p1) t union all
select 'P2' 分区名, t.* from pt_range_test2 partition (p2) t union all
select 'P3' 分区名, t.* from pt_range_test2 partition (p3) t
/*
1 P1 1 瑶瑶 2019/1/1
2 P2 1500 倩倩 2020/1/1
3 P3 优优 2023/1/1
*/
select * from pt_range_test2 partition (p1) union all
select * from pt_range_test2 partition (p2) union all
select * from pt_range_test2 partition (p3)
--不分区查询所有数据
select * from pt_range_test2
------------------------------------------------------------------------------
--列表分区
create table pt_list_test(
pid number(10),
pname varchar2(30),
sex varchar2(10)
) partition by list(sex)(
partition p1 values ('MAN', '男') ,
partition p2 values ('WOMAN', '女') ,
partition p3 values (default)
) enable row movement;
--删表
drop table pt_list_test
--插入
insert into pt_list_test (pid, pname, sex) values (1, '瑶瑶', '男');
insert into pt_list_test (pid, pname, sex) values (2, '倩倩', 'WOMAN');
insert into pt_list_test (pid, pname, sex) values (3, '优优', '任妖');
commit;
--使用分区查询所有数据
select 'P1' 分区名, t.* from pt_list_test partition (p1) t union all
select 'P2' 分区名, t.* from pt_list_test partition (p2) t union all
select 'P3' 分区名, t.* from pt_list_test partition (p3) t
/*
1 P1 1 瑶瑶 男
2 P2 2 倩倩 WOMAN
3 P3 3 优优 任妖
*/
-----------------------------------------------------------------------
--哈希分区(散列分区)(建议分区数量为2的N次幂,使得分区数量分布更加均衡)
create table pt_hash_test2(
pid number(10),
pname varchar2(30)
) partition by hash(pid)
partitions 4;
--插入
insert into pt_hash_test2 (pid, pname) values (1, '瑶瑶');
insert into pt_hash_test2 (pid, pname) values (1500, '倩倩');
insert into pt_hash_test2 (pid, pname) values (null, '优优');
insert into pt_hash_test2 (pid, pname) values (6666, '桂优');
commit;
--删表
drop table pt_hash_test2
--目前只知道非分区方式查询所有数据
select * from pt_hash_test2
-----------------------------------------------------------------------------
--组合分区(情况1:范围+列表分区)
create table pt_range_list_test(
pid number(10),
pname varchar2(30),
sex varchar2(10),
create_date date
) partition by range(create_date)
subpartition by list(sex)(
partition p1 values less than(to_date('2020-01-01', 'YYYY-MM-DD'))
(
subpartition sub1p1 values('MAN'),
subpartition sub2p1 values('WOMAN'),
subpartition sub3p1 values(default)
),
partition p2 values less than(to_date('2021-01-01', 'YYYY-MM-DD'))
(
subpartition sub1p2 values('MAN'),
subpartition sub2p2 values('WOMAN'),
subpartition sub3p2 values(default)
),
partition p3 values less than(maxvalue)
(
subpartition sub1p3 values('MAN'),
subpartition sub2p3 values('WOMAN'),
subpartition sub3p3 values(default)
)
) enable row movement;
--删表
drop table pt_range_list_test
---------------------------------------------------------------------------
--组合分区(情况2:范围+哈希分区)
create table pt_range_hash_test(
pid number(10),
pname varchar2(30),
sex varchar2(10),
create_date date
) partition by range(create_date)
subpartition by hash(pid) subpartitions 4
(
partition p1 values less than(to_date('2020-01-01', 'YYYY-MM-DD')),
partition p2 values less than(to_date('2021-01-01', 'YYYY-MM-DD')),
partition p3 values less than(to_date('2022-01-01', 'YYYY-MM-DD')),
partition p4 values less than(maxvalue)
) enable row movement;
--删表
drop table pt_range_hash_test