Oracle Partition 分区详细总结

最新推荐文章于 2024-06-03 23:12:41 发布

wounler

最新推荐文章于 2024-06-03 23:12:41 发布

阅读量1w

点赞数 5

文章标签： oracle sql

本文链接：https://blog.csdn.net/wounler/article/details/120438261

版权

选中sql得表名，右键查看，可以观察到tab页最后一项有个“分区”的字样。增加分区相当于在sql中增加过滤条件。类似partition by函数

group by是分组函数，partition by是分区函数（像sum()等是聚合函数），注意区分。

表空间如下所示

partition by list (PROD_NUM)
(
partition P1 values ('121080005', '121080006', '121080004', '121080007', '010120')
tablespace NRCS_LMS_TBS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 8M
next 8K
minextents 1
maxextents unlimited
),
partition P_OTHER values (default)
tablespace NRCS_LMS_TBS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 8M
next 8K
minextents 1
maxextents unlimited
)
);

查看分区得方法：

--显示数据库所有分区表的信息：
select * from DBA_PART_TABLES

--显示当前用户可访问的所有分区表信息:
select * from ALL_PART_TABLES

--显示当前用户所有分区表的信息：
select * from USER_PART_TABLES

--显示表分区信息显示数据库所有分区表的详细分区信息：
select * from DBA_TAB_PARTITIONS

--显示当前用户可访问的所有分区表的详细分区信息：
select * from ALL_TAB_PARTITIONS

--显示当前用户所有分区表的详细分区信息：
select * from USER_TAB_PARTITIONS

根据性别分区，性别分为‘男’，‘女’，‘default’,default代表其他

可以增加一个default分区，来存储列表以外的数据。

若插入的列值不在指定的list分区范围内就会报错，为避免这种情况，使用list分区时可创建一个default分区。default 分区用来存储那些不在指定范围内的记录，类似于range分区的maxvalue分区。

partition by list (sex) --根据性别分区
(
partition l1 values('男')，
partition l2 values('女')，
partition l3 values(default)
);

分区相关sql

--查询分区
select * from person partition (p2);
select * from person1 partition (l2);
select * from person2 partition (SYS_P548);
select * from person3 partition (h2);
select * from person4 subpartition (p7_sp2);
select * from person5 subpartition (p11_sp5);

--删除表分区
--alter table 表名 drop partition 分区名;
alter table person drop partition p2;

--增加分区(ADD)：
1、如果list分区有default或者range分区有maxvalue，则不能进行add partition操作
2、add partition的值必须大于所有分区的值。
3、若列表分区表中已经建立了default分区，则不能再增加分区。只能先删除default分区，再增加新分区。删除default分区前可以用交换分区进行备份。
alter table p_list add partition p_3 values('20121113');

List分区也需要指定列的值，其分区值必须明确指定，该分区列只能有一个，不能像range或者hash分区那样同时指定多个列做为分区依赖列，但它的单个分区对应值可以是多个。
　　在分区时必须确定分区列可能存在的值，一旦插入的列值不在分区范围内，则插入/更新就会失败，因此通常建议使用list分区时，要创建一个default分区存储那些不在指定范围内的记录，类似range分区中的maxvalue分区。

在根据某字段，如城市代码分区时，可以指定default，把非分区规则的数据，全部放到这个default分区。
如：

create table custaddr
(
id varchar2(15) not null,
areacode varchar2(4)
)
partition by list (areacode)
( partition t_list025 values ('025'),
partition t_list372 values ('372') ,
partition t_list510 values ('510'),
partition p_other values (default)
)

partition by的用法，需要结合over函数使用，放到from之前

查询成绩表每科前3名：

select * from( select 学生ID,科目,score,ROW_NUMBER() over (partition by 科目 order by score) newcolumn from dbo.成绩表) t where newcolumn<=3

group by是分组函数，partition by是分区函数（像sum()等是聚合函数），注意区分。

1、over函数的写法：

over（partition by cno order by degree ）

先对cno 中相同的进行分区，在cno 中相同的情况下对degree 进行排序

over()函数写法over(partition by expr2 order by expr3)，根据expr2对结果进行分区，在各分区内按照expr3进行排序；

over函数不能单独使用，需要与row_number()，rank()和dense_rank，lag()和lead(),sum()等配合使用。

分区partiton by 与 group by的区别
group by会将结果集按照指定字段进行聚合，结果集会缩减，在统计部门人数，平均工资时会用到；
partition by会对结果集按照指定字段分层排列，结果集不会缩减，如将公司所有人按照部门进行分区，会发现结果集中同一部门的人会连续排列。

--查询产品表中相同产品并且针对版本号进行排序

select row_number() over(partition by t.prod_num order by t.prod_vrsn_num) ，t.* from prod_info t;

如果要查询表的所有数据，要给表增加别名，否则会报 ORA-0093:missing expression

2、分区函数Partition By与rank()的用法“对比”分区函数Partition By与row_number()的用法
例：查询每名课程的第一名的成绩

（1）使用rank()

SELECT   *
FROM   (select sno,cno,degree,
   rank()over(partition by cno order by degree desc) mm
   from score)
where mm = 1;

得到结果：

（2）使用row_number()

SELECT *
FROM (select sno,cno,degree,
row_number()over(partition by cno order by degree desc) mm
from score)
where mm = 1;

得到结果：

（3）rank()与row_number()的区别
由以上的例子得出，在求第一名成绩的时候，不能用row_number()，因为如果同班有两个并列第一，row_number()只返回一个结果。

3、分区函数Partition By与rank()的用法“对比”分区函数Partition By与dense_rank()的用法
例：查询课程号为‘3-245’的成绩与排名

（1）使用rank()

SELECT *
FROM (select sno,cno,degree,
rank()over(partition by cno order by degree desc) mm
from score)
where cno = '3-245'

得到结果：

（2）使用dense_rank()

SELECT *
FROM (select sno,cno,degree,
dense_rank()over(partition by cno order by degree desc) mm
from score)
where cno = '3-245'

得到结果：

（3）rank()与dense_rank()的区别
由以上的例子得出，rank()和dense_rank()都可以将并列第一名的都查找出来；但rank()是跳跃排序，有两个第一名时接下来是第三名；而dense_rank()是非跳跃排序，有两个第一名时接下来是第二名。

partition by关键字是分析性函数的一部分，它和聚合函数（如group by）不同的地方在于它能返回一个分组中的多条记录，而聚合函数一般只有一条反映统计值的记录，

partition by用于给结果集分组，如果没有指定那么它把整个结果集作为一个分组。

partition by 与group by不同之处在于前者返回的是分组里的每一条数据，并且可以对分组数据进行排序操作。后者只能返回聚合之后的组的数据统计值的记录。

Demo

数据库表结构 学生成绩表 UserGrade

Id int Checked 主键Id
Name varchar(50) Checked 学生名
Course varchar(50) Checked 课程名
Score int Checked 分数

01、把每个人学生的成绩按照升序排名（思路:根据学生姓名分组根据每个人成绩排序）

sql语句

select *,ROW_NUMBER() over( partition by Name order by Score )排名
from UserGrade

查询结果

Id Name Course Score 排名
1004 李四数学 60 1
1005 李四语文 80 2
1001 李四英语 100 3
1007 王五数学 30 1
1006 王五语文 50 2
1003 王五英语 50 3
1008 张三英语 60 1
1000 张三语文 80 2
1002 张三数学 90 3

02、把每个学科的成绩分别进行排名（思路:根据学科分组根据成绩排序）

sql语句

select *,ROW_NUMBER() over( partition by Course order by Score )排名
from UserGrade

查询结果

Id Name Course Score 排名
1002 张三数学 90 1
1004 李四数学 60 2
1007 王五数学 30 3
1001 李四英语 100 1
1008 张三英语 60 2
1003 王五英语 50 3
1000 张三语文 80 1
1005 李四语文 80 2
1006 王五语文 50 3