Oracle学习系列—数据库优化—In和Exists的使用

关于In和Exists的使用优劣,在官方文档有很好的说明.

实际上并不存在优劣之分,Oracle会跟据实际情况进行In和Exists的相互转换,因此可以说大多数情况下是等效的.

In certain circumstances, it is better to use IN rather than EXISTS. In general, if the selective predicate is in the subquery, then use IN. If the selective predicate is in the parent query, then use EXISTS.

在某种情况,使用In要比Exists更好一些.通常情况下,如果选择断言使用在子查询语句中,最好使用In;如果选择断言在父查询语句中,则最好使用Exists

Note: This discussion is most applicable in an OLTP environment,where the access paths either to the parent SQL or subquery are through indexed columns with high selectivity. In a DSS environment, there can be low selectivity in the parent SQL or subquery, and there might not be any indexes on the join columns. In a DSS environment, consider using semi-joins for the EXISTS case.

备注:这样的讨论在OLTP环境中更适用一些,通常情况下对父或子查询语句的访问通过具有良好选择性的索引列来进行的.而在DSS环境中,访问子或父查询语句都是比较的选择性的或者没有任何索引列.在DSS环境中可以考虑使用Exists语句.

例如

SELECT /* EXISTS example */ e.employee_id , e.first_name , e.last_name , e.salary FROM employees e WHERE EXISTS (SELECT 1 FROM orders o /* Note 1 */ WHERE e.employee_id = o.sales_rep_id /* Note 2 */ AND o.customer_id = 144); /* Note 3 */

将会被解析成

SELECT /* IN example */ e.employee_id , e.first_name , e.last_name , e.salary FROM employees e WHERE e.employee_id IN (SELECT o.sales_rep_id /* Note 4 */ FROM orders o WHERE o.customer_id = 144); /* Note 3 */

例如

SELECT /* IN example */ e.employee_id , e.first_name , e.last_name , e.department_id , e.salary FROM employees e WHERE e.department_id = 80 /* Note 5 */ AND e.job_id = 'SA_REP' /* Note 6 */ AND e.employee_id IN (SELECT o.sales_rep_id FROM orders o); /* Note 4 */

将会被解析成

SELECT /* EXISTS example */ e.employee_id , e.first_name , e.last_name , e.salary FROM employees e WHERE e.department_id = 80 /* Note 5 */ AND e.job_id = 'SA_REP' /* Note 6 */ AND EXISTS (SELECT 1 /* Note 1 */ FROM orders o WHERE e.employee_id = o.sales_rep_id); /* Note 2 */

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/6517/viewspace-145532/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/6517/viewspace-145532/