- 3,715,756 Users
- 2,242,858 Discussions
- 7,845,559 Comments
Subquery in SELECT statement generates HORRIBLE plan, but moving to FROM clause "fixes" it
We have thousands of queries written like this:
. . .
WHEN "ADT"."SEQ_NUM_IN_ENC" IS NULL
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)
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
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 126.96.36.199 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.