Oracle 查询技巧与优化（⼀）单表查询与排序
前⾔
作为⼀个程序员在写SQL时往往注重结果⽽忽略了本该应⽤的技巧和更优性能的选择（之前本⼈⼀直也是这样），当公司没有⼀个DBA（据我了解⼤多数中⼩公司都是没有的）为我们做SQL优化时那我们理应将SQL尽可能的写的⾼效简洁，⽽不是拿“我是⼀个java程序员或.NET程序员不擅长这些”为借⼝，博主本⼈也是以能写出更⾼效和更优秀的SQL为⽬标⽽构想出本系列blog，通过实际的⽣产环境数据进⾏演练并总结学习⼀些程序员也应当具备的某些DBA的素质，废话不多说，⾸先开始第⼀篇学习与总结，第⼀篇相对⽐较简单，回顾⼀下oracle中基本的查询操作和⼀些函数。
单表查询与排序
⾸先准备数据，这⾥的数据是从⽣产环境DB中备份出来的，即某市考试招⽣系统的⼀个基础学⽣信息表，数据量也不⼤⼤概1W+，约50个
字段，主要是学⽣的个⼈基本信息：
接下来看⼀下⼏个较为简单的查询技巧和注意点。
分页排序
如题，从分页语句开始，都知道oracle中是通过rownum来进⾏分页的，例如我们查询20条到30条之间的数据：
如上所⽰，这种分页⽅式在绝⼤多数情况下是最⾼效的，⽽并⾮是通过s.rn>20 and s<=30，因为这种不如上⾯的效率⾼。接下来如果在分页的基础上添加排序，例如：查询20条到30条之间的数据并按出⽣⽇期（csrq_）排序，很简单，我们只需在最⾥层的查询中加上排序语句即可：
select  *
from  (select  rownum rn, t.*
from  (select  sid_, stuname_, csrq_ from  t_studentinfo) t
where  rownum <= 30) s
where  s.rn > 20
查询结果如下图所⽰：
取随机n 条记录排序
如题，很简单，利⽤oracle提供的随机函数包dbms_random来完成，⾸选简单了解⼀下随机数，即dbms_ramdom.value：
运⾏上述SQL即可以返回⼀个⼤于0⼩于1的38位精度的随机数，如下图： 搞清楚了这⼀点，我们接下来就写⼀个按随机数排序，例如取学⽣表的前10条数据并按随机数排序，⽐如这样写：
select  *
from  (select  rownum rn, t.*
from  (select  sid_, stuname_, csrq_
from  t_studentinfo
order  by  csrq_) t
where  rownum <= 30) s
where  s.rn > 20
select  dbms_random.value  from  dual
select  bmh_, stuname_, csrq_
from  T_STUDENTINFO
where  rownum < 10
order  by  dbms_random.value ;
连续运⾏三次，看⼀下查询结果：
乍⼀看仿佛没问题，没错，是按随机数排序了，每次运⾏结果的顺序都不⼀样，但是请仔细观察，这三次运⾏结果的学⽣仿佛是同⼀批⼈，只是顺序发⽣了改变，没错，这才是问题的关键，我们上⾯的写法是先取学⽣，后排序，也就是说结果已经提前确定，只是顺序在发⽣变化，这样的效果并不是我们预期的，我们期望的是在全体学⽣中随机抽取10⼈，所以我们需要改写⼀下我们的SQL，使其先让全体学⽣排序，再取前10名学⽣：
select t.*
sql优化的几种方式from (select bmh_, stuname_, csrq_
from T_STUDENTINFO
order by dbms_random.value) t
where rownum < 10
这回再连续运⾏三次看⼀下查询结果：
这次再仔细观察⼀下三次的运⾏结果很明显已经没有⼀个重复的⼈了，这也就达到了我们的预期，所以往往SQL的书写顺序也对结果有着决定性的影响，这⼀点尤为重要。
查询中的条件逻辑
如题，oracle提供了case…when语句使得我们可以在SQL中灵活运⽤多条件分⽀来根据值转换为不同的查询结果，例如：
select sid_,
stuname_,
case mzdm_
when1then
'汉族'
when3then
'满族'
when4then
'回族'
when9then
'蒙古族'
end as mz
from t_studentinfo
where sid_ in ('36697', '34115', '39590', '30692')
order by mzdm_
查询结果如下：
在实际应⽤中像这种民族code肯定应该关联对应的字典表的键来获取值，这⾥我们仅仅想演⽰⼀下case…when语句的⽤法，显⽽易见，以case开头并以end结尾，中间是when条件，并在end关键字之后可以通过as起别名，很⽅便。
空值转换
如题，⼜是⼀个很常⽤的东西，⽐如如果某⼀列查询为空时，我们想将空值替换为我们指定的⼀个值，这⾥就可以选择nvl函数或者coalesce函数，均可以达到期望的效果：
select tcsdm_ from t_studentinfo where sid_ in ('33405','29982','28974');
select nvl(tcsdm_,'999') tcsdm from t_studentinfo where sid_ in ('33405','29982','28974');
select coalesce(tcsdm_,'999') tcsdm from t_studentinfo where sid_ in ('33405','29982','28974');
运⾏结果如下图：
但这⾥还是要区分⼀下这两个函数的⽤法，⾸先nvl函数只能传2个参数：NVL( string1, replace_with)，功能很简单，即：如果string1为NULL，则NVL函数返回replace_with的值，否则返回原来的值。再需要注意⼀点就是string1与replace_with需要保持同⼀数据类型，接下来再看⼀看coales
ce函数，它是⽀持多个参数的，形如：COALESCE(exp1,exp2,...,expn)，n>=2，同样它的含义也和nvl区别很⼤（尽管它们达到的效果相同），即：返回第⼀个不为空的表达式，如果都为空则返回空值。关于使⽤哪⼀个好应该也是显⽽易见的，即在多参数的情况下coalesce函数明显更具优势，因为它避免了需要嵌套nvl函数，若是只有两个参数的话就⽆所谓了，正如我们上⾯的例⼦，看个⼈喜好。
模糊查询中的通配符
如题，偶尔在查询中也会遇到这种问题，⾸先创建⼀个测试⽤的视图：
create or replace view v_test_02 as
select'_AAA'as column1 from dual
union all
select'_\AAA'as column1 from dual
union all
select'_AAAB_'as column1 from dual
union all
select'QAAA'as column1 from dual;
直⼊主题，需求是我想查询包含字符串“_AAA”的结果，那么条件中的like语句该怎么写呢？我们先试⼀下这样写看看：select * from v_test_02 where column1 like'_AAA%';

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。