Oracle中的高效语句
Where子句中的连接顺序
ORACLE采用自下而上的顺序解析WHERE子句
根据这个原理 表之间的连接必须写在其他WHERE条件之前 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾
举例
（低效）select … from table t where t sal > and t jobtype = and < （select count（*） from table t where t pno = t tno;（高效）select … from table t where < （select count（*） from table t where t pno = t tno and t sal > and t jobtype = ; Select子句中避免使用 *
当你想在select子句中列出所有的column时 使用动态SQL列引用 * 是一个方便的方法
不幸的是 这是一个非常低效的方法
实际上 ORACLE在解析的过程中 会将 * 依次转换成所有的列名 这个工作是通过查询数据字典完成的 这意味着将耗费更多的时间
减少访问数据库的次数
当执行每条SQL语句时 ORACLE在内部执行了许多工作
解析SQL语句 估算索引的利用率 绑定变量 读数据块等等
由此可见 减少访问数据库的次数 就能实际上减少ORACLE的工作量
举例
题目——我要查找编号为 学生的信息
（低效）select name age gender address from t_student where id = ;select name age gender address from t_student where id = ;（高效）select a name a age a gender a address b name b age b gender b address from t_student a t_student b where a id = and b id = ; 使用Decode函数来减少处理时间
使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表
举例
（低效）select count（*） sum（banace） from table where dept_id = and name like anger% ;select count（*） sum（banace） from table where dept_id = and name like anger% ;（高效）select count（decode（dept_id XYZ null）） count_ count（decode（dept_id XYZ null）） count_ sum（decode（dept_id dept_id null）） sum_ sum（decode（dept_id dept_id null）） sum_ from table where name like anger% ; 整合简单 无关联的数据库访问
如果你有几个简单的数据库查询语句 你可以把它们整合到一个查询中（即使它们之间没有关系）举例
（低效）select name from table where id = ;select name from table where id = ;select name from table where id = ;（高效）select t name t name t namefrom table t table t table t where t id（+） = and t id（+） = and t id（+） =
【注 上面例子虽然高效 但是可读性差 需要量情而定啊！】[nextpage]
删除重复记录
最高效的删除重复记录方法 （ 因为使用了ROWID）举例
delete from table t where t rowid > （select min（t rowid） from table t where t id = t id） 尽量不要使用having子句 可以考虑用where替换
having只会在检索出所有记录之后才对结果集进行过滤 这个处理需要排序 总计等操作
如果能通过where子句限制记录的数目 那就能减少这方面的开销 [nextpage]
尽量用表的别名
当在SQL语句中连接多个表时 请使用表的别名并把别名前缀于每个Column上
这样一来 就可以减少解析的时间并减少那些由Column歧义引起的语法错误
用exists替代in（发现好多程序员不知道这个怎么用）
在许多基于基础表的查询中 为了满足一个条件 往往需要对另一个表进行联接
在这种情况下 使用exists（或not exists）通常将提高查询的效率
举例
（低效）select … from table t where t id > and pno in （select no from table where name like % ） （高效）select … from table t where t id > and exists （select from table t where t pno = t no and name like % ） 用not exists替代not in
在子查询中 not in子句将执行一个内部的排序和合并
无论在哪种情况下 not in都是最低效的 （因为它对子查询中的表执行了一个全表遍历）
为了避免使用not in 我们可以把它改写成外连接（Outer Joins）或not exists
用exists替换distinct
当提交一个包含一对多表信息的查询时 避免在select子句中使用distinct 一般可以考虑用exists替换举例
（低效）select distinct d dept_no d dept_name from t_dept d t_emp e where d dept_no = e dept_no;（高效）select d dept_no d dept_name from t_dept d where exists （select from t_emp where d dept_no = e dept_no） exists使查询更为迅速 因为RDBMS核心模块将在子查询的条件一旦满足后 立刻返回结果
用表连接替换exists
通常来说 采用表连接的方式比exists更有效率
举例
（低效）select ename from emp e where exists （select from dept where dept_no = e dept_no and dept_cat = W ） SELECT ENAME（高效）select ename from dept d emp e where e dept_no = d dept_no and dept_cat = W ; 避免在索引列上使用is null和is not null避免在索引中使用任何可以为空的列 ORACLE将无法使用该索引
对于单列索引 如果列包含空值 索引中将不存在此记录 对于复合索引 如果每个列都为空 索引中同样不存在此记录 如果至少有一个列不为空 则记录存在于索引中
举例
如果唯一性索引建立在表的A列和B列上 并且表中存在一条记录的A B值为（ null） ORACLE将不接受下一条具有相同A B值（ null）的记录（插入） 然而如果所有的索引列都为空 ORACLE将认为整个键值为空而空不等于空
因此你可以插入 条具有相同键值的记录 当然它们都是空！
lishixinzhi/Article/program/Oracle/201311/16701
绛旓細鍏堝缓涓存椂琛ㄥ啀杩涜鍒犻櫎鐨勬搷浣滆姣旂洿鎺ョ敤涓鏉¤鍙ヨ繘琛屽垹闄よ楂樻晥寰楀銆備笂闈鐨勮鍙浼氭妸鎵鏈夐噸澶嶇殑鍏ㄩ兘鍒犻櫎锛屽湪oracle涓锛屾湁涓殣钘忎簡鑷姩rowid锛岄噷闈㈢粰姣忔潯璁板綍涓涓敮涓鐨剅owid锛屾垜浠鏋滄兂淇濈暀鏈鏂扮殑涓鏉¤褰曪紝鎴戜滑灏卞彲浠ュ埄鐢ㄨ繖涓瓧娈碉紝淇濈暀閲嶅鏁版嵁涓璻owid鏈澶х殑涓鏉¤褰曞氨鍙互浜嗐備笅闈㈡槸鏌ヨ閲嶅鏁版嵁鐨勪竴涓...
绛旓細鍦oracle鏁版嵁搴撲腑,瑕佹眰涓や釜瀛楁鐨勫拰鍙互鐢╯ql璇彞锛堝墠鎻愭槸涓や釜瀛楁閮芥槸鏁板瓧鍨嬶級锛歋ELECT num1+num2 AS num FROM table_name;鍏朵腑num1銆乶um2鏄姹傚拰鐨勪袱涓瓧娈碉紝num鏄柊鍛藉悕鐨勫拰瀛楁锛宼able_name鏄鏌ヨ鐨勮〃鍚嶃
绛旓細鍦ORACLE涓鏌ヨ鍓100鏉¤褰鐨勮鍙鐨勪功鍐欐柟娉曟槸锛歱roc sql outobs=100锛泂elect * from table1锛泀uit銆傜敤SAS鍐橲QL灏卞簲璇ョ敤瀹冮噷闈㈣嚜甯︾殑涓浜涘姛鑳斤紝鍙兘浼氭洿绠鍗曘傛煡璇竴涓〃涓墍鏈夊瓧娈甸兘鐩稿悓鐨勮褰曪細 姣斿鐜板湪鏈変竴浜哄憳琛 锛堣〃鍚嶏細peosons锛 鑻ユ兂灏嗗鍚嶃佺紪鍙枫佷綇鍧杩欎笁涓瓧娈靛畬鍏ㄧ浉鍚岀殑璁板綍鏌ヨ鍑烘潵锛...
绛旓細max琛屼笉锛熸寜鐓rocesskey鍒嗙粍锛岀劧鍚庢壘鍒癿ax(uploadtime)锛屽啀鑷繛鎺ュ氨鍙互浜嗭紝浣犱笉鏄氨瑕佹渶澶х殑涔堛俿elect processkey,name from table,(select processkey,max(uploadtime) time from table group by processkey) a where a.processkey=table.processkey and table.uploadtime=a.time 杩欎釜姣旀帓搴忕劧鍚庡彇...
绛旓細鎬濊矾涓猴細鍏堟壘鍑烘瘡涓鍙峰搴旂殑鏈楂樺垎锛岀敤瀛﹀彿鍔犳渶楂樺垎浣滀负涓存椂琛紝鍐嶄笌鍘熻〃鍏宠仈锛屽湪鍘熻〃涓煡璇㈠嚭瀛﹀彿涓庢渶楂樺垎瀵瑰簲鐨勫鍚嶅拰鏈堜唤銆傜敱浜庢槸鍚岃〃闂村叧鑱斿叧绯伙紝oracle涓鏃犳硶鐩存帴瀹氫箟涓存椂琛紝鏁呬娇鐢╓ITH..AS..璇彞瑙ｅ喅銆傛垜鐨勬祴璇曟暟鎹负锛歋TUDENT琛細鍏朵腑鍙互鐪嬪嚭灏忕孩1鏈1鏃ュ垎鏁版渶楂橈紝涓13573鍒嗭紱灏忔槑2鏈1鏃...
绛旓細1銆佸垱寤烘祴璇曡〃锛宑reate table test_date(id varchar2(20),v_date date);2銆佹彃鍏ユ祴璇曟暟鎹紱insert into test_date select level, trunc(sysdate)+level v_date from dual connect by level<=5 commit;3銆佹煡璇㈣〃涓叏閲忔暟鎹紱select t.*, rowid from test_date t;4銆佺紪鍐璇彞锛屾煡璇㈡椂闂寸瓑浜庡綋鍓...
绛旓細瀹炵幇渚嬪彞濡備笅锛歋ELECTa.*FROMtable1aWHERENOTEXISTS(SELECT1FROMtable1bWHEREb.id>a.id)鎴栬匰ELECT*FROMtable1WHEREidIN(SELECTMax(id)FROMtable1)鍙堟垨鑰匰ELECT*FROMtable1WHEREid=(SELECTMax(id)FROMtable1)
绛旓細l l 鏄惁闇瑕佸缓绔ORACLE鏁版嵁搴绱㈠紩銆佽仛闆嗐佹暎鍒? l l 绯荤粺鍏ㄥ眬鍖(SGA)澶у皬鏄惁瓒冲? l l SQL璇彞鏄惁楂樻晥? 2銆2銆佹暟鎹粨搴撶郴缁(Data Warehousing),杩欑淇℃伅绯荤粺鐨勪富瑕佷换鍔℃槸浠ORACLE鐨娴烽噺鏁版嵁涓繘琛屾煡璇,寰楀埌鏁版嵁涔嬮棿鐨勬煇浜涜寰嬨傛暟鎹簱绠＄悊鍛橀渶瑕佷负杩欑绫诲瀷鐨凮RACLE鏁版嵁搴撶潃閲嶈冭檻涓嬭堪鍙傛暟: l l 鏄惁閲囩敤B*-...
绛旓細1銆乻elect emp.* from emp,(select deptno,avg(sal) avg1 from emp group by deptno)B where emp.deptno=B.deptno and emp.sal>B.avg1;2銆乻elect emp.*,B.avg1 骞冲潎宸ヨ祫 from emp,(select deptno,avg(sal) avg1 from emp group by deptno)B where emp.deptno=B.deptno and emp.sal>...
绛旓細SELECT FROM employees2 c WHERE c.salary> (select avg(salary) from employees2 b where b.division_id=c.division_id)GROUP BY c.division_id
