Question Details

No question body available.

Tags

oracle-database

Answers (1)

April 29, 2025 Score: 3 Rep: 12,897 Quality: Medium Completeness: 60%

Oracle elides the join to the table because it uses the PK constraint definition to know that you can't get more than one row, so the table isn't needed to get the correct # of rows for an outer join. But unfortunately as you've discovered, it does not have the intelligence to inspect the inline block and evaluate the GROUP BY in a similar way. It insists on projecting the join key, which is preventing elision. Perhaps it is a side-effect of delaying the group by sort until after the join (which the optimizer tends to do by default), while if you force it not to with a NOMERGE hint, the VIEW operation it sticks above it seems to prevent visibility into it from the standpoint of the parent query block.

Whatever the explanation might be for why Oracle can't figure that out, there is no hint that will get it to do this, as unless it has this intelligence in the optimizer on its own it cannot risk what to its belief might be wrong results by eliding the inline block. Hints are not supposed to change results, and Oracle doesn't trust us to tell us it won't.

If you really wanted to get it to elide the work in the inline block, you need to move it into a subquery in the SELECT list. Something in the SELECT list cannot ever change the # of result rows, so if you don't ask for it as a result, it can elide it freely:

CREATE OR REPLACE VIEW VWELIDEDEXAMPLE
AS
    SELECT m.ELIDEKEY,
           m.MAINVALUE,
           x.EXTENSIONVALUE,
           (SELECT SUM (CHILDVALUE) 
              FROM ELIDECHILD cc
             WHERE cc.elidekey = m.elidekey) CHILDVALUESUM
      FROM ELIDEMAIN  m
           LEFT OUTER JOIN ELIDEEXTENSION x ON  m.ELIDEKEY = x.ELIDEKEY;

Test without referring to childvaluesum:

EXPLAIN PLAN FOR SELECT ELIDEKEY, MAINVALUE FROM VWELIDEDEXAMPLE;
SELECT  FROM TABLE(dbms_xplan.display());
|   0 | SELECT STATEMENT          |            |     8 |    56 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS STORAGE FULL| ELIDE_MAIN |     8 |    56 |     3   (0)| 00:00:01 |

Test with referring to child_value_sum:

EXPLAIN PLAN FOR SELECT ELIDE_KEY, MAIN_VALUE, CHILD_VALUE_SUM FROM VW_ELIDED_EXAMPLE;
SELECT  FROM TABLE(dbmsxplan.display())
|   0 | SELECT STATEMENT                        |                |     8 |   336 |     5  (20)| 00:00:01 |
|   1 |  MERGE JOIN OUTER                       |                |     8 |   336 |     5  (20)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID           | ELIDEMAIN     |     8 |    56 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN                      | ELIDEMAINPK  |     8 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                             |                |     2 |    70 |     3  (34)| 00:00:01 |
|   5 |    VIEW                                 | VWSSQ1       |     2 |    70 |     2   (0)| 00:00:01 |
|   6 |     HASH GROUP BY                       |                |     2 |    16 |     2   (0)| 00:00:01 |
|   7 |      TABLE ACCESS BY INDEX ROWID BATCHED| ELIDECHILD    |     6 |    48 |     2   (0)| 00:00:01 |
|   8 |       INDEX FULL SCAN                   | ELIDECHILD_PK |     6 |       |     1   (0)| 00:00:01 |

You can also use a function call instead of a subquery and it'll do the same thing - elide it if you don't ask for the value - as long as the function is in the SELECT list, not in the WHERE clause.