[20250717]UNNEST提示Correlation column in SELECT, GROUP BY, or ORDER BY.txt

[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个常量即可。