OracleHint学习之三
与表连接顺序的相关hint：
扎鲁特旗网站制作公司哪家好，找创新互联公司！从网页设计、网站建设、微信开发、APP开发、自适应网站建设等网站项目制作，到程序开发，运营维护。创新互联公司于2013年创立到现在10年的时间，我们拥有了丰富的建站经验和运维经验，来保证我们的工作的顺利进行。专注于网站建设就选创新互联公司。
执行顺序emp-jobs-dept
SQL> select /*+ ordered */e.ename,j.job,e.sal,d.deptno from emp e,jobs j,dept d where e.empno=j.empno and e.deptno=d.deptno and d.loc='CHICAGO' order by e.ename;
6 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 477715418
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 235 | 9 (23)| 00:00:01 |
| 1 | SORT ORDER BY | | 5 | 235 | 9 (23)| 00:00:01 |
|* 2 | HASH JOIN | | 5 | 235 | 8 (13)| 00:00:01 |
| 3 | MERGE JOIN | | 14 | 504 | 6 (17)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| EMP | 14 | 238 | 2(0)| 00:00:01 |
| 5 | INDEX FULL SCAN | PK_EMP | 14 | | 1(0)| 00:00:01 |
|* 6 | SORT JOIN | | 14 | 266 | 4 (25)| 00:00:01 |
| 7 | TABLE ACCESS FULL | JOBS | 14 | 266 | 3(0)| 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 11 | 2(0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | IDX_DEPT_LOC | 1 | | 1(0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("E"."DEPTNO"="D"."DEPTNO")
6 - access("E"."EMPNO"="J"."EMPNO")
filter("E"."EMPNO"="J"."EMPNO")
9 - access("D"."LOC"='CHICAGO')
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
898 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
6 rows processed修改from后表的顺序：执行顺序变成emp-dept-jobs
SQL> select /*+ ordered */e.ename,j.job,e.sal,d.deptno from emp e,dept d,jobs j where e.empno=j.empno and e.deptno=d.deptno and d.loc='CHICAGO' order by e.ename; 6 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 3709357593 ------------------------------------------------------------------------------------------------ | Id | Operation| Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT| | 5 | 235 | 9(23)| 00:00:01 | | 1 | SORT ORDER BY| | 5 | 235 | 9(23)| 00:00:01 | |* 2 | HASH JOIN| | 5 | 235 | 8(13)| 00:00:01 | | 3 | MERGE JOIN| | 5 | 140 | 5(20)| 00:00:01 | | 4 | TABLE ACCESS BY INDEX ROWID | EMP | 14 | 238 | 2 (0)| 00:00:01 | | 5 | INDEX FULL SCAN| IDX_EMP_DEPT | 14 | | 1 (0)| 00:00:01 | |* 6 | SORT JOIN| | 1 | 11 | 3(34)| 00:00:01 | | 7 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 11 | 2 (0)| 00:00:01 | |* 8 | INDEX RANGE SCAN| IDX_DEPT_LOC | 1 | | 1 (0)| 00:00:01 | | 9 | TABLE ACCESS FULL| JOBS | 14 | 266 | 3 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------
Leading hint：是针对多个目标表的hint，它的含义是让优化器将我们指定的多个表的连接结果作为目标sql表连接过程中的驱动结果集，并且将leading hint中从左到右出现的第一个目标表作为整个表连接过程中的首个驱动表：（emp_temp--emp--dept-jobs）对于没有指定的，优化器可以调整，而ordered表连接顺序被指定死了。
SQL> select /*+ leading(t e) */e.ename,j.job,e.sal,d.deptno from emp e,jobs j,dept d,emp_temp t where e.empno=j.empno and e.deptno=d.deptno and e.ename=t.ename and d.loc='CHICAGO' order by e.ename; 6 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 3130730953 ----------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 5 | 270 | 12(9)| 00:00:01 | | 1 | SORT ORDER BY | | 5 | 270 | 12(9)| 00:00:01 | |* 2 | HASH JOIN | | 5 | 270 | 11(0)| 00:00:01 | |* 3 | HASH JOIN | | 5 | 175 | 8(0)| 00:00:01 | |* 4 | HASH JOIN | | 14 | 336 | 6(0)| 00:00:01 | | 5 | TABLE ACCESS FULL | EMP_TEMP | 14 | 98 | 3(0)| 00:00:01 | | 6 | TABLE ACCESS FULL | EMP | 14 | 238 | 3(0)| 00:00:01 | | 7 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 11 | 2(0)| 00:00:01 | |* 8 | INDEX RANGE SCAN | IDX_DEPT_LOC | 1 | | 1(0)| 00:00:01 | | 9 | TABLE ACCESS FULL | JOBS | 14 | 266 | 3(0)| 00:00:01 | -----------------------------------------------------------------------------------------------
USE_MERGE：中指定的目标表应该是排序合并连接的中的被驱动表：
SQL> select /*+ use_merge(e) */ * from emp e,dept d where e.deptno=d.deptno; 14 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 4082513813 --------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 14 |812 | 6 (17)| 00:00:01 | | 1 | MERGE JOIN | | 14 |812 | 6 (17)| 00:00:01 | | 2 | TABLE ACCESS BY INDEX ROWID| EMP | 14 |532 | 2 (0)| 00:00:01 | | 3 | INDEX FULL SCAN | IDX_EMP_DEPT | 14 | | 1 (0)| 00:00:01 | |* 4 | SORT JOIN | | 4 | 80 | 4 (25)| 00:00:01 | | 5 | TABLE ACCESS FULL | DEPT | 4 | 80 | 3 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------
SQL> select /*+ use_merge(e j d t) */e.ename,j.job,e.sal,d.deptno from emp e,jobs j,dept d,emp_temp t where e.empno=j.empno and e.deptno=d.deptno and e.ename=t.ename and d.loc='CHICAGO' order by e.ename; 6 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 2730522951 -------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 5 | 270 | 15 (34)| 00:00:01 | | 1 | MERGE JOIN | | 5 | 270 | 15 (34)| 00:00:01 | | 2 | SORT JOIN | | 5 | 235 | 11 (37)| 00:00:01 | | 3 | MERGE JOIN | | 5 | 235 | 10 (30)| 00:00:01 | | 4 | SORT JOIN | | 5 | 140 | 6 (34)| 00:00:01 | | 5 | MERGE JOIN | | 5 | 140 | 5 (20)| 00:00:01 | | 6 | TABLE ACCESS BY INDEX ROWID | EMP | 14 | 238 | 2 (0)| 00:00:01 | | 7 | INDEX FULL SCAN | IDX_EMP_DEPT | 14 | | 1 (0)| 00:00:01 | |* 8 | SORT JOIN | | 1 | 11 | 3 (34)| 00:00:01 | | 9 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 11 | 2 (0)| 00:00:01 | |* 10 | INDEX RANGE SCAN | IDX_DEPT_LOC | 1 | | 1 (0)| 00:00:01 | |* 11 | SORT JOIN | | 14 | 266 | 4 (25)| 00:00:01 | | 12 | TABLE ACCESS FULL | JOBS | 14 | 266 | 3 (0)| 00:00:01 | |* 13 | SORT JOIN | | 14 | 98 | 4 (25)| 00:00:01 | | 14 | TABLE ACCESS FULL | EMP_TEMP | 14 | 98 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------------------------------
与之对应的no_use_merge：
SQL> select /*+ ordered no_use_merge(d) */ * from emp e,dept d where e.deptno=d.deptno; 14 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 1123238657 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 14 | 812 |6 (0)| 00:00:01 | |* 1 | HASH JOIN | | 14 | 812 |6 (0)| 00:00:01 | | 2 | TABLE ACCESS FULL| EMP | 14 | 532 |3 (0)| 00:00:01 | | 3 | TABLE ACCESS FULL| DEPT |4 | 80 |3 (0)| 00:00:01 | ---------------------------------------------------------------------------
USE_NL:
SQL> select /*+ use_nl(e,j, d, t) */e.ename,j.job,e.sal,d.deptno from emp e,jobs j,dept d,emp_temp t where e.empno=j.empno and e.deptno=d.deptno and e.ename=t.ename and d.loc='CHICAGO' order by e.ename; 6 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 4019883924 ------------------------------------------------------------------------------------------------ | Id | Operation| Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT| | 5 | 270 | 18 (6)| 00:00:01 | | 1 | SORT ORDER BY| | 5 | 270 | 18 (6)| 00:00:01 | | 2 | NESTED LOOPS| | 5 | 270 | 17 (0)| 00:00:01 | | 3 | NESTED LOOPS | | 5 | 175 | 10 (0)| 00:00:01 | | 4 | NESTED LOOPS| | 5 | 140 | 3 (0)| 00:00:01 | | 5 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 11 | 2 (0)| 00:00:01 | |* 6 | INDEX RANGE SCAN| IDX_DEPT_LOC | 1 | | 1 (0)| 00:00:01 | | 7 | TABLE ACCESS BY INDEX ROWID| EMP | 5 | 85 | 1 (0)| 00:00:01 | |* 8 | INDEX RANGE SCAN| IDX_EMP_DEPT | 5 | | 0 (0)| 00:00:01 | |* 9 | TABLE ACCESS FULL| EMP_TEMP | 1 | 7 | 1 (0)| 00:00:01 | |* 10 | TABLE ACCESS FULL| JOBS | 1 | 19 | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------
USE_HASH:
SQL> select /*+ ordered use_hash(e,j, d, t) */e.ename,j.job,e.sal,d.deptno from emp e,jobs j,dept d,emp_temp t where e.empno=j.empno and e.deptno=d.deptno and e.ename=t.ename and d.loc='CHICAGO' order by e.ename;
6 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3131502444
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 270 | 12(9)| 00:00:01 |
| 1 | SORT ORDER BY | | 5 | 270 | 12(9)| 00:00:01 |
|* 2 | HASH JOIN | | 5 | 270 | 11(0)| 00:00:01 |
|* 3 | HASH JOIN | | 5 | 235 | 8(0)| 00:00:01 |
|* 4 | HASH JOIN | | 14 | 504 | 6(0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 14 | 238 | 3(0)| 00:00:01 |
| 6 | TABLE ACCESS FULL | JOBS | 14 | 266 | 3(0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 11 | 2(0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | IDX_DEPT_LOC | 1 | | 1(0)| 00:00:01 |
| 9 | TABLE ACCESS FULL | EMP_TEMP | 14 | 98 | 3(0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
加入ordered hint可以走出自己想要的执行计划
网页名称：OracleHint学习之三
标题路径：https://www.cdcxhl.com/article8/gsipop.html
成都网站建设公司_创新互联，为您提供服务器托管、微信小程序、App开发、网站内链、企业建站、外贸网站建设
声明：本网站发布的内容（图片、视频和文字）以用户投稿、用户转载内容为主，如果涉及侵权请尽快告知，我们将会在第一时间删除。文章观点不代表本网站立场，如需处理请联系客服。电话：028-86922220；邮箱：631063699@qq.com。内容未经允许不得转载，或转载时需注明来源： 创新互联
- 【网站设计】品牌网站设计的一般布局 2016-11-21
- 品牌网站设计怎么发挥效果？ 2022-08-10
- 如何制作自己的网站 品牌网站设计技巧 2021-05-28
- 企业品牌网站设计思路介绍 2023-01-29
- 品牌网站设计-好的用户体验更容易留住用户 2022-04-30
- 怎样做深圳品牌网站设计才有效果 2021-10-18
- 吴江高端网站建设制作品牌网站设计如何布局 2020-11-23
- 成都企业品牌网站设计的常见布局方式 2023-03-25
- 品牌网站设计中大小标题之间的色彩关系 2022-12-03
- 深圳品牌网站设计公司 2014-05-13
- 品牌网站设计对企业来说有什么意义 2021-10-27
- 品牌网站设计应该考虑的问题有哪些？ 2023-03-23