oracle查询优化改写读书笔记
一、基本查询
1.NULL知识点整理
1)null 不支持加减乘除大小比较，查询结果包含null值时可用is null判断 或者将空值转换为实际值后作为条件
2)null值排序可用关键字 NULLS FIRST 、NULLS LAST
3)如果在子查询中包含空值，则NOT in (空值) 返回为空
select *from emp where sal >=0 or sal is null order by sal nulls first; select *from emp where nvl(sal,0) >=0 ;
2.将空值转换为实际值，nvl和coalesce的区别
nvl(COMMISSION_PCT,0)；coalesce(EXPR1,EXPR2,EXPR3...EXPRn)；相同点：从左往右数，返回第一个非null值。
区别：1) nvl只适合于两个参数的，COALESCE适合于多个参数。
2) coalesce里的所有参数类型必须保持一致，nvl可以不一致。
3.模糊查询包含通配符
字段中包含通配符%、_、#、？等时查询时可以使用转义字符如：
select *from v where vname like '_BCE%' ; --ABCEDF
--_BCEFG --通配符'_' 替代一个或多个字符，把'\'标识为转义字符后 select *from v where vname like '\_BCE%' escape '\'; --_BCEFG
4.translate(expr,from_string,to_string)
from_string 与 to_string 以字符为单位，对应字符一一替换，如果to_string对应位置没有字符，则from_string对应字符被消掉，可用于删除字符串中字母、数字或指定字符。
select translate('ab 你好 bcadefg','abcdefg','1234567') as new_str from dual; --12 你好 2314567
5.UNION 与 OR
当在条件中有or时，经常会改写为union 。需注意union 不仅两个数据集间重复的数据会被去重，单个数据集里重复的数据也会被去重 ，可使用唯一列、主键列或者rowid等避免去重。
6.explain plan 执行计划
稍微复杂的sql得到了意想不到的结果，可以通过执行计划查看sql各语句的执行顺序、解析、连接方式以及耗费等。
相同的写法对于不同的数据量可能会得到不同的结果，具体哪种写法性能最优，应结合执行计划分析。
默认情况下，PLAN_TABLE是全局临时表的同义词，执行explain plan 保持锁定状态，无需担心会话间行锁。
explain plan for select empno, ename from emp where empno = 7788 union select empno, ename from emp where ename = 'SCOTT'; select *from table(dbms_xplan.display); PLAN TABLE OUTPUT Plan hash value: 223241865 ------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2 | 40 | 5 (80)| 00:00:01 | | 1 | SORT UNIQUE | | 2 | 40 | 5 (80)| 00:00:01 | | 2 | UNION-ALL | | | | | | | 3 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 20 | 1 (0)| 00:00:01 | |* 4 | INDEX UNIQUE SCAN | INDEX_EMP_NO | 1 | | 0 (0)| 00:00:01 | | 5 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 20 | 2 (0)| 00:00:01 | |* 6 | INDEX RANGE SCAN | INDEX_EMP_ENAME | 1 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("EMPNO"=7788) 6 - access("ENAME"='SCOTT') Note ----- - dynamic sampling used for this statement
7.表连接
推荐使用join写法，易看清表之间的关系。
--inner join 返回两表匹配的数据 select *from l inner join r on l.v = r.v; --加where条件后的写法 select *from l, r where l.v = r.v; --left join 返回左表全部，右表匹配部分 select *from l left join r on l.v = r.v; --加(+)后的写法 select *from l, r where l.v = r.v(+); --right join 返回右表全部，左表匹配部分 select *from l right join r on l.v = r.v; --加(+)后的写法 select *from l, r where l.v(+) = r.v; --full join 返回左右表全部，匹配数据显示同一行 select *from l full join r on l.v = r.v; --无(+)的写法
8.将分割数据转换为多值IN列表
/* CLARK,KING,MARTIN 不能直接用in查询 可通过如下方式转换 说明：connect by 是树形查询的一个子句，level 表示树形中的级别层次，是个伪劣 通过level <=3 循环3次 正则表达式 regexp_replace 替换掉所有非‘,’字符取长度加1 得到字符串个数 即循环次数。 正则表达式 regexp_substr [^,]+ 得到每个子字符串 另还有 regexp_count('CLARK,KING,MARTIN', ‘,’) + 1 也可得到子字符串数量 regexp_like 模糊匹配 */ select * from emp e where e.ename in (select regexp_substr('CLARK,KING,MARTIN', '[^,]+', 1, level) as ename from dual connect by level <= (length(regexp_replace('CLARK,KING,MARTIN', '[^,]')) + 1));
二、插入、更新与删除
1.阻止对某几列的插入
可以创建一个不包含某几列的视图，新增数据时通过视图。注：通过view新增数据不能使用关键字default
create or replace view v_emp as select emp.empno,emp.ename from emp; insert into v_emp(empno,ename) values(9999,'NM')
2.限制数据录入 WITH CHECK OPTION
1）当条件比较简单，可以直接加在表中，如工资必须大于0
alter table EMP add constraint CH_SAL check (sal>0);2）复杂或者特殊的约束条件是不能加在表里的，如雇佣日期大于当前日期 不能直接使用关键字 sysdate
insert into (select empno, ename, hiredate from emp where hiredate <= sysdate with check option) values (9999, 'test', sysdate + 1); --ora-01402: 视图with check option where 子句违规 --不符合条件(hiredate <= sysdate)时，不允许insert
3.多表插入语句
*无条件 insert */ insert all into emp1(empno,ename,job) values(empno,ename,job) into emp2(empno,ename,deptno) values(empno,ename,deptno) select empno,ename,job,deptno from emp where deptno in (10,20); /*有条件 insert all 按条件插入*/ insert all when job in ('SALESMAN','MANAGER') then into emp1(empno,ename,job) values(empno,ename,job) when deptno in ('20','30') then into emp2(empno,ename,deptno) values(empno,ename,deptno) select empno,ename,job,deptno from emp ; /*有条件 insert first 当第一个表复合条件后第二个表不再插入对应行 */ insert first when job in ('SALESMAN','MANAGER') then into emp1(empno,ename,job) values(empno,ename,job) when deptno in ('20','30') then into emp2(empno,ename,deptno) values(empno,ename,deptno) select empno,ename,job,deptno from emp ; /*转置insert 或者说是insert all 的一个用法*/ insert all into t2(d,des) values('周一',d1) into t2(d,des) values('周二',d2) into t2(d,des) values('周三',d3) into t2(d,des) values('周四',d4) into t2(d,des) values('周五',d5) select d1,d2,d3,d4,d5 from t1
4.用其他表中的值更新
1）通过子查询更新
2）通过可更新view
3) merge写法，性能高
/*更新符合条件的部分数据*/ UPDATE (SELECT EMP.DNAME, DEPT.DNAME AS NEW_DNAME FROM EMP LEFT JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO WHERE DEPT.DNAME IN ('ACCOUNTTING', 'RESEARCH')) SET DNAME = NEW_DNAME; /*merge 写法*/ merge into emp using (select dname, deptno from dept where dept.dname IN ('ACCOUNTTING', 'RESEARCH')) dept on (dept.deptno = emp.deptno) when matched then update set emp.dname = dept.dname
三、实用查询案例
1.返回各部门工资排名前三的员工
select deptno, empno, sal, row_number() over(partition by deptno order by sal desc) as row_number,--工资相同时 ，序号保持递增 rank() over(partition by deptno order by sal desc) as rank, --工资相同时，序号相同，其后序号与row_number 相同 dense_rank() over(partition by deptno order by sal desc) as dense_rank --工资相同时，序号相同，其后序号递增 from emp l where l.deptno in (20, 30) order by 1, 3 desc;
2.返回最值所在行数据，如：工资最高、工资最低人所在行的员工名称
select deptno, empno, max(ename) keep(dense_rank first order by sal) over(partition by deptno) 工资最低的人, max(ename) keep(dense_rank last order by sal) over(partition by deptno) 工资最高的人, ename, sal from emp l where l.deptno = 10 order by 1, 6 desc
3.日期运算
select to_number(to_char(sysdate, 'hh24')) 时, to_number(to_char(sysdate, 'mi')) 分, to_number(to_char(sysdate, 'ss')) 秒, to_number(to_char(sysdate, 'dd')) 日, to_number(to_char(sysdate, 'mm')) 月, to_number(to_char(sysdate, 'yyyy')) 年, to_number(to_char(sysdate, 'ddd')) 年内第几天, trunc(sysdate, 'dd') 一天之始, trunc(sysdate, 'day') 周初, trunc(sysdate, 'mm') 月初, last_day(sysdate) 月末, add_months(trunc(sysdate, 'mm'), 1) 下月初, trunc(sysdate, 'yy') 年初, to_char(sysdate, 'day') 周几, to_char(sysdate, 'month') 月份, months_between(sysdate, sysdate - 31) 间隔月, add_months(sysdate, 5) 加减月, to_char(sysdate,D) 这周的第几天,--Day of week (1–7) 1,2,3,4,5,6,7－－注意：每星期的第1天是”星期日, to_char(sysdate, DD) 这个月的第几天,--Day of month (1–31) 1,2,3,4…31, to_char(sysdate,DDD) 今年的第几天 --Day of year (1–366) 1,2,3,4…366from dual; --判断一年是否是闰年 只需判断2月份有多少天 select to_char(last_day(add_months(trunc(sysdate,'yy'),1)),'dd') from dual;
4.分析函数lead 、lag，取相邻数据
select deptno, ename, hiredate, lead(hiredate) over(order by hiredate) next_hd,--当前记录的下一条记录 lag(hiredate) over(order by hiredate) lag_hd --当前记录的上一条记录 from emp where deptno = 10;
其他应用场景 如一个账号多个时间点登录，可以用此方法查出每次登录间隔。
5.合计信息Group by 子句, ROLLUP, GROUPING, GROUPING_ID,CUBE
1)应用rollup得到总合计信息
select deptno,sum(sal) from emp group by rollup(deptno);
2）如果需要根据多个列汇总数据，可以用括号把两个字段作为一个整体，写法如下
select deptno,sum(sal) from emp group by rollup((deptno,job));
3）有时需要汇总小计及总计数据，且需要区分合计信息所在行，可以用grouping判断当前列是否被汇总，参数只能是group by 后列名
select deptno,job,sum(sal),grouping(deptno) as 是否总计,grouping(job) from emp group by rollup(deptno,job)
易得如下报表
select (case when grouping(deptno) = 1 then '总计' when grouping(job) = 1 then '小计' else to_char(deptNo) end) as 部门编码, job, sum(sal) from emp group by rollup(deptno, job)
4)要求按照deptno,job 的各种组合汇总，并返回总的合计 可用 cube
SELECT (case grouping(deptno) || grouping(job) when '00' then '按部门与工作分组' when '10' then '按工作分组' when '01' then '按部门分组' when '11' then '总合计' end) as grouping, /* 把grouping(deptno) || grouping(job)的结果当作二进制， 再转换为十进制 就是grouping(deptno,job)的值*/ (case grouping_id(deptno,job) when 0 then '按部门与工作分组' when 2 then '按工作分组' when 1 then '按部门分组' when 3 then '总合计' end) as grouping_id, DEPTNO,JOB,SUM(SAL) FROM EMP GROUP BY CUBE(DEPTNO,JOB) order by grouping(deptno) , grouping(job)
6.对不同组同时实现聚集，如员工表中列出员工所在部门及职位的人数
使用分析函数语句较简单，关联查询+子查询语句会复杂很多
select ename, deptno, count(*) over(partition by deptno) as 部门人数, job, count(*) over(partition by job) as 职位人数, count(*) over() 总人数 from emp where deptno in (10, 20)
7 分层查询
/* 1)start with empno = 7566 为起点递归查询 2） prior 可以取得上一级的信息 3）connect by 子句递归条件 上一级人员编码(prior empno) = 本级主管编码(mgr) 向下递归 注意改为 上一级主管编码(prior mgr) = 本级主管编码(empno) 则向上递归 4)level 当前行所在等级，connect_by_isleaf 当前行是否为叶子节点，易知根节点、分支节点、叶子节点 5）sys_connect_by_path 节点路径 6) siblings 在分支内排序 7) empno !=7788 减去一个分支 */ select empno as 员工编码, ename as 员工姓名, mgr as 主管编码, (prior ename) as 主管姓名, level 层级, connect_by_isleaf 叶子节点, sys_connect_by_path(ename, ',') 节点路径 from emp start with empno = 7566 connect by (prior empno) = mgr /*and empno !=7788*/ order siblings by empno desc;
