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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/15720542/viewspace-664827/