首页 / 更多教程 / SQL优化之表连接方式
SQL优化之表连接方式
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了SQL优化之表连接方式,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含6941字,纯文字阅读大概需要10分钟。
内容图文
或者INDEX RANGE SCANSQL> select /*+gather_plan_statistics use_nl(e,d) leading(e)*/* from emp e,dept d where d.deptno=e.deptno; 已选择14行。 执行计划 ---------------------------------------------------------- Plan hash value: 3625962092 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 14 | 812 | 17 (0)| 00:00:01 | | 1 | NESTED LOOPS | | | | | | | 2 | NESTED LOOPS | | 14 | 812 | 17 (0)| 00:00:01 | | 3 | TABLE ACCESS FULL | EMP | 14 | 532 | 3 (0)| 00:00:01 | |* 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 | | 5 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------- -------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("D"."DEPTNO"="E"."DEPTNO") 统计信息 ---------------------------------------------------------- 1 recursive calls 0 db block gets 26 consistent gets 0 physical reads 0 redo size 2035 bytes sent via SQL*Net to client 524 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 14 rows processed
SQL> select /* use_nl(d,e) leading(e) */
* from dept d
left join emp e on d.deptno=e.deptno;
已选择15行。
执行计划
----------------------------------------------------------
Plan hash value: 2251696546
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 812 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN OUTER | | 14 | 812 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 80 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 14 | 532 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 14 | 532 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("D"."DEPTNO"="E"."DEPTNO"(+))
filter("D"."DEPTNO"="E"."DEPTNO"(+))
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
11 consistent gets
0 physical reads
0 redo size
1916 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
15 rows processed
SQL> select /* use_nl(d,e) leading(e) */
* from dept d
left join emp e on d.deptno=e.deptno
where e.sal<3000;
已选择11行。
执行计划
----------------------------------------------------------
Plan hash value: 844388907--------------------------------------------------------------------------------
--------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 406 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN | | 7 | 406 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 80 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 0000:01 |
|* 4 | SORT JOIN | | 7 | 266 | 4 (25)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | EMP | 7 | 266 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("D"."DEPTNO"="E"."DEPTNO")
filter("D"."DEPTNO"="E"."DEPTNO")
5 - filter("E"."SAL"<3000)
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
11 consistent gets
0 physical reads
0 redo size
1756 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
11 rows processed
SQL> select /*+leading(e) use_nl(d,e) */
* from dept d
left join emp e on d.deptno=e.deptno
where e.sal<3000;
已选择11行。
执行计划
----------------------------------------------------------
Plan hash value: 3625962092
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 406 | 10 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | | | |
|
| 2 | NESTED LOOPS | | 7 | 406 | 10 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | EMP | 7 | 266 | 3 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("E"."SAL"<3000)
4 - access("D"."DEPTNO"="E"."DEPTNO")
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
23 consistent gets
0 physical reads
0 redo size
1853 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
11 rows processed
SQL优化之表连接方式
标签:for from class state ffffff strong 嵌套 而且 cpu
本文系统来源:https://www.cnblogs.com/wongandy/p/11337211.html
内容总结
以上是互联网集市为您收集整理的SQL优化之表连接方式全部内容,希望文章能够帮你解决SQL优化之表连接方式所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。