ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > [20200718]注意sql hint写法2.txt

[20200718]注意sql hint写法2.txt

原创 Oracle 作者：lfree 时间：2020-07-19 10:12:29 0 删除 编辑

[20200718]注意sql hint写法2.txt

--//更正链接http://blog.itpub.net/267265/viewspace-2642961/的错误.

1.环境:
SCOTT@test01p> @ ver1
PORT_STRING                    VERSION        BANNER                                                                               CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0           12.2.0.1.0     Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0

2.测试:
SCOTT@test01p>  select /*+ use_nl(emp ) */ * from dept ,emp where dept.deptno=emp.deptno;
SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  f6v18db4wf38v, child number 0
-------------------------------------
 select /*+ use_nl(emp ) */ * from dept ,emp where
dept.deptno=emp.deptno
Plan hash value: 1123238657
-------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |        |       |     6 (100)|          |       |       |          |
|*  1 |  HASH JOIN         |      |     14 |   812 |     6   (0)| 00:00:01 |  1055K|  1055K|  719K (0)|
|   2 |   TABLE ACCESS FULL| EMP  |     14 |   532 |     3   (0)| 00:00:01 |       |       |          |
|   3 |   TABLE ACCESS FULL| DEPT |      4 |    80 |     3   (0)| 00:00:01 |       |       |          |
-------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - SEL$1 / EMP@SEL$1
   3 - SEL$1 / DEPT@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")

--//emp作为驱动表,但是连接不是nested loop,而是hash join.应该写成如下:

SCOTT@test01p> select /*+ use_nl(emp dept) */ * from dept ,emp where dept.deptno=emp.deptno;
SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  gk5d852xxj4b5, child number 0
-------------------------------------
select /*+ use_nl(emp dept) */ * from dept ,emp where
dept.deptno=emp.deptno
Plan hash value: 4192419542
----------------------------------------------------------------------------
| Id  | Operation          | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |        |       |    10 (100)|          |
|   1 |  NESTED LOOPS      |      |     14 |   812 |    10   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEPT |      4 |    80 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| EMP  |      4 |   152 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - SEL$1 / DEPT@SEL$1
   3 - SEL$1 / EMP@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("DEPT"."DEPTNO"="EMP"."DEPTNO")

--//这样写才是走nested loop.但是连接顺序是dept在前,emp在后.

select /*+ use_nl(dept emp ) */ * from dept ,emp where dept.deptno=emp.deptno;
SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  3t7v5jv2dwbpj, child number 0
-------------------------------------
select /*+ use_nl(dept emp ) */ * from dept ,emp where
dept.deptno=emp.deptno
Plan hash value: 4192419542
----------------------------------------------------------------------------
| Id  | Operation          | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |        |       |    10 (100)|          |
|   1 |  NESTED LOOPS      |      |     14 |   812 |    10   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEPT |      4 |    80 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| EMP  |      4 |   152 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------
--//连接顺序是dept在前,emp在后.也就是要控制连接顺序必须使用leadiing或者order提示.

select /*+ leading(emp dept ) use_nl(dept emp ) */ * from dept ,emp where dept.deptno=emp.deptno;
SCOTT@test01p> @ dpc '' advanced
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  0nvmx1zbbdvbt, child number 0
-------------------------------------
select /*+ leading(emp dept ) use_nl(dept emp ) */ * from dept ,emp
where dept.deptno=emp.deptno
Plan hash value: 3625962092
-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |        |       |    17 (100)|          |
|   1 |  NESTED LOOPS                |         |     14 |   812 |    17   (0)| 00:00:01 |
|   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)|          |
|   5 |   TABLE ACCESS BY INDEX ROWID| DEPT    |      1 |    20 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

select /*+ leading(dept emp ) use_nl(dept emp ) */ * from dept ,emp where dept.deptno=emp.deptno;
SCOTT@test01p> @ dpc '' advanced
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  cfc054wzj13c1, child number 0
-------------------------------------
select /*+ leading(dept emp ) use_nl(dept emp ) */ * from dept ,emp
where dept.deptno=emp.deptno

Plan hash value: 4192419542

----------------------------------------------------------------------------
| Id  | Operation          | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |        |       |    10 (100)|          |
|   1 |  NESTED LOOPS      |      |     14 |   812 |    10   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEPT |      4 |    80 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| EMP  |      4 |   152 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------

3.走hash join以及merge join也存在类似的情况?

SCOTT@test01p> select /*+ use_hash( emp dept) */ * from dept ,emp where dept.deptno=emp.deptno;
SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  82kkvfn2wfm9u, child number 0
-------------------------------------
select /*+ use_hash( emp dept) */ * from dept ,emp where
dept.deptno=emp.deptno
Plan hash value: 615168685
-------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |        |       |     6 (100)|          |       |       |          |
|*  1 |  HASH JOIN         |      |     14 |   812 |     6   (0)| 00:00:01 |  1398K|  1398K| 1056K (0)|
|   2 |   TABLE ACCESS FULL| DEPT |      4 |    80 |     3   (0)| 00:00:01 |       |       |          |
|   3 |   TABLE ACCESS FULL| EMP  |     14 |   532 |     3   (0)| 00:00:01 |       |       |          |
-------------------------------------------------------------------------------------------------------

SCOTT@test01p> select /*+ leading(emp dept) use_hash( emp dept) */ * from dept ,emp where dept.deptno=emp.deptno;
SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  4uwf6gq3vqwt6, child number 0
-------------------------------------
select /*+ leading(emp dept) use_hash( emp dept) */ * from dept ,emp
where dept.deptno=emp.deptno
Plan hash value: 1123238657
-------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |        |       |     6 (100)|          |       |       |          |
|*  1 |  HASH JOIN         |      |     14 |   812 |     6   (0)| 00:00:01 |  1098K|  1098K|  699K (0)|
|   2 |   TABLE ACCESS FULL| EMP  |     14 |   532 |     3   (0)| 00:00:01 |       |       |          |
|   3 |   TABLE ACCESS FULL| DEPT |      4 |    80 |     3   (0)| 00:00:01 |       |       |          |
-------------------------------------------------------------------------------------------------------

SCOTT@test01p> select /*+ leading(emp dept) use_merge( emp dept) */ * from dept ,emp where dept.deptno=emp.deptno;
SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  a1933csmkkyd3, child number 0
-------------------------------------
select /*+ leading(emp dept) use_merge( emp dept) */ * from dept ,emp
where dept.deptno=emp.deptno
Plan hash value: 3406566467
--------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |        |       |     8 (100)|          |       |       |          |
|   1 |  MERGE JOIN         |      |     14 |   812 |     8  (25)| 00:00:01 |       |       |          |
|   2 |   SORT JOIN         |      |     14 |   532 |     4  (25)| 00:00:01 |  2048 |  2048 | 2048  (0)|
|   3 |    TABLE ACCESS FULL| EMP  |     14 |   532 |     3   (0)| 00:00:01 |       |       |          |
|*  4 |   SORT JOIN         |      |      4 |    80 |     4  (25)| 00:00:01 |  2048 |  2048 | 2048  (0)|
|   5 |    TABLE ACCESS FULL| DEPT |      4 |    80 |     3   (0)| 00:00:01 |       |       |          |
--------------------------------------------------------------------------------------------------------

SCOTT@test01p> select /*+ leading( dept emp) use_merge( emp dept) */ * from dept ,emp where dept.deptno=emp.deptno;
SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  3jz1v9y326xvp, child number 0
-------------------------------------
select /*+ leading( dept emp) use_merge( emp dept) */ * from dept ,emp
where dept.deptno=emp.deptno
Plan hash value: 844388907
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |        |       |     6 (100)|          |       |       |          |
|   1 |  MERGE JOIN                  |         |     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 |  2048 |  2048 | 2048  (0)|
|   5 |    TABLE ACCESS FULL         | EMP     |     14 |   532 |     3   (0)| 00:00:01 |       |       |          |
--------------------------------------------------------------------------------------------------------------------

4.总结:
--//实际上在链接还做了一些例子:
--//http://blog.itpub.net/267265/viewspace-2122782/=>[20160730]hint 冲突.txt
--//这种细节问题总是不小心就会犯.
--//总之要控制执行计划,最好使用leading,use_nl()里面的表如果仅仅1个参数作为被驱动表才有效.
--//不行加入2个参数.避免执行计划与提示冲突.

来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/267265/viewspace-2705445/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： [20200711]关于左右连接的问题.txt
下一篇： [20200801]sql hint冲突.txt
请登录后发表评论 登录
全部评论
lfree
专家姓名：张文胜
熟悉oracle相关技术，擅长sql优化，rman备份与恢复，熟悉linux shell编程。

注册时间：2008-01-03

  • 博文量
    4017
  • 访问量
    7991604

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us 编辑准则

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593