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

oracle查询优化改写技巧和案例（学习笔记）第一章1.4将空值转换成实际值函数coalesce(exp1,exp2,...)返回第一个为非空的值，避免了返回空值；1.5查找满足多个条件的行用到or：只要达到其中一个条件就可以的；查询所有的提成的员工：即提成不为空，commisNOTNULL;这个很好的例子；用括号把多个条件给分隔开；1.8在where字句中引用别名的列；即要引用别名的列时必须是一张表中的字段；select*from(salas工资,commas提成fromemp)xwhere工资2000;1.9拼接列用“||”把各列连起来员工的工作是：ename||‘的工作是’||job1.10在slect语句中使用条件逻辑即用case来解决：格式为字段，casewhenthen;whenthen;else;endas别名情景：当工资2000元时，就返回“过低”，4000就返回“过高”，复习考题：（P9）要按照工资档次统计人数；1.11限制返回的行数用rownum是依次对数据做出的标识，是所有的数据取出来后才能确定其序号；rownum=2而不能rownum=2；查询某个序号可以这样：select*from(selectrownumassn,emp.*fromemp)whererownum=2)wheresn=2;1.12从表中随机返回n条记录先随机排序，再取数据（正确）：selectempno,enamefrom(selectempno,enamefromemporderbydbms_random.value())whererownum=3;先取数据再随机排序（错误）：selectempno,enamefromempwhererownum=3orderbydbms_random.value();1.13模糊查询通配符主要有“like、_、%如果查询中包含通配符就要用到转义字符：select*fromvwherevnamelike'\_like'ESCAPE'\';第二章给查询结果排序2.1以指定的次序返回查询结果ASC：升序排序，从小到大排序；desc：倒序排序，即从大到小；orderby3asc:表示按第三列排序：这种排序适用于该列取值不定时或者说经常改变的列，很方便，比如查询的列增加了，而我们只是排序第一列；2.2按多个字段排序排序的字段要用,分开，比如：orderbyAdesc,Basc;2.3按字符串排序用到了substr（）函数；主要是运用了快速查找顾客的电话的尾号4的顺序；substr(phone_number,-4)表示后四位；2.4translate()函数translate(expr,from_string,to_string):from_string和to_string以字符为单位，对应字符一一替换；如果to_string为空，则返回空值；如果to_string对应的位置没有字符，删除from_string中列出的字符将会被消掉；运用：2.5按数字和字母混合字符串中的字母排序把重要的东西提取出来，次要的东西踢掉，这道题是对translate的运用；先构造视图createorreplaceviewvasselectempno||''||enameasdatafromemp;selectdata,translate(data,'-0123456789','-')asenamefromvorderby2;2.6处理排序空值在orderby的后面加个NULLSFIRST或NULLSLAST;2.7根据条件不同列中的值来排序领导对工资在1000到2000的感兴趣；selectempnoas编码，enameas姓名,salas工资fromempwheredeptmo=30orderbycasewhensal=1000andsal2000then1else2end,3;第三章操作多个表（P要操作）2015/04/2917:003.1unionall与空字符串空字符串不等于空值；3.2union与or一般不用unionall，这样避免的重复的数据；(P25要复习)为了消除bitmapconvert的影响：altersessionset_b_tree_bitmap_plans=false;但有是用union或or会被忽略，从而出现错误；不过加入一个唯一标识后，即保证了正确去重，又防止了不该发生的去重。还可以用rowid来解决union或or的去重问题；数据取自视图用的是rownum；createorreplaceviewvasselecte.deptno,e.mgr,e.job,d.dnamefromempeinnerjoindeptdond.deptno=e.deptno;witheas(selectrownumassn,deptno,mgr,job,fromv)selectdeptnofrom(selectsn,deptno,fromewheremgr=7698unionselectsn,deptno,fromewherejob='SALESMAN')orderby1;这里的with之后的“e”只是临时创建一个view只在查询期存在，执行with完之后就消失；2015/05/2611:15为了消除bitmpconvert的影响，设置参数为altersessionset_b_tree_bitmap_plans=false;对比planexplainplanforselectempno,ename,fromempwherewmpno=7788orename='SCOTT';select*fromtable(dbms_xplan.display)第八章：日期操作2016/04/1015:298.6确定一年内属于周内某一天的所有日期withxas(selecttrunc(sysdate,'y')+(level-1)dyfromdualconnectbylevel=add_months(trunc(sysdate,'y'),12)-trunc(sysdate,'y'))selectdy,to_char(dy,'dy')as周五fromxwhereto_char(dy,'d')=6;第十章高级查询2015/04/2416:5410.1给结果集分页第十一章报表和数据仓库运算2015/04/2709:4411.1行转列1.使用casewhenend查询需求为对emp按job分组汇总，每个部门显示为一列。第一种情况：selectjobas工作,casedeptnowhen10thensalendas部门10工资，casedeptnowhen20thensalendas部门20工资，casedeptnowhen10thensalendas部门30工资，salas合计工资fromemporderby1;第二种情况：selectjobas工作，sum（casedeptnowhen10thensalend）as部门10工作，sum（casedeptnowhen20thensalend）as部门20工作，sum(casedeptnowhen30thensalend)as部门30工作，sum(sal)as合计工资；2.使用pivot函数select*from(selectjob,sal,com,deptnofromemp)pivot(sum(sal)ass,sum(comm))ascfordeptnoin(10asds10,20asds20,30asds30)orderby1;11.2列转行1.使用UNionAll；（10g以前常用，但不方便）2.使用unpivot函数;（11g以后就可以用，方便）select*fromtable_nameunpivot(new_column_name1fornew_column_name2in(column_name1,column_name2,column_name3))2015/04/3009:5411.3将结果集反向转置为一列selectempsfrom(selectenames,job,to_char(sal)assal,nullast_colfromempwheredeptno=10)unpivotincludenulls(empsforcolin(ename,job,sal,t_col));参数incluenulls表示即使数据为空也显示这一行；第十二章分层查询2015/12/0614:3412.8：字段内list值去重某个字段里存的值如这样的（心尘;重复;java;oracle;猪猪;幸福;重复;皮卡丘;远洋;java;燕燕;），那么如何去掉重复的值呢？1.如果要处理的表只有一行数据，可以只使用regexp_substr(),regexp_count()和connecbylevel，即可处理，其sql语句为：selectregexp_substr(col_name,'[^;]+',1,level)fromtable_nameconnectbylevel=regexp_count(col_name,';')+1;未完待续！可是继续关注