改写的目的是为了缩短语句运行时间,通常第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博客 ” ，链接：https://blog.itpub.net/15720542/viewspace-664827/ ，如需转载，请注明出处，否则将追究法律责任。
