SQL 執行順序 你懂的

哎呀我的天吶發表於2015-12-07
SQL

SELECT * FROM employees e LEFT JOIN  departments d ON e.department_id = d.department_id   AND d.DEPARTMENT_ID > 100 

很顯然and條件先於join執行

SELECT * FROM employees e LEFT JOIN  departments d ON e.department_id = d.department_id   WHERE d.DEPARTMENT_ID > 100 

SELECT * FROM employees e LEFT JOIN  departments d ON e.department_id =

d.department_id  WHERE d.DEPARTMENT_ID > 100



Plan hash value: 2599337293



----------------------------------------------------------------------------------------------------------------------

| Id  | Operation		       | Name	       	   | Starts | E-Rows | A-Rows |	  A-Time   | Buffers | Reads |

----------------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT	       |                   |	  1 |	     |	    2 |00:00:00.01 |	  8 |	   1 |

|   1 |  NESTED LOOPS		       |       	           |	  1 |	     |	    2 |00:00:00.01 |	  8 |	   1 |

|   2 |   NESTED LOOPS		       |                   |	  1 |	   1 |	    2 |00:00:00.01 |	  6 |	   1 |

|   3 |    TABLE ACCESS BY INDEX ROWID | EMPLOYEES         |	  1 |	   2 |	    2 |00:00:00.01 |	  4 |	   1 |

|*  4 |     INDEX RANGE SCAN	       | EMP_DEPARTMENT_IX |	  1 |	   2 |	    2 |00:00:00.01 |	  2 |	   1 |

|*  5 |    INDEX UNIQUE SCAN	       | DEPT_ID_PK	   |	  2 |	   1 |	    2 |00:00:00.01 |	  2 |	   0 |

|   6 |   TABLE ACCESS BY INDEX ROWID  | DEPARTMENTS	   |	  2 |	   1 |	    2 |00:00:00.01 |	  2 |	   0 |

----------------------------------------------------------------------------------------------------------------------





Predicate Information (identified by operation id):

---------------------------------------------------





   4 - access("E"."DEPARTMENT_ID">100)

   5 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")

       filter("D"."DEPARTMENT_ID">100)



26 rows selected.


SELECT * FROM employees e LEFT JOIN  departments d ON e.department_id = d.department_id   AND d.DEPARTMENT_ID > 10  WHERE d.MANAGER_ID > 200 

SELECT * FROM employees e LEFT JOIN  departments d ON e.department_id =
d.department_id  AND d.DEPARTMENT_ID > 10  WHERE d.MANAGER_ID > 200
Plan hash value: 3971452728
-------------------------------------------------------------------------------------------------------------
| Id  | Operation		       | Name		   |Starts | E-Rows | A-Rows |	  A-Time   | Buffers|
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	       |                   |	 1 |	    |	   6 | 00:00:00.01 |	  9 |
|   1 |  NESTED LOOPS		       | 		   |	 1 |	    |	   6 | 00:00:00.01 |	  9 |
|   2 |   NESTED LOOPS		       | 		   |	 1 | 	  9 |	   6 | 00:00:00.01 |	  7 |
|*  3 |    TABLE ACCESS BY INDEX ROWID | DEPARTMENTS	   |	 1 |	  1 |	   4 | 00:00:00.01 |	  4 |
|*  4 |     INDEX RANGE SCAN	       | DEPT_ID_PK	   |	 1 |     26 |	  26 | 00:00:00.01 |	  2 |
|*  5 |    INDEX RANGE SCAN	       | EMP_DEPARTMENT_IX |	 4 |     10 |	   6 | 00:00:00.01 |	  3 |
|   6 |   TABLE ACCESS BY INDEX ROWID  | EMPLOYEES         |	 6 |      9 |	   6 | 00:00:00.01 |	  2 |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("D"."MANAGER_ID">200)
   4 - access("D"."DEPARTMENT_ID">10)
   5 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
       filter("E"."DEPARTMENT_ID">10)
from (join/AND) on where group by having order by rownum

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29990276/viewspace-1867351/，如需轉載，請註明出處，否則將追究法律責任。

相關文章