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

[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提示。