oracle sql tunning 15 --常用改寫

改寫的目的是為了縮短語句執行時間,通常第2種方法比第一種好

1.having vs where

(1)select owner,count(*) from all_objects group by owner  having wner='SYS'

     time:44.156 seconds   cost:250

(2)select owner,count(*) from all_objects WHERE wner='SYS' group by owner

     time:2.985 seconds    cost:244

2.minus vs not in

測試表：

drop table test1;
create table test1 as select owner,object_id,object_name from all_objects
drop table test2;
create table test2 as select owner,object_id,object_name from all_objects where object_id>2000

(1)

select object_id
from test1
minus
select object_id
from test2

time:0.688seconds  cost:631

(2)

select distinct a.object_id from test1 a where a.object_id not in (select object_id from test2)

time:0.219seconds  cost:592

3.or vs union

(1)select * from test1 where wner = 'SYS' OR wner='HR' or wner='SCOTT'

time:0.094 seconds cost:109

(2)

SELECT * FROM TEST1 WHERE wner='SYS'
UNION
SELECT * FROM TEST1 WHERE wner='HR'
UNION
SELECT * FROM TEST1 WHERE wner='SCOTT'

time:1.281 seconds cost:329

4.or vs in

(1)select * from test1 where wner = 'SYS' OR wner='HR' or wner='SCOTT'

     time:0.094 seconds cost:109

(2)select * from test1 where owner in ('SYS','HR','SCOTT')

     time:0.016 seconds cost:109

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/15720542/viewspace-664827/，如需轉載，請註明出處，否則將追究法律責任。