Re: Slow Update

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_at_gmail.com> 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
>

--
http://www.freelists.org/webpage/oracle-l