oracle查询优化改写技巧和案例(学习笔记)

oracle 查询优化改写技巧和案例 学习笔记 第一章 1 4 将空值转换成实际值 函数 coalesce exp1 exp2 返回第一个为非空的值 避免了返回空值 1 5 查找满足多个条件的行 用到 or 只要达到其中一个条件就可以 的 查询所有的提成的员工 即提成不为空 comm is NOT NULL 这个很好的例子 用括号把多个条件给分隔开 1 8 在 where 字句中引用别名的列 即要引用别名的列时必须是一张表中的字段 select from sal as 工资 comm as 提成 from emp x where 工资 2000 1 9 拼接列 用 把各列连起来 员工的工作是 ename 的工作是 job 1 10 在 slect 语句中使用条件逻辑 即用 case 来解决 格式为 字段 case when then when then else end as 别名 情景 当工资 2000 元时 就返回 过低 4000 就返回 过高 复习考题 P9 要按照工资档次统计人数 1 11 限制返回的行数 用 rownum 是依次对数据做出的标识 是所有的数据取出来后才能确定其序号 rownum 2 而不能 rownum 2 查询某个序号可以这样 select from select rownum as sn emp from emp where rownum 2 where sn 2 1 12 从表中随机返回 n 条记录 先随机排序 再取数据 正确 select empno ename from select empno ename from emp order by dbms random value where rownum 3 先取数据再随机排序 错误 select empno ename from emp where rownum 1000 and sal 2000 then 1 else 2 end 3 第三章 操作多个表 P 要操作 2015 04 29 17 00 3 1union all 与空字符串 空字符串不等于空值 3 2union 与 or 一般不用 union all 这样避免的重复的数据 P25 要复习 为了消除 bitmap convert 的影响 alter session set b tree bitmap plans false 但有是用 union 或 or 会被忽略 从而出现错误 不过加入一个唯一标识后 即保证了 正 确去重 又防止了不该发生的去重 还可以用 rowid 来解决 union 或 or 的去重问题 数据取自视图用的是 rownum create or replace view v as select e deptno e mgr e job d dname from emp e inner join dept d on d deptno e deptno with e as select rownum as sn deptno mgr job from v select deptno from select sn deptno from e where mgr 7698 union select sn deptno from e where job SALESMAN order by 1 这里的 with 之后的 e 只是临时创建一个 view 只在查询期存在 执行 with 完之后就消 失 2015 05 26 11 15 为了消除 bitmp convert 的影响 设置参数为 alter session set b tree bitmap plans false 对比 plan explain plan for select empno ename from emp where wmpno 7788 or ename SCOTT select from table dbms xplan display 第八章 日期操作 2016 04 10 15 29 8 6 确定一年内属于周内某一天的所有日期 with x as select trunc sysdate y level 1 dy from dual connect by level add months trunc sysdate y 12 trunc sysdate y select dy to char dy dy as 周五 from x where to char dy d 6 第十章 高级查询 2015 04 24 16 54 10 1 给结果集分页 第十一章 报表和数据仓库运算 2015 04 27 09 44 11 1 行转列 1 使用 case when end 查询需求为对 emp 按 job 分组汇总 每个部门显示为一列 第一种情况 select job as 工作 case deptno when 10 then sal end as 部门 10 工资 case deptno when 20 then sal end as 部门 20 工资 case deptno when 10 then sal end as 部门 30 工资 sal as 合计工资 from emp order by 1 第二种情况 select job as 工作 sum case deptno when 10 then sal end as 部门 10 工作 sum case deptno when 20 then sal end as 部门 20 工作 sum case deptno when 30 then sal end as 部门 30 工作 sum sal as 合计工资 2 使用 pivot 函数 select from select job sal com deptno from emp pivot sum sal as s sum comm as c for deptno in 10 as ds10 20 as ds20 30 as ds30 order by 1 11 2 列转行 1 使用 UNion All 10g 以前常用 但不方便 2 使用 unpivot 函数 11g 以后就可以用 方便 select from table name unpivot new column name1 for new column name2 in column name1 column name2 column name3 2015 04 30 09 54 11 3 将结果集反向转置为一列 select emps from select enames job to char sal as sal null as t col from emp where deptno 10 unpivot include nulls emps for col in ename job sal t col 参数 inclue nulls 表示即使数据为空也显示这一行 第十二章 分层查询 2015 12 06 14 34 12 8 字段内 list 值去重 某个字段里存的值如这样的 心尘 重复 java oracle 猪猪 幸福 重复 皮卡丘 远洋 java 燕燕 那么如何去掉重复的值呢 1 如果要处理的表只有一行数据 可以只使用 rege