首页 / MYSQL / 举例一个比较好的表连接的执行计划
举例一个比较好的表连接的执行计划
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了举例一个比较好的表连接的执行计划，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含3646字，纯文字阅读大概需要6分钟。
内容图文
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
-----------------------------------------------------------------------------------------------------
| 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 |
-----------------------------------------------------------------------------------------------------
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
内容总结
以上是互联网集市为您收集整理的举例一个比较好的表连接的执行计划全部内容，希望文章能够帮你解决举例一个比较好的表连接的执行计划所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源：【匿名】