Oracle 最佳化效率

標籤：color   暫存資料表   and   span   rom   nec   最佳化   inner   nbsp   

1、rownum

(1)

較慢SELECT ywlsdm, qybm, spzt, czsj, czrydm FROM GH_hs WHERE ywlsdm in (SELECT ywlsdm FROM GH_hs WHERE spzt = ‘2‘)
較快SELECT ywlsdm, qybm, spzt, czsj, czrydm FROM GH_hs WHERE ywlsdm in (SELECT ywlsdm FROM GH_hs WHERE spzt = ‘2‘ and rownum <= (SELECT COUNT(*) FROM GH_hs hs WHERE EXISTS (SELECT ywlsdm FROM GH_hs WHERE spzt = ‘2‘)))

2、with as 暫存資料表

select * from (with qybm as (select gx.qybm          from (select a.ghdm                   from gh_ghdm a                  start with a.ghdm = ‘1‘                 connect by prior a.ghdm = a.sjghdm) gh         inner join gh_qyghgx gx on gh.ghdm = gx.tjghdm         ),yg as (   select * from gh_jc_yg t where t.yglx = ‘0‘)select yg.* from qybm , yg where qybm.qybm = yg.qyghdm )

 

Oracle 最佳化效率