Subquery in SELECT statement generates HORRIBLE plan, but moving to FROM clause "fixes" it

Bob Bryla

We have thousands of queries written like this:

SELECT

. . .

  "ACCOUNT"."RECUR_STS_HA_C",

  CASE

    WHEN "ADT"."SEQ_NUM_IN_ENC" IS NULL

    THEN NULL

    ELSE

      (SELECT name.NAME

      FROM ADT prev

      LEFT OUTER JOIN PAT_CLASS name

      ON prev.PAT_CLASS_C       = name.ADT_PAT_CLASS_C

      WHERE prev.PAT_ENC_CSN_ID = "ADT"."PAT_ENC_CSN_ID"

        AND prev.SEQ_NUM_IN_ENC   = ("ADT"."SEQ_NUM_IN_ENC"-1)

      )

  END

FROM . . .

The column value is calculated from a subquery. Testing these reports on 18c/19c shows that almost none of these report queries even finish running after 24 hours; the plan shows what is going on with that subquery:

Testing a "rewrite" like this gives us an excellent plan, and runs even faster than on previous versions:

. . .

LEFT OUTER JOIN

    (SELECT PREV.PAT_ENC_CSN_ID, PREV.SEQ_NUM_IN_ENC, NAME.NAME

     FROM ADT PREV

        LEFT OUTER JOIN PAT_CLASS NAME

           ON PREV.PAT_CLASS_C = NAME.ADT_PAT_CLASS_C) PSN

    ON ADT.PAT_ENC_CSN_ID=PSN.PAT_ENC_CSN_ID

       AND (ADT.SEQ_NUM_IN_ENC-1) = PSN.SEQ_NUM_IN_ENC

. . .

And now the execution plan shows a much better shape on this step:

The query finishes in about the same time it did in previous releases.

We will not be able to set OPTIMIZER_FEATURES_ENABLE to 12.2.0.1 locally or globally, nor can we rewrite all of these queries, it will not be feasible (it will take 1000s of hours). We just want to know what the optimizer is doing "wrong" or some other global method to get the optimizer to "move" that subquery and give us the better plan. Thanks.