oracle查询优化改写

川长思鸟来 2022-01-07 07:01 275阅读 0赞

单表查询

给查询结果排序

操作多张表

插入、更新、删除

使用字符串

使用数字

日期运算

日期操作

范围处理

> 读 “oracle查询优化改写” 一书笔记。 作者：师庆栋；罗炳森

--------------------

### 单表查询 ###

1：将空值转换为实际值。

关键词：coalesce 如果name为空，展示age值，age还为空，展示默认值。

SELECT coalesce(name, age, ‘默认值’) FROM stu；
    等价于
    SELECT  nvl( nvl(name,age),'默认值' )  FROM stu

2：为列取别名 AS 或者空格

3：where条件中需要引用取别名的列，必须嵌套一层

4：字符串拼接 使用 || 或者 concat()函数

5：select 语句中使用条件逻辑

case
        when	then
        when	then
        else
    end

6：返回限制的行数 where rownum>=3

7：查询语句中 select、rownum、order by 三个关键词的执行书序一次为：select、rownum、order by

### 给查询结果排序 ###

1：排序关键之 order by 列名 ASC（升序,默认为ASC）/ DESC（降序）

也可将列名换成列的序号， eg：1代表 sel1列。

select sel1,sel2,sel3 from emp order by 1;

2：多列排序，函数排序

3：TRANSLATE 关键字

语法TRANSLATE(expr,from\_string,to\_string) . from\_string与to\_string 以字符为单位，对应字符，一 一替换。若to\_string 为空，则返回空；若from\_string对应位置在to\_string 中没有替换为空。

4：按数字和字母混合字符串中的字母排序。可以使用translate 关键词将数字、空格替换掉。

5：处理排序空值。oracle默认排序空值在最后，若想把空值放最前面，可使用 NULLS FIRST。最后面 使用 NULLS LAST。 该关键词放在order by 烈面后面。

### 操作多张表 ###

1：合并数据集UNION ALL：不会除重、UNION：除重（一般和唯一列一起使用）

2：IN、EXISTIS 和 INNER JOIN。 三个PLAN中，join 利用Hash join（哈希链接） 其他两种使用hash join semi（哈希半链接）

3：多表链接

<table> 
 <tbody> 
 <tr> 
 <td>&nbsp;</td> 
 <td> 特点 </td> 
 <td> 书写方式 </td> 
 </tr> 
 <tr> 
 <td> INNER JOIN </td> 
 <td> 返回两张表相匹配的数据 </td> 
 <td> select * from A inner join B on A.no=B.no; select * from A,B where A.no=B.no; </td> 
 </tr> 
 <tr> 
 <td> LEFT JOIN </td> 
 <td> 左表为主，左表返回所有数据，右表返回相匹配数据 </td> 
 <td> select * from A left join B on A.no=B.no; select * from A,B where A.no=B.no(+); </td> 
 </tr> 
 <tr> 
 <td> RIGHT JOIN </td> 
 <td> 右表为主，右表返回所有数据，左表返回相匹配数据 </td> 
 <td> select * from A rightjoin B on A.no=B.no; select * from A,B where A.no(+)=B.no; </td> 
 </tr> 
 <tr> 
 <td> FULL JOIN </td> 
 <td> 左右表均返回所有数据 </td> 
 <td> select * from A inner join B on A.no=B.no; </td> 
 </tr> 
 </tbody> 
</table>

4：外链接中的条件要放在on 关键后面。

### 插入、更新、删除 ###

1：倘若某表中有数列存在默认值，使用INSERT 语句时，如果插入列中包含默认值列，切该列使用默认值，需要使用 DEFAULT 关键字；如果插入列中不包含默认值列，则表中实际数据会自动添加默认值。

2：复制表的定义和数据

CREATE TABLE STU2 AS SELECT * FROM STU; 
    CREATE TABLE STU2 AS SELECT * FROM STU WHERE 1=2;

3：多表插入语句

<table> 
 <tbody> 
 <tr> 
 <td> 无条件 INSERT </td> 
 </tr> 
 <tr> 
 <td> 有条件 INSERT ALL </td> 
 </tr> 
 <tr> 
 <td> 转置 INSERT </td> 
 </tr> 
 <tr> 
 <td> 有条件 INSERT FIRST </td> 
 </tr> 
 </tbody> 
</table>

--将emp中满足 in ('10','20')的数据分别插入 emp1和emp2
    insert all
    	into emp1(empno,ename,job) values (empno,ename,job)
    	into emp1(empno,ename,deptno) values (empno,ename,deptno)
    select empno,ename,job,deptno from emp where deptno in ('10','20');
    
    --将emp中满足job in ('SALESMAN','MANAGER') 的数据存入emp1中，并将表emp中满足deptno in ('10','20')的数据存入emp2 中
    delete emp1;
    delete emp2;
    insert all
    	when job in ('SALESMAN','MANAGER') then
    		into emp1(empno,ename,job) values (empno,ename,job)
    	when deptno in ('10','20') then
    		into emp1(empno,ename,deptno) values (empno,ename,deptno)
    select empno,ename,job,deptno from emp;
    
    
    --将emp中满足job in ('SALESMAN','MANAGER') 的数据存入emp1中，并将表emp中满足deptno in ('10','20')的数据存入emp2 中，如果两个条件都满足的数据，只会在前面表中存在中
    delete emp1;
    delete emp2;
    insert first
    	when job in ('SALESMAN','MANAGER') then
    		into emp1(empno,ename,job) values (empno,ename,job)
    	when deptno in ('10','20') then
    		into emp1(empno,ename,deptno) values (empno,ename,deptno)
    select empno,ename,job,deptno from emp;

4：其它表中的值更新

--若emp表中新增字段dname，需要将dept.dname更新至emp中，且只更新部门为A和B的数据
    --方法一
    update emp
    	set emp.dname=
    		(select dept.dname from dept where dept.deptno=emp.deptno and dept.dname in ('A','B'))
    where exists (select dept.dname from dept where dept.deptno=emp.deptno and dept.dname in ('A','B'));
    
    --方法二（如果ORA-01779：无法修改与非键值保存表对应的列 错误，在dept表中加上唯一索引或主键）
    update (select emp.dname,dept.dname as new_dname 
    		from emp
    		inner join dept on dept.deptno=emp.deptno
    		where dept.dname in ('A','B'))
    set dname = new_dname;
    
    --方法三
    merge into emp
    	using (select dname,deptno from dept where dept.dname in ('A','B')) dept
    	on dept.deptno=emp.deptno
    	when matched then
    	update set emp.dname = new_dname;

5：删除重复数据

--表dupes(id,name) 中name列有重复数据，需要删除重复数据
    --方法一：通过name相同，id不同来除重 (id列为自增)
    delete from dupes a
    where exists (select null from dupes b where b.name= a.name and b.id > a.id);
    --方法二：通过rowid来除重
    delete from dupes a
    where exists (select null from dupes b where b.name= a.name and b.rowid> a.rowid);
    --方法三：通过rowid  和分组来除重
    delete from dupes 
    where rowid in (select rid from (
    				select rowid ad rid,row_number() over (partition by order by id) as seq
    				from dupes)
    				where seq>1);

### 使用字符串 ###

1：遍历字符串

--connect by 字句 （可以配合substr配合）
 select level from dual connect by level<=4;
 	level
 ----------
 	1
 	2
 	3
 	4
 4 row selected

2：regexp\_count(str,'，')函数：计算str中有几个'，'。

3：将字符和数字分离

--数据  abc123	使用如下SQL将其分开
    select regexp_replace(data,'[0-9]','') adc,
    	   regexp_replace(data,'^[0-9]','') 123,
    	from dept;

4：查询只包含字母或数字型的数据

regexp_like(data,'[ABC]'); 相当于 like '%A%'  or like '%B%' or like '%C%';
    regexp_like(data,'[0-9a-zA-Z]+'); 相当于 like '%数字%'  or like '%小写字母%' or like '%大写字母%';
    
    1：regexp_like(data,'A'); 相当于 like '%A%'  
    2: regexp_like(data,'^A'); 相当于 like 'A%'
    3: regexp_like(data,'A$'); 相当于 like '%A'
    4: regexp_like(data,'^A$'); 相当于 like 'A'
    5: regexp_like(data,'^16+'); +号前面的6至少匹配一次，相当于 相当于 like '16%'

5：根据表中的行创建一个分隔列表

--根据表中的行创建一个分隔列表
    select deptno,
    	   listagg(ename,',') whitin group (order by ename) as enames
    from  emp
    group by deptno;

6：取缔n个分隔字符串

原字符串 str=sun,da,sheng   取出da
    select regexp_substr(str,'[^,]+',1,2) as newStr from  V;
    ^:在方括号里面表示否的意思；+表示匹配1次以上；
    [^,]+ 表示匹配不包含逗号的多个字符；
    1：表示从第一个字符开始
    2：表示第二个能匹配 '[^,]+' 的字符串。

7：分解IP地址 192.168.1.110

select regexp_substr(v.ip,'[^,]+',1,1) as a,
    	   regexp_substr(v.ip,'[^,]+',1,2) as b,
    	   regexp_substr(v.ip,'[^,]+',1,3) as c,
    	   regexp_substr(v.ip,'[^,]+',1,4) as d
     from  (select '192.168.1.110' as ip from dual) V;
     --------------------------------------------
    a		b		c		d
    192		168		1		110

### 使用数字 ###

1：常用的聚合函数

select deptno,
    	   AVG(sal) as 平均值,
    	   MIN(sal) as 最小值,
    	   MAX(sal) as 最大值,
    	   SUM(sal) as 合计,
    	   COUNT(*) as 总行数,
    	   COUNT(comm) as 获取提成的人,
    	   AVG(comm) as 错误的人均提成算法,
    	   AVG(coalesce(comm,0)) as 正确的人均提成算法  --需要把空值转换为0
    from emp
    group by deptno;

注意：当表中没有数据时，不加group by会返回一行数据；加group by 没有数据返回。

2：生成累计和

--注：某公司需要计算用人成本，需要对员工工资进行累加，以便于更直观的看出成本变化。（成本累计=当前行以及当前行上面所有行人工成本之和）
    select empno as 编号,
    	   ename as 姓名,
    	   sal   as 人工成本,
    	   sum(sal) over(order by empno) as 成本累计
    from emp
    where deptno =30
    order by empno;
    
    ---------------------------------------
    sum(sal) over(order by empno)   --odery by 的列 还可以加排序方式 asc desc 
    --相当于
    sum("sal") over(order by "empno" range between unbounded preceding and current row);
    sum("sal") over(order by "empno" rows between unbounded preceding and current row)

3：计算累计差

如下步骤实现计算累计差：

1：对数据排序。

select rownum as seq, a.*
    from (select 编号,项目,金额 from tb_A order by 编号) a;

2：可以将seq=1视为收入，其他视为支出，case when 将后面金额变为负数

with  x  as
        (select rownum as seq, a.*
        from (select 编号,项目,金额 from tb_A order by 编号) a)
    select 编号,项目,金额,(case seq=1 when 金额 else -金额 end) as 转换后的值 from x;

3：将转换后的值进行累加处理

with  x  as
        (select rownum as seq, a.*
        from (select 编号,项目,金额 from tb_A order by 编号) a)
    select 编号,项目,金额,sum(case seq=1 when 金额 else -金额 end) over(order by seq) as 余额 from x;

4：更改累计和的值。

前面介绍累加或者累计减，再介绍一种混合场景。银行存取款余额展示。存款为加，取款为减。 将取款变为负数，进行累计，展示余额。

注意：计算出现次数最多的值；返回最值所在的行数据；计算百分比等，不常用，不一一赘述。

### 日期运算 ###

1：加减日、月、年

Date类型可以直接加减天数；加减月份用add\_months函数。

2：加减时、分、秒

Date类型可以直接加减天数，那么1/24就是一个小时，分钟和秒的加减类同。

3：日期间隔之时、分、秒

两个日期减法，得到的天数，乘以24就是小时，再乘以60 就是分钟，再乘以60就是秒

4：日期间隔之日、月、年

加减月份用add\_months；计算月份间隔用函数months\_between。

5：计算一年中周内各日期的次数

步骤：1：获取年度信息

2：计算一年有多少天

3：生成日期列表

4：转换成对应的日期

5：汇总统计

步骤：1：获取年度信息

2：计算一年有多少天

3：生成日期列表

4：转换成对应的日期

5：汇总统计

with x as
 (select to_date('2019-01-01','yyyy-mm-dd') as 年初) from dual),
 x1 as
 (select 年初,add_months(年初,12)) as 下年初 from x),
 x2 as
 (select 年初，下年初，下年初-年初 as 天数 from x1),
 x3 as
 (select 年初+(level-1) as 日期) from x2 connect by level<=天数),
 x4 as 
 (select 日期,to_char(日期,'DY') as 星期) from x3)
 select 星期,count(1) as 天数 from x4 group by 星期;

6：确定当前记录和下一条记录之间相差的天数

需要用到lead() over() 分析函数； lead对应的就是lag

select deptno,
           ename,
           hiredate,
           lead(hiredate) over(order by hiredate) next_hd
    from emp;

### 日期操作 ###

1：sysdate 能得到的信息

<table> 
 <tbody> 
 <tr> 
 <td>&nbsp;</td> 
 <td> 表达式 </td> 
 <td> 1980-12-17 05:20:30 </td> 
 </tr> 
 <tr> 
 <td> 时 </td> 
 <td> to_number(to_char('sysdate','hh24')) </td> 
 <td> 5 </td> 
 </tr> 
 <tr> 
 <td> 分 </td> 
 <td> to_number(to_char('sysdate','mi')) </td> 
 <td> 20 </td> 
 </tr> 
 <tr> 
 <td> 秒 </td> 
 <td> to_number(to_char('sysdate','ss')) </td> 
 <td> 30 </td> 
 </tr> 
 <tr> 
 <td> 日 </td> 
 <td> to_number(to_char('sysdate','dd')) </td> 
 <td> 17 </td> 
 </tr> 
 <tr> 
 <td> 月 </td> 
 <td> to_number(to_char('sysdate','mm')) </td> 
 <td> 12 </td> 
 </tr> 
 <tr> 
 <td> 年 </td> 
 <td> to_number(to_char('sysdate','yyyy')) </td> 
 <td> 1980 </td> 
 </tr> 
 <tr> 
 <td> 年内第几天 </td> 
 <td> to_number(to_char('sysdate','ddd')) </td> 
 <td> 352 </td> 
 </tr> 
 <tr> 
 <td> 一天之始 </td> 
 <td> trunc(sysdate,'dd') </td> 
 <td> 1980-12-17 </td> 
 </tr> 
 <tr> 
 <td> 周初 </td> 
 <td> trunc(sysdate,'day') </td> 
 <td> 1980-12-14 </td> 
 </tr> 
 <tr> 
 <td> 月初 </td> 
 <td> trunc(sysdate,'mm') </td> 
 <td> 1980-12-01 </td> 
 </tr> 
 <tr> 
 <td> 月末 </td> 
 <td> last_day(sysdate) </td> 
 <td> 1980-12-31 05:20:30 </td> 
 </tr> 
 <tr> 
 <td> 下月初 </td> 
 <td> add_months(trunc(sysdate,'mm'),1) </td> 
 <td> 1981-01-01 </td> 
 </tr> 
 <tr> 
 <td> 年初 </td> 
 <td> trunc(sysdate,'yy') </td> 
 <td> 1980-01-01 </td> 
 </tr> 
 <tr> 
 <td> 周几 </td> 
 <td> to_char(sysdate,'day') </td> 
 <td> 星期三 </td> 
 </tr> 
 <tr> 
 <td> 月份 </td> 
 <td> to_char(sysdate,'month') </td> 
 <td> 12月 </td> 
 </tr> 
 </tbody> 
</table>

2：EXTRACT 也可以取时间字段（systimestamp）中的年月日时分秒，返回值为number类型。不能去Date中的时分秒。

3：创建本月日历

枚举指定月份所有日期，并转换为对应的周信息，再按周做一次“行转列”即可

with x1 as /*1:指定日期 如 sysdate*/
 (select to_date(sysdate,'yyyy-mm-dd') as cur_date from dual),
 x2 as /*2：取月初*/
 (select trunc(cur_date,'mm') as 月初, add_months(trunc(cur_date,'mm'),1) as 下月初 from x1),
 x3 as /*3:枚举当月所有天*/
 (select 月初+(level-1) as 日 from x2 connect by level<=(下月初-月初)),
 x4 as /*4:提取周信息*/
 (select to_char(日,'iw') as 所在周, to_char(日,'dd') as 日期, to_number(to_char(日,'d')) from x3)
 select MAX( case 周几 when 2 then 日期 end) 周一,
 MAX( case 周几 when 3 then 日期 end) 周二,
 MAX( case 周几 when 4 then 日期 end) 周三,
 MAX( case 周几 when 5 then 日期 end) 周四,
 MAX( case 周几 when 6 then 日期 end) 周五,
 MAX( case 周几 when 7 then 日期 end) 周六,
 MAX( case 周几 when 1 then 日期 end) 周日
 from x4
 group by 所在周
 order by 所在周;

3：全年日历，类似创建本月日历，有兴趣可以翻阅书籍。

4：确定指定年份季度的开始日期和结束日期

select sn as 季度
 (sn-1)*3+1 as 开始月份,
 add_months(年初,(sn-1)*3) as 开始日期,
 add_months(年初,sn*3)-1 as 结束日期
 from (select trunc(to_date(年,'yyyy'),'yyyy') as 年初,sn
 from (select '2019' as 年,level as sn from dual connect by <=4) a 
 ) b;

5：补充范围内丢失的值

with x as
 (select 开始年份 + (level-1) as 年份
 from (select extract(YEAR from MIN(hiredate)) as 开始年份, 
 extract(YEAR from MAN(hiredate)) as 结束年份
 from scott.emp) connect by level <=(结束年份-开始年份+1)
 select x.年份,count(e.empno) 聘用人数
 from x
 left join scoot.emp e on (extract(YEAR from e.hiredate) = x.年份)
 group by x.年份
 order by 1;

### 范围处理 ###

1：定位连续值得范围（可以使用表自关联或者 lead() over() ）

<table> 
 <tbody> 
 <tr> 
 <td> proj_id </td> 
 <td> proj_start </td> 
 <td> proj_end </td> 
 </tr> 
 <tr> 
 <td> 1 </td> 
 <td> 2019-01-01 </td> 
 <td> 2019-02-01 </td> 
 </tr> 
 <tr> 
 <td> 2 </td> 
 <td> 2019-02-01 </td> 
 <td> 2019-03-01 </td> 
 </tr> 
 </tbody> 
</table>

select proj_id as 工程号,
           proj_start as 开始日期,
           proj_end as 结束日期,
           lead(proj_start) over (order by proj_id) 下个工程开始日期
    from v

把上面sql作为一个视图，然后可以加上相关条件；也可用于列之间的计算，例如：计算用户前后两次登陆时间。