12.分析函数与分区表——Oracle数据库学习日记

分析函数与分区表

1、分析函数

# 统计分区中各组行数
count() over(partition by ... order by...)
列出各部门的人数以员工姓名、部门编号及工资
select ename,sal,deptno,count(*) over(partition by deptno)
from emp;

# 统计分区中各组总和
sum() over(partition by ... order by ...)
列出各部门员工姓名、工资、部门编号和各部门工资总和，各部门内按工资排序
select ename,sal,deptno,
        sum(*) over(partition by deptno order by sal)
 from emp;

max() over(partition by ... order by ...)
min() over(partition by ... order by ...)
avg() over(partition by ... order by ...)

#rank() over()跳跃排序
#dense_rank() over()连续排序
rank() over(partition by ... order by ...)
dense_rank() over(partition by ... order by ...)
列出全公司的员工信息，按照各部门员工工资排序，并列出序号
select empno,ename,job,hiredate,sal,deptno,
    rank() over(partition by deptno order by sal desc) r
  from emp;

#lead() over()前一个
#lag() over()后一个
lag() over(partition by .. order by ...)
lead() over(partition by ... order by ...)
计算个人工资与比自己高一位/低一位工资的差额
select ename,job,sal,deptno,
    lead(sal,1,0) over(partition by deptno order by sal)        # 比自己工资高的部门前一个
    lag(sal,1,0) over(partition by deptno order by sal)     # 比自己工资低的部门后一个
 from emp;

#取第一个值，最后一个值
first_value() over(partition by ... order by ...)
last_value() over(partition by ... order by...)

# ratio_to_report(a) over(partition by b):求按照b分组后a的值在所属分组中总值的占比，a的值必须为数值或数值型字段
当前行的值在分组后组内总值占比
select ename,job,SAL,deptno,
    ratio_to_report(sal) over(partition by job)
  from emp;

2、Partition table 分区表

2.1概念

分区表，逻辑上仍然是一张完整的表，只是将表中的数据在物理上存放到多个空间（也可以是不同磁盘上），这样查询数据时，不至于每次都扫描整张表。
当表中的数据量不断增大，查询数据的速度就会变慢，应用程序的性能就会下降，这是就应该考虑对表进行分区。

2.2作用

oracle的表分区功能通过改善可管理性、性能和可用性，从而为各式应用程序带来了极大的好处。
通常，分区可以使某些查询以及维护操作的性能大大提高。
此外，分区还可以极大简化常见的管理任务，分区是构建千兆字节数据系统或超高可用性系统的关键工具。

2.3优缺点

优点
a.改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索速度
b.增强可用性：如果表的某个分区出现故障，表在其他分区的数据仍然可用
c.维护方便：如果表的某个分区出现故障，需要修复数据，只修复该分区即可
d.均衡I/O：可以把不同的分区映射到磁盘以平衡I/O，改善整个系统性能

缺点：
分区表相关，已经存在的表没有方法可以直接转化为分区表。不过oracle提供了在线重定义表的功能。

2.4类型

2.4.1范围分区

范围分区将数据基于范围映射到每个分区，这个范围是你在创建分区时指定的分区键决定的。这种分区方式是最为常用的，并且分区键经常采用日期

# 按照值的范围进行分区
create table customer
	(customer_id number(10) primary key,
	first_name varchar2(30) not null,
	last_name varchar2(30) not null)
partition by range(customer_id)
(partition P1 values less than (1000),
partition P2 values less than (2000));

#按照日期范围进行分区
CREATE TABLE t
(id number,
create_date date)
partition by range(create_date) interval(numtoyminterval(1,'MONTH'))
(
partition p201303 values less than (to_date('2013-03-01','yyyy-mm-dd')),
partition p201304 values less than (to_date('2013-04-01','yyyy-mm-dd')),
partition p201305 values less than (to_date('2013-05-01','yyyy-mm-dd'))
);

2.4.2列表分区

该分区的特点是某列的值只有几个，基于这样的特点我们可以采用列表分区

create table p_test1
	(cola number(10),
	sex varchar2(10))
partition by list(sex)
(partition P1 values ('man'),
partition P2 values ('woman'),
partition p3 values (null));

2.4.3散列分区

这类分区是在列值上使用散列算法，以确定将行放入哪个分区中。当列的值没有合适的条件时，建议使用散列分区。

CREATE TABLE HASH_TABLE
(
 COL NUMBER(8),
 INF VARCHAR2(100)
)
PARTITION BY HASH (COL)
( PARTITION PART01 TABLESPACE HASH_TS01,
 PARTITION PART02 TABLESPACE HASH_TS02,
 PARTITION PART03 TABLESPACE HASH_TS03)

2.4.4间隔分区

分区使Oracle高效的处理海量数据称为可能，但使用Interval Partition也有一些限制：

• 分区列的类型只能是数值或日期

CREATE TABLE t
(id number,
create_date date)
partition by range(create_date) interval(numtoyminterval(1,'MONTH'))
(
partition p201303 values less than (to_date('2013-03-01','yyyy-mm-dd')),
partition p201304 values less than (to_date('2013-04-01','yyyy-mm-dd')),
partition p201305 values less than (to_date('2013-05-01','yyyy-mm-dd'))
);

2.5维护

2.5.1添加分区

-- range partitioned table
ALTER TABLE range_example ADD PARTITIONpart04 VALUES LESS THAN
(TO_DATE('2008-10-1 00:00:00','yyyy-mm-ddhh24:mi:ss'));

--list partitioned table
ALTER TABLE list_example ADD PARTITIONpart04 VALUES ('TE');

--Adding Values for a List Partition
ALTER TABLE list_example MODIFY PARTITION part04 ADD VALUES('MIS');

--Dropping Values from a List Partition
ALTER TABLE list_example MODIFY PARTITION part04 DROP VALUES('MIS');

--hash partitioned table
ALTER TABLE hash_example ADD PARTITIONpart03;

2.5.2删除分区

ALTER TABLE ... DROP PARTITION part_name;

2.5.3合并分区

ALTER TABLE part_table
MERGE PARTITIONS part01_1,part01_2
    INTO PARTITION part01 UPDATE INDEXES;

2.5.4分割分区

alter table p_table
split partition P_MAX
    at (TO_DATE('2004-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS'))
    into (partition P2003,partition P_MAX) update indexes;