SQL> var loc varchar2(30) SQL> exec :loc:='South San Francisco' PL/SQL procedure successfully completed. SQL> SELECT 2 emp.last_name,emp.first_name,j.job_title,d.department_name,l.city,l.state_province,l.postal_code,l.street_address, 3 emp.email,emp.phone_number,emp.hire_date,emp.salary,mgr.last_name 4 from hr.employees emp,hr.employees mgr,hr.departments d,hr.locations 5 l,hr.jobs j 6 where l.city =:loc 7 and emp.manager_id=mgr.employee_id 8 and emp.department_id=d.department_id 9 and d.location_id=l.location_id 10 and emp.job_id=j.job_id; --每次都是以嵌套循環來完畢整個的查詢流程,這就是一個最好的運行計劃 45 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 4121168346 <span style="font-size:10px;">----------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 15 | 2580 | 8 (0)| 00:00:01 | | 1 | NESTED LOOPS | | 15 | 2580 | 8 (0)| 00:00:01 | | 2 | NESTED LOOPS | | 15 | 2400 | 6 (0)| 00:00:01 | | 3 | NESTED LOOPS | | 15 | 1995 | 5 (0)| 00:00:01 | | 4 | NESTED LOOPS | | 4 | 268 | 3 (0)| 00:00:01 | | 5 | TABLE ACCESS BY INDEX ROWID| LOCATIONS | 1 | 48 | 2 (0)| 00:00:01 | |* 6 | INDEX RANGE SCAN | LOC_CITY_IX | 1 | | 1 (0)| 00:00:01 | | 7 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 4 | 76 | 1 (0)| 00:00:01 | |* 8 | INDEX RANGE SCAN | DEPT_LOCATION_IX | 4 | | 0 (0)| 00:00:01 | | 9 | TABLE ACCESS BY INDEX ROWID | EMPLOYEES | 4 | 264 | 1 (0)| 00:00:01 | |* 10 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 10 | | 0 (0)| 00:00:01 | | 11 | TABLE ACCESS BY INDEX ROWID | JOBS | 1 | 27 | 1 (0)| 00:00:01 | |* 12 | INDEX UNIQUE SCAN | JOB_ID_PK | 1 | | 0 (0)| 00:00:01 | | 13 | TABLE ACCESS BY INDEX ROWID | EMPLOYEES | 1 | 12 | 1 (0)| 00:00:01 | |* 14 | INDEX UNIQUE SCAN | EMP_EMP_ID_PK | 1 | | 0 (0)| 00:00:01 | -----------------------------------------------------------------------------------------------------</span> Predicate Information (identified by operation id): --------------------------------------------------- 6 - access("L"."CITY"=:LOC) 8 - access("D"."LOCATION_ID"="L"."LOCATION_ID") 10 - access("EMP"."DEPARTMENT_ID"="D"."DEPARTMENT_ID") 12 - access("EMP"."JOB_ID"="J"."JOB_ID") 14 - access("EMP"."MANAGER_ID"="MGR"."EMPLOYEE_ID") Statistics ---------------------------------------------------------- 2035 recursive calls 0 db block gets 669 consistent gets 19 physical reads 0 redo size 3923 bytes sent via SQL*Net to client 407 bytes received via SQL*Net from client 4 SQL*Net roundtrips to/from client 24 sorts (memory) 0 sorts (disk) 45 rows processed
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索:z360901061

微信掃一掃加我為好友
QQ號聯系: 360901061
您的支持是博主寫作最大的動力,如果您喜歡我的文章,感覺我的文章對您有幫助,請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧,狠狠點擊下面給點支持吧,站長非常感激您!手機微信長按不能支付解決辦法:請將微信支付二維碼保存到相冊,切換到微信,然后點擊微信右上角掃一掃功能,選擇支付二維碼完成支付。
【本文對您有幫助就好】元
