[Oracle] 《Oracle查询优化改写技巧与案例2.0》读书笔记

前言

只是记录和整理了一些对我有用的笔记，详细得请看原著。

单表查询

查找空值

select * from emp where comm is null

NULL 不支持 加、减、乘、除、大小比较、相等比较，否则只能为空；

处理空值NVL

NVL 只能处理多个参数，如果要处理多个参数，则可以使用COALESCE

在 WHERE 子句中 引用取别名的列

select * from （select sal as 工资， comm as 提成 from emp） x where 工资  <= 1000;

限制返回的行数

rownum 是依次对数据做标识的，需要先有第一名，后面才会有第二名。

select * 
from （select rownum as sn,emp.* from emp where rownum <= 2）
where sn = 2

给查询结果排序

以指定的次序返回查询结果

除了 order by 字段名 ASC 这种写法，还可以写成 order by 3 ASC 意思是按第三列排序。
用数据代替列位置只能用于order by 字句中，其他地方都不能用。

从表中随机返回n条记录

先随机排序，再取数据

select empno, ename
from (select empno,ename from emp order by dbms_random.value())
where rownum <=3

TRANSLATE

select  TRANSLATE （'ab 你好 bcadefg','abcdefg','1234567' ） as new_str from dual;

结果：
12你好2314567

处理排序空值

Oracle 默认 升序时空值在后，降序时空值在前
可以使用关键字 NULLS FIRST，NULLS LAST 更改空值顺序

根据条件取不同列中的值来排序

例如：领导对工资在1000到2000元之前员工排在前面，以便优先查看。

select empno as 编码，
       ename as 姓名，
       sal as 工资
       from emp
       where dempno = 30 
       order by case when sal >= 1000 and sal < 2000 THEN 1 else 2 end,3;

操作多个表

IN、EXISTS和INNER JOIN

IN 写法

select empno, ename, job,sal,deptno
from emp
where (ename,job,sal) in (select ename,job,sal from emp2); 

EXISTS 写法

select empno,ename,job,sal,deptno
from emp a
where exists (select NULL emp2 b where 
                  b.ename = a.ename 
                  and b.job =a.job
                  and b.sal = a.sal )

INNER JOIN 写法

select a.empno,a.ename,a.job,a.sal,a.deptno from emp a 
inner join emp2 b on (b.ename = a.ename and b.job = a.job and b.sal = a.sal) 

JOIN 写法中利用了 HASH JOIN（哈希连接） ，其他两种应用都是HASH JOIN SEMI (哈希半连接)。
更详细的两者的区别请看Oracle 连接和半连接，我是没看懂。。。

插入、更新与删除

阻止对某几列插入

建表的时候会遇到默认值 SYSDATE，这种列一般是为了记录数据生成的时间，不允许手动录入，那么系统控制不到位，可能会出现手动录入的情况发生这时可以通过视图来完成。

表 T4_1（c1,c2,c3,c4）,c4 是SYSDATE
create or replace view v4_1 as select c1,c2,c3 from T4_1
insert into v4_1(c1,c2,c3) values ('手输c1',null,'不能改c4');

复制表的定义及数据

create table t4_2 as select * from t4_1;

用WITH CHECK OPTION 限制数据录入

当约束条件比较简单时，可以直接加在表中，如工资必须大于0

alter table emp add constraints ch_sal check(sal > 0 ); 

insert into 
    (select empno,ename,hiredate)
     from emp
     where hiredate <= SYSDATE WITH CHECK OPTION)
     VALUES 
     (9999,'TEST',SYSDATE+1)
    
     会提示错误

(select empno,ename,hiredate)
from emp
where hiredate <= SYSDATE WITH CHECK OPTION) 是个视图

多表插入语句

无条件INSERT

因为没有加条件，所以会同时向两个表插入数据，且两个表中插入的条数一样

insert all
into emp1(empno,ename) values (empno,ename)
into emp2(empno,deptno) values (empno,ename)
select empno,ename,deptno from emp;

有条件INSERT ALL

insert all
when ename in ('x','t') then 
into emp1(empno,ename) values (empno,ename)
when deptno in ('1','2') then 
into emp2(empno,deptno) values (empno,ename)
select empno,ename,deptno from emp;

有条件INSERT FIRST

当第一个表符合条件后，第二个表将不再插入对应的行

insert first
when ename in ('x','t') then 
into emp1(empno,ename) values (empno,ename)
when deptno in ('1','2') then 
into emp2(empno,deptno) values (empno,ename)
select empno,ename,deptno from emp;

合并记录

merge into bonuses d
using (select employee_id,salary,depart_id
       from employees
       where depart_id = 80
      ) s
    on (d.employee_id = s.employee_id)
    when matched then 
    update 
        set d.bonus = d.bonus + s.salary * 0.01
    when not matched then
    insert 
    (d.employee_id,d.bonus)
    values 
    (s.employee_id,s.salary * 0.01 )

注意事项：
（1）语句是merge into bonuses ,所以在这个语句中只能更改bonuses 的数据，不能改using 后面那些表的数据。
（2）更新，插入两个操作是同时进行的，不分先后。
（3） 在MERGE INTO 语句里不能更新JOIN系列
（4） ON 后面的条件一定要放在括号里，否则会报错
on (d.employee_id = s.employee_id)

删名字重复的记录

通过rowid

delete 
  from dupes a
  where exists (select null from dupes b where b.name = a.name
  and b.rowid > a.rowid)

通过分析函数

delete 
from dupes 
where rowid in (select rid 
             from (select rowid as rid, row_number() over(partition by name order by id) as seq from dupes
             whereseq >1 );
)

使用字符串

生成连续数值

select rownum as rn from emp where rownum <= 3;

select LeveL AS rn from dual CONNECT BY LEVEL <=3;

遍历字符串

select 汉字，LEVEL，substr(汉字，LEVEL，1) as 汉字拆分 from v5_2
connect by LEVEL <= length(汉字);

将字符和数字数据分离

select regexp_replace(data,'[0-9]','') dname,
regexp_replace(data,'[^0-9]','') deptno
from t5_5;

根据表中的行创建一个分割列表

select deptno,
  listagg(ename,',') within group(ORDER BY ename) As total_name
  from emp
  GROUP BY deptno;

生成排名

row_number() over(partition by deptno order by sal desc);
rank() over(partition by deptno order by sal desc);
dense_rank() over(partition by deptno order by sal desc);

row_number() 相同的工资会生成 1,2,3
rank 相同的工资会生成同样的序号 1,1,3
dense_rank() 会生成同样的序号 1,1，2

改写优化案例

不建议使用标量子查询，直接改成left join

可以使用MAX() 代替 rownum =1

参考书籍

Oracle查询优化改写技巧与案例2.0