Hi,
I found this together with Andi Schlögl last week. It is a heavy bug and
happens when in a merge statement no rows are inserted.
In such case the distinct keys Statistic for unique and primary keys is
updated to 0.
That results in much to high cost and cardinality estimates in joins.
I have even seen the infamous 18(E) estimates, which are indicating IMHO
an overflow in the cost and cardinality calculation.
---------------------------------------
| Rows | Bytes |TempSpc| Cost (%CPU)|
---------------------------------------
| 18E| 15E| | 18E(100)|
In addition you can see wrong estimates for the table access by index
rowid step in a nested loop. (Correct estimate should be 1).
---------------------------------------------------------------------------
| Id | Operation | Rows |
---------------------------------------------------------------------------
| 42 | TABLE ACCESS BY LOCAL INDEX ROWID | 1188M|
|* 43 | INDEX UNIQUE SCAN | 1 |
Andi has made a testcase and is detailing his results here:
https://twitter.com/AndiSchloegl/status/1429739669458231301 and here:
https://twitter.com/AndiSchloegl/status/1429739845363113988
This bug should be around since 12.2.
We just wonder if anybody has seen it before?
Thanks
Lothar
