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

lfree發表於2020-07-19

[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部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-2705445/,如需轉載,請註明出處,否則將追究法律責任。

相關文章