Oracle未像忽略左连接表那样忽略左连接子查询的优化问询

我创建了一个视图，该视图左连接了一张扩展表，同时左连接了一个子查询——该子查询的连接键同样具有唯一性，与扩展表类似。当查询该视图且不请求扩展表的列时，Oracle会正确跳过对扩展表的访问；但当不请求子查询的列时，Oracle仍会执行该子查询，这并无实际必要。请问是否有Oracle提示或其他方法，能让Oracle在非必需时不执行该子查询？

示例代码 #

CREATE TABLE ELIDE_MAIN
(
    ELIDE_KEY     VARCHAR2 (10) NOT NULL,
    MAIN_VALUE    NUMBER NOT NULL,
    CONSTRAINT ELIDE_MAIN_PK PRIMARY KEY (ELIDE_KEY)
);

CREATE TABLE ELIDE_EXTENSION
(
    ELIDE_KEY          VARCHAR2 (10) NOT NULL,
    EXTENSION_VALUE    NUMBER NOT NULL,
    CONSTRAINT ELIDE_EXTENSION_PK PRIMARY KEY (ELIDE_KEY)
);

CREATE TABLE ELIDE_CHILD
(
    ELIDE_KEY      VARCHAR2 (10) NOT NULL,
    CHILD_NO       NUMBER NOT NULL,
    CHILD_VALUE    NUMBER NOT NULL,
    CONSTRAINT ELIDE_CHILD_PK PRIMARY KEY (ELIDE_KEY, CHILD_NO)
);

INSERT INTO ELIDE_MAIN (ELIDE_KEY, MAIN_VALUE) VALUES ('AAA', 1);
INSERT INTO ELIDE_MAIN (ELIDE_KEY, MAIN_VALUE) VALUES ('BBB', 2);
INSERT INTO ELIDE_MAIN (ELIDE_KEY, MAIN_VALUE) VALUES ('CCC', 3);
INSERT INTO ELIDE_MAIN (ELIDE_KEY, MAIN_VALUE) VALUES ('DDD', 4);

INSERT INTO ELIDE_EXTENSION (ELIDE_KEY, EXTENSION_VALUE) VALUES ('AAA', 11);
INSERT INTO ELIDE_EXTENSION (ELIDE_KEY, EXTENSION_VALUE) VALUES ('BBB', 22);

INSERT INTO ELIDE_CHILD (ELIDE_KEY, CHILD_NO, CHILD_VALUE) VALUES ('AAA', 1, 100);
INSERT INTO ELIDE_CHILD (ELIDE_KEY, CHILD_NO, CHILD_VALUE) VALUES ('AAA', 2, 101);

INSERT INTO ELIDE_CHILD (ELIDE_KEY, CHILD_NO, CHILD_VALUE) VALUES ('CCC', 1, 100);
INSERT INTO ELIDE_CHILD (ELIDE_KEY, CHILD_NO, CHILD_VALUE) VALUES ('CCC', 2, 101);
INSERT INTO ELIDE_CHILD (ELIDE_KEY, CHILD_NO, CHILD_VALUE) VALUES ('CCC', 3, 300);
INSERT INTO ELIDE_CHILD (ELIDE_KEY, CHILD_NO, CHILD_VALUE) VALUES ('CCC', 4, 301);

COMMIT;

CREATE OR REPLACE VIEW VW_ELIDED_EXAMPLE
AS
    SELECT m.ELIDE_KEY,
           m.MAIN_VALUE,
           x.EXTENSION_VALUE,
           c.CHILD_VALUE_SUM
      FROM ELIDE_MAIN  m
           LEFT OUTER JOIN ELIDE_EXTENSION x ON m.ELIDE_KEY = x.ELIDE_KEY
           LEFT OUTER JOIN (  SELECT ELIDE_KEY, SUM (CHILD_VALUE) CHILD_VALUE_SUM
                                FROM ELIDE_CHILD
                            GROUP BY ELIDE_KEY) c
               ON m.ELIDE_KEY = c.ELIDE_KEY
WITH READ ONLY;

EXPLAIN PLAN FOR SELECT ELIDE_KEY, MAIN_VALUE FROM VW_ELIDED_EXAMPLE;

执行计划 #

Plan
SELECT STATEMENT  ALL_ROWSCost: 6  Bytes: 312  Cardinality: 8                   
    7 HASH GROUP BY  Cost: 6  Bytes: 312  Cardinality: 8                
        6 HASH JOIN OUTER  Cost: 5  Bytes: 312  Cardinality: 8              
            4 NESTED LOOPS OUTER  Cost: 5  Bytes: 312  Cardinality: 8        
                2 STATISTICS COLLECTOR      
                    1 TABLE ACCESS FULL TABLE AZASLOW.ELIDE_MAIN Cost: 3  Bytes: 128  Cardinality: 4  
                3 INDEX RANGE SCAN INDEX (UNIQUE) AZASLOW.ELIDE_CHILD_PK Cost: 2  Bytes: 14  Cardinality: 2     
            5 INDEX FAST FULL SCAN INDEX (UNIQUE) AZASLOW.ELIDE_CHILD_PK Cost: 2  Bytes: 42  Cardinality: 6         

从执行计划可见，查询不会访问ELIDE_EXTENSION表，但仍会执行子查询，尽管逻辑上二者都是基于唯一键的左连接。

注：我知道创建带索引的物化视图可能可行，但不愿采用这种复杂方式。

针对该问题，以下几种方法无需依赖物化视图，即可让Oracle在不需要子查询列时跳过执行：

1. 给子查询添加/*+ NO_UNNEST */提示 #

在视图的子查询中加入NO_UNNEST提示，阻止优化器将子查询展开到主查询中，让子查询保持独立的逻辑单元。这样优化器更容易识别出当不需要子查询的列时，可以跳过对应的连接和计算。

修改后的视图定义：

CREATE OR REPLACE VIEW VW_ELIDED_EXAMPLE
AS
    SELECT m.ELIDE_KEY,
           m.MAIN_VALUE,
           x.EXTENSION_VALUE,
           c.CHILD_VALUE_SUM
      FROM ELIDE_MAIN  m
           LEFT OUTER JOIN ELIDE_EXTENSION x ON m.ELIDE_KEY = x.ELIDE_KEY
           LEFT OUTER JOIN (  SELECT /*+ NO_UNNEST */ ELIDE_KEY, SUM (CHILD_VALUE) CHILD_VALUE_SUM
                                FROM ELIDE_CHILD
                            GROUP BY ELIDE_KEY) c
               ON m.ELIDE_KEY = c.ELIDE_KEY
WITH READ ONLY;

2. 将子查询转换为独立视图 #

把子查询单独封装成一个独立的视图，再在主视图中左连接这个视图。这种结构能让优化器更清晰地判断是否需要访问该视图对应的表，从而在不需要相关列时跳过连接操作。

步骤如下：

-- 创建子查询对应的独立视图
CREATE OR REPLACE VIEW VW_CHILD_SUM
AS
    SELECT ELIDE_KEY, SUM (CHILD_VALUE) CHILD_VALUE_SUM
      FROM ELIDE_CHILD
  GROUP BY ELIDE_KEY;

-- 修改主视图
CREATE OR REPLACE VIEW VW_ELIDED_EXAMPLE
AS
    SELECT m.ELIDE_KEY,
           m.MAIN_VALUE,
           x.EXTENSION_VALUE,
           c.CHILD_VALUE_SUM
      FROM ELIDE_MAIN  m
           LEFT OUTER JOIN ELIDE_EXTENSION x ON m.ELIDE_KEY = x.ELIDE_KEY
           LEFT OUTER JOIN VW_CHILD_SUM c
               ON m.ELIDE_KEY = c.ELIDE_KEY
WITH READ ONLY;

3. 使用/*+ ELIMINATE_JOIN(c) */提示（Oracle 12c+） #

如果使用Oracle 12c或更高版本，可以在查询视图时直接指定ELIMINATE_JOIN提示，明确告诉优化器尝试消除与子查询别名c相关的连接。当查询不需要子查询的列时，优化器会跳过对应的子查询执行。

查询示例：

SELECT /*+ ELIMINATE_JOIN(c) */ ELIDE_KEY, MAIN_VALUE FROM VW_ELIDED_EXAMPLE;

内容的提问来源于stack exchange，提问作者ajz