The optimizer has unnested the "union all" IN subquery - which is probably
a very good idea since you're probably expecting to update only a few items
(normally).
The problem is that it's then chosen a full tablescan and hash join to get
back to the po_line_locations_all table when it probably should have done a
nested loop join using an index on line_location_id.
The error is due to a massive cardinality estimate that appears at
operation 16 (2,337K) from an estimate 1 index entry at operation 1. That
suggests a stats problem with that specific index - is the release_id a
column with a very skewed distribution?
If you can fix that bit of the problem you should see the nested loop and
suitable performance - if not you'll have to hint the code into a nested
loop join (possibly with an SQL Patch).
Regards
Jonathan Lewis
On Thu, 2 Nov 2023 at 19:38, Amit Saroha <eramitsaroha@xxxxxxxxx> wrote:
Hi All,
We have an issue where users are taking a long time to cancel a purchase
order line. Using AH data, I was able to identify one of the queries that
was taking a while in this procedure. I've attached the monitoring report
for your perusal, and I ask that you let me know if you have any
suggestions on how to make the query run better.
I appreciate your support and assistance in advance.
Best Regards,
AMIT
