SQL var loc varchar2(30)SQL exec :loc:=South San FranciscoPL/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,e
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 | | <em style="color:transparent">本文来源[email protected]搞@^&代*@码网(</em> 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
搞代码网（gaodaima.com）提供的所有资源部分来自互联网，如果有侵犯您的版权或其他权益，请说明详细缘由并提供版权或权益证明然后发送到邮箱[email protected]，我们会在看到邮件的第一时间内为您处理，或直接联系QQ：872152909。本网站采用BY-NC-SA协议进行授权
转载请注明原文链接：举例一个比较好的表连接的执行计划
转载请注明原文链接：举例一个比较好的表连接的执行计划
