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/，如需轉載，請註明出處，否則將追究法律責任。