[20250307]关于use_hash提示遇到的问题.txt

--//前一段时间遇到的问题，当时觉得奇怪,做一个简单例子说明问题：

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_hash(dept emp) */ dname,ename,loc from dept,emp where dept.deptno = emp.deptno;

SCOTT@book01p> @ dpc '' 'outline' ''
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         |      |     12 |   348 |     6   (0)| 00:00:01 |  1399K|  1399K| 1018K (0)|
|   2 |   TABLE ACCESS FULL| DEPT |      4 |    80 |     3   (0)| 00:00:01 |       |       |          |
|   3 |   TABLE ACCESS FULL| EMP  |     12 |   108 |     3   (0)| 00:00:01 |       |       |          |
-------------------------------------------------------------------------------------------------------
....
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_HASH(@"SEL$1" "EMP"@"SEL$1")
      END_OUTLINE_DATA
  */

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_hash(dept emp)

   3 -  SEL$1 / "EMP"@"SEL$1"
           -  use_hash(dept emp)

--//开始看到提示部分感觉很奇怪，不理解，实际上真实的提示应该是USE_HASH(@"SEL$1" "EMP"@"SEL$1")，里面写入被驱动表就可以了。
--//对于这类情况要优化最好加入leading提示。