[20260215]关于连接提示.txt
--//再次提醒自己，连接提示仅仅写1个表。
1.环境：
SCOTT@book01p> @ ver2
==============================
PORT_STRING : x86_64/Linux 2.4.xx
VERSION : 21.0.0.0.0
BANNER : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
BANNER_FULL : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
BANNER_LEGACY : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
CON_ID : 0
PL/SQL procedure successfully completed.
2.测试：
SCOTT@book01p> SELECT /*+ USE_NL(emp,dept) */ ename,dname FROM emp,dept WHERE emp.deptno = dept.deptno;
ENAME DNAME
---------- ------------------------------
CLARK ACCOUNTING
KING ACCOUNTING
MILLER ACCOUNTING
SMITH RESEARCH
JONES RESEARCH
SCOTT RESEARCH
ADAMS RESEARCH
FORD RESEARCH
ALLEN SALES
WARD SALES
MARTIN SALES
BLAKE SALES
TURNER SALES
JAMES SALES
14 rows selected.
SCOTT@book01p> @ dpc '' outline ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 8padkbhdmrdwa, child number 0
-------------------------------------
SELECT /*+ USE_NL(emp,dept) */ ename,dname FROM emp,dept WHERE
emp.deptno = dept.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 | 308 | 10 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT | 4 | 52 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| EMP | 4 | 36 | 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"
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('21.1.0')
DB_VERSION('21.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "DEPT"@"SEL$1")
FULL(@"SEL$1" "EMP"@"SEL$1")
LEADING(@"SEL$1" "DEPT"@"SEL$1" "EMP"@"SEL$1")
USE_NL(@"SEL$1" "EMP"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("EMP"."DEPTNO"="DEPT"."DEPTNO")
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2 (U - Unused (1))
---------------------------------------------------------------------------
2 - SEL$1 / "DEPT"@"SEL$1"
U - USE_NL(emp,dept)
3 - SEL$1 / "EMP"@"SEL$1"
- USE_NL(emp,dept)
Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
61 rows selected.
--//注意看提示部分。有1个错误，最好不要这样写。
--//采用leading + use_NL最佳
SELECT /*+ leading(dept emp ) USE_NL(emp) */ ename,dname FROM emp,dept WHERE emp.deptno = dept.deptno;
SCOTT@book01p> @ dpc '' outline ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID btb316jp7m1bz, child number 0
-------------------------------------
SELECT /*+ leading(dept emp ) USE_NL(emp) */ ename,dname FROM emp,dept
WHERE emp.deptno = dept.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 | 308 | 10 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT | 4 | 52 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| EMP | 4 | 36 | 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"
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('21.1.0')
DB_VERSION('21.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "DEPT"@"SEL$1")
FULL(@"SEL$1" "EMP"@"SEL$1")
LEADING(@"SEL$1" "DEPT"@"SEL$1" "EMP"@"SEL$1")
USE_NL(@"SEL$1" "EMP"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("EMP"."DEPTNO"="DEPT"."DEPTNO")
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2
---------------------------------------------------------------------------
1 - SEL$1
- leading(dept emp )
3 - SEL$1 / "EMP"@"SEL$1"
- USE_NL(emp)
Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
--//如果写成如下,执行计划并不选择nested loop。
SELECT /*+ USE_NL(emp) */ ename,dname FROM emp,dept WHERE emp.deptno = dept.deptno;
SCOTT@book01p> @ dpc '' outline,adaptive ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 9v2us3kvdfj50, child number 0
-------------------------------------
SELECT /*+ USE_NL(emp) */ ename,dname FROM emp,dept WHERE emp.deptno =
dept.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 | 308 | 6 (0)| 00:00:01 | 1744K| 1744K| 949K (0)|
|- 2 | NESTED LOOPS | | 14 | 308 | 6 (0)| 00:00:01 | | | |
|- 3 | NESTED LOOPS | | | | | | | | |
|- 4 | STATISTICS COLLECTOR | | | | | | | | |
| 5 | TABLE ACCESS FULL | EMP | 14 | 126 | 3 (0)| 00:00:01 | | | |
|- * 6 | INDEX UNIQUE SCAN | PK_DEPT | | | | | | | |
|- 7 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 13 | 3 (0)| 00:00:01 | | | |
| 8 | TABLE ACCESS FULL | DEPT | 4 | 52 | 3 (0)| 00:00:01 | | | |
-----------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1
5 - SEL$1 / "EMP"@"SEL$1"
6 - SEL$1 / "DEPT"@"SEL$1"
7 - SEL$1 / "DEPT"@"SEL$1"
8 - SEL$1 / "DEPT"@"SEL$1"
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('21.1.0')
DB_VERSION('21.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "EMP"@"SEL$1")
FULL(@"SEL$1" "DEPT"@"SEL$1")
LEADING(@"SEL$1" "EMP"@"SEL$1" "DEPT"@"SEL$1")
USE_HASH(@"SEL$1" "DEPT"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
6 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
5 - SEL$1 / "EMP"@"SEL$1"
U - USE_NL(emp)
Note
-----
- this is an adaptive plan (rows marked '-' are inactive)
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
68 rows selected.
--//由于adaptive plan 存在，选择hash join连接。可以在11g下测试也是选择hash join，21c加入Hint Report，还很容易看出问题在
--//那里，以前的版本就没有这么幸运了。
--//总之，最佳的方式加入leading + use_nl,use_nl里面的表选择被驱动表。
--//有时候优化加提示没有注意这些细节，浪费不必要的时间。
[20260215]关于连接提示.txt
-
lfree
2026-02-16 21:18:48 -
Oracle
- 原创