Hello Listers, We have a delete query with a difference in plan after we
moved to 19C. It runs hundreds of thousands of times in a day. It was using
unique scan path on 11.2 and was running faster i.e. ~.0001 seconds per
execution but when we moved to 19C it's now going for an index fast full
scan path and taking ~.05 seconds/execution.
We fetched the 10053 trace for both OFE 19.1 and OFE 11.2. What we see is ,
in 11.2 the best cost comes to be 2.000464 i.e the index unique scan path.
An optimizer evaluated table scan vs index unique scan path but there was
no index FFS path cost evaluation. In the 19c trace we are seeing the new
"index FFS" access path evaluation and that cost endup as 2.000000 and thus
chosen as the best path in 19c.
So my question is,
1)If the index ffs path was not possible for delete statements and was
introduced in 19c?
2)To handle such a scenario if hinting the query is the only option?
DELETE FROM TAB1 WHERE CMP_ID = :b1 AND EP_DT = to_date(:b2,'DD-MON-YYYY');
Here an unique index TAB1_PK exists on column cmp_id, EP_DT.
11G 10053 trace:-
https://gist.github.com/oracle9999/ba46c45d4c227a7ed7f56be21c26ccfc
19C 10053 trace:-
https://gist.github.com/oracle9999/9edb233ced8bd5c6b908f5c7c7a1dc70
