摘自《oracle8i性能设计及优化.pdf》
P80 ANY和SOME 操作符号
x > any (select sal from emp where job='ANA') 相当于
exists (select sal from emp where job='ANA' and x > sal)
P80 ALL操作符号
x > all(:first_sal,:second_sal) 相当于
x > :first_sal and x > :second_sal
x > all (select sal from emp where deptno = 10) 相当于
not ( x <= any(select sal from emp where deptno = 10)) 相当于
not exists (select sal from where deptno=10 and x
摘自《Oracle高性能SQL调整》 P347 19.6 调整带有不相等条件的子查询
any 运算符
例如: 返回所有birthday > 出生于1985年之后的任何客户 的职员名称
select ename
from emp
where birthdate > any
(select birthdate from customer where birthdate > '31-DEC-1985')
上面的SQL语句可以优化为
select ename
from emp,(select min(birthdate) min_bday from customer where birthdate > '31-DEC-1985') in_line_view
where emp.birthdate > in_line_view.min_bday;
all 运算符
例如:
select ename
from emp
where birthdate > all
(select birthdate from customer where birthdate > '31-DEC-1985');
可以替换为
select ename
from emp,(select max(birthdate) max_bday from customer where birthdate > '31-DEC-1985') in_line_view
where emp.birthdate > in_line_view.max_bday;
总之,使用ALL和ANY的子查询总是可以用内嵌视图来代替,而且这个视图的性能要好的多,因为它利用了被连接表上的索引。
P80 ANY和SOME 操作符号
x > any (select sal from emp where job='ANA') 相当于
exists (select sal from emp where job='ANA' and x > sal)
P80 ALL操作符号
x > all(:first_sal,:second_sal) 相当于
x > :first_sal and x > :second_sal
x > all (select sal from emp where deptno = 10) 相当于
not ( x <= any(select sal from emp where deptno = 10)) 相当于
not exists (select sal from where deptno=10 and x
摘自《Oracle高性能SQL调整》 P347 19.6 调整带有不相等条件的子查询
any 运算符
例如: 返回所有birthday > 出生于1985年之后的任何客户 的职员名称
select ename
from emp
where birthdate > any
(select birthdate from customer where birthdate > '31-DEC-1985')
上面的SQL语句可以优化为
select ename
from emp,(select min(birthdate) min_bday from customer where birthdate > '31-DEC-1985') in_line_view
where emp.birthdate > in_line_view.min_bday;
all 运算符
例如:
select ename
from emp
where birthdate > all
(select birthdate from customer where birthdate > '31-DEC-1985');
可以替换为
select ename
from emp,(select max(birthdate) max_bday from customer where birthdate > '31-DEC-1985') in_line_view
where emp.birthdate > in_line_view.max_bday;
总之,使用ALL和ANY的子查询总是可以用内嵌视图来代替,而且这个视图的性能要好的多,因为它利用了被连接表上的索引。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10805681/viewspace-402160/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/10805681/viewspace-402160/