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

o r a c l e查询优化改写技巧和案例(学习笔记)
-标准化文件发布号：（9556-EUATWK-MWUB-WUNN-INNUL-DDQTY-KII
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的影响，设置参数为
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
第八章：日期操作
2016/04/10 15:29
确定一年内属于周内某一天的所有日期
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
给结果集分页
第十一章报表和数据仓库运算
2015/04/27 09:44
行转列
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;
列转行
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
将结果集反向转置为一列
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
：字段内list值去重
某个字段里存的值如这样的（心尘;重复;java;oracle;猪猪;幸福;重复;皮卡丘;远洋;java;燕燕;），那么如何去掉重复的值呢？
1.如果要处理的表只有一行数据，可以只使用
regexp_substr(),regexp_count()和connec by level，即可处理，
其sql语句为：
select regexp_substr(col_name,'[^;]+',1,level) from table_name
connect by level <=regexp_count(col_name,';') +1;
未完待续！可是继续关注。