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

来源：这里教程网时间：2026-03-03 15:58:38 作者：

[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个参数.避免执行计划与提示冲突.