选中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;
得到结果: