[20250717]UNNEST提示Correlation column in SELECT, GROUP BY, or ORDER BY.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.测试例子：
--//生产系统sql语句相对复杂，写一个简单例子：
$ cat g7.txt
select  * from dept
  where not exists (
    select /*+ &1 */ &2
      from emp
      where dept.deptno = emp.deptno
  ) ;
--//注意执行需要2个参数。

4.测试：
SCOTT@book01p> @ sl all
alter session set statistics_level = all;
Session altered.

SCOTT@book01p> @ g7.txt '' dname
    DEPTNO DNAME                          LOC
---------- ------------------------------ -------------
        40 OPERATIONS                     BOSTON
--//注意第2个字段dname来自表dept。

SCOTT@book01p> @ dpc '' outline ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  1n80f4jpyrd29, child number 0
-------------------------------------
select  * from dept   where not exists (     select /*+  */ dname
from emp       where dept.deptno = emp.deptno   )
Plan hash value: 3547749009
---------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |       |     9 (100)|          |      1 |00:00:00.01 |      28 |
|*  1 |  FILTER            |      |      1 |        |       |            |          |      1 |00:00:00.01 |      28 |
|   2 |   TABLE ACCESS FULL| DEPT |      1 |      4 |    80 |     3   (0)| 00:00:01 |      4 |00:00:00.01 |       7 |
|*  3 |   TABLE ACCESS FULL| EMP  |      4 |      2 |     6 |     2   (0)| 00:00:01 |      3 |00:00:00.01 |      21 |
---------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - SEL$1 / "DEPT"@"SEL$1"
   3 - SEL$2 / "EMP"@"SEL$2"
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$2")
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "DEPT"@"SEL$1")
      PQ_FILTER(@"SEL$1" SERIAL)
      FULL(@"SEL$2" "EMP"@"SEL$2")
      END_OUTLINE_DATA
  */
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter( IS NULL)
   3 - filter("EMP"."DEPTNO"=:B1)
--//执行计划使用nest，id=1采用FILTER，导致执行效率低下，4次全表扫描emp表，逻辑读28。

--//如果参数2使用常量，注也可以使用emp的某个字段，效果一样。
SCOTT@book01p> @ g7.txt '' 1
    DEPTNO DNAME                          LOC
---------- ------------------------------ -------------
        40 OPERATIONS                     BOSTON

SCOTT@book01p> @ dpc '' outline ''

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  4t34fkagctx63, child number 0
-------------------------------------
select  * from dept   where not exists (     select /*+  */ 1
from emp       where dept.deptno = emp.deptno   )
Plan hash value: 1353548327
-------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |      1 |        |       |     6 (100)|          |      1 |00:00:00.01 |       8 |       |       |          |
|   1 |  MERGE JOIN ANTI             |         |      1 |      1 |    23 |     6  (17)| 00:00:01 |      1 |00:00:00.01 |       8 |       |       |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |      1 |      4 |    80 |     2   (0)| 00:00:01 |      4 |00:00:00.01 |       2 |       |       |          |
|   3 |    INDEX FULL SCAN           | PK_DEPT |      1 |      4 |       |     1   (0)| 00:00:01 |      4 |00:00:00.01 |       1 |       |       |          |
|*  4 |   SORT UNIQUE                |         |      4 |     12 |    36 |     4  (25)| 00:00:01 |      3 |00:00:00.01 |       6 |  2048 |  2048 | 2048  (0)|
|   5 |    TABLE ACCESS FULL         | EMP     |      1 |     12 |    36 |     3   (0)| 00:00:01 |     14 |00:00:00.01 |       6 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$5DA710D3
   2 - SEL$5DA710D3 / "DEPT"@"SEL$1"
   3 - SEL$5DA710D3 / "DEPT"@"SEL$1"
   5 - SEL$5DA710D3 / "EMP"@"SEL$2"
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$5DA710D3")
      UNNEST(@"SEL$2")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      INDEX(@"SEL$5DA710D3" "DEPT"@"SEL$1" ("DEPT"."DEPTNO"))
      FULL(@"SEL$5DA710D3" "EMP"@"SEL$2")
      LEADING(@"SEL$5DA710D3" "DEPT"@"SEL$1" "EMP"@"SEL$2")
      USE_MERGE(@"SEL$5DA710D3" "EMP"@"SEL$2")
      END_OUTLINE_DATA
  */
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
       filter("DEPT"."DEPTNO"="EMP"."DEPTNO")
--//不管连接方式如何，逻辑读下降许多。

5.强制使用提示unnest看看：

SCOTT@book01p> @ g7.txt 'unnest' dname
    DEPTNO DNAME                          LOC
---------- ------------------------------ -------------
        40 OPERATIONS                     BOSTON

SCOTT@book01p> @ dpc '' outline ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  8rbqcqwvytd2b, child number 0
-------------------------------------
select  * from dept   where not exists (     select /*+ unnest */ dname
      from emp       where dept.deptno = emp.deptno   )
Plan hash value: 3547749009
---------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |       |     9 (100)|          |      1 |00:00:00.01 |      28 |
|*  1 |  FILTER            |      |      1 |        |       |            |          |      1 |00:00:00.01 |      28 |
|   2 |   TABLE ACCESS FULL| DEPT |      1 |      4 |    80 |     3   (0)| 00:00:01 |      4 |00:00:00.01 |       7 |
|*  3 |   TABLE ACCESS FULL| EMP  |      4 |      2 |     6 |     2   (0)| 00:00:01 |      3 |00:00:00.01 |      21 |
---------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - SEL$1 / "DEPT"@"SEL$1"
   3 - SEL$2 / "EMP"@"SEL$2"
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$2")
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "DEPT"@"SEL$1")
      PQ_FILTER(@"SEL$1" SERIAL)
      FULL(@"SEL$2" "EMP"@"SEL$2")
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter( IS NULL)
   3 - filter("EMP"."DEPTNO"=:B1)
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
   3 -  SEL$2
         U -  unnest / Correlation column in SELECT, GROUP BY, or ORDER BY
53 rows selected.

--//注意看提示：U -  unnest / Correlation column in SELECT, GROUP BY, or ORDER BY
--//oracle认为not exist 查询里面存在不相干的列，导致提示unnest无效。

--//更正该问题很简单，直接使用1个常量即可。