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

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

第一章

将空值转换成实际值

函数coalesce(exp1,exp2,...)

返回第一个为非空的值，避免了返回空值；

查找满足多个条件的行

用到or：只要达到其中一个条件就可以的；

查询所有的提成的员工：即提成不为空，comm is NOT NULL;这个很好的例子；

用括号把多个条件给分隔开；

在where字句中引用别名的列；

即要引用别名的列时必须是一张表中的字段；

select * from (sal as 工资 ,comm as 提成from emp) x where 工资 >2000;

拼接列

用“||”把各列连起来

员工的工作是：ename || ‘的工作是’|| job

在slect语句中使用条件逻辑

即用case来解决：格式为

字段，

case

when then ;

when then ;

else ;

end as 别名

情景：当工资<2000元时，就返回“过低”，<4000 就返回“过高”，

复习考题：（P9）要按照工资档次统计人数；

限制返回的行数

用rownum是依次对数据做出的标识，是所有的数据取出来后才能确定其序号；

rownum<= 2而不能rownum = 2；

查询某个序号可以这样：

select * from (select rownum as sn,emp.* from emp) where rownum <=2) where sn = 2;

从表中随机返回n条记录

先随机排序，再取数据（正确）：

select empno,ename from (select empno,ename from emp order by ()) where rownum <=3;先取数据再随机排序（错误）：

select empno,ename from emp where rownum <=3 order by ();

模糊查询

通配符主要有“like"、"_"、"%"

如果查询中包含通配符就要用到转义字符：

select * from v where vname like '\_like' ESCAPE '\';

第二章给查询结果排序

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

ASC：升序排序，从小到大排序；desc：倒序排序，即从大到小；

order by 3 asc:表示按第三列排序：这种排序适用于该列取值不定时或者说经常改变的列，很方便，比如查询的列增加了，而我们只是排序第一列；

按多个字段排序

排序的字段要用","分开，比如：order by A desc, B asc;

按字符串排序

用到了substr（）函数；主要是运用了快速查找顾客的电话的尾号4的顺序；

substr(phone_number,-4) 表示后四位；

()函数

translate(expr,from_string,to_string):from_string和to_string以字符为单位，对应字符一一替换；

如果to_string 为空，则返回空值；如果to_string对应的位置没有字符，删除

from_string中列出的字符将会被消掉；

运用：

按数字和字母混合字符串中的字母排序

把重要的东西提取出来，次要的东西踢掉，这道题是对translate的运用；

先构造视图

create or replace view v as

select empno || '' || ename as data from emp;

select data,translate(data,'-09','-') as ename from v order by 2;

处理排序空值

在order by 的后面加个 NULLS FIRST或NULLS LAST;

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

领导对工资在1000到2000的感兴趣；

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

第三章操作多个表（P要操作）

2015/04/29 17:00

all 与空字符串

空字符串不等于空值；

与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 ,,, from emp e

inner join dept d on =;

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的影响，设置参数为