[20260215]关于连接提示.txt

[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里面的表选择被驱动表。 --//有时候优化加提示没有注意这些细节，浪费不必要的时间。