Take a step backwards on hinting, and record the results very carefully.
a) Without the patch and with no added hints - generate the plan, outline,
and aliases
b) Try the hint cardinality(@SET$632CC7ED 20) - generate the plan, outline
and aliases
Plan (b) might be the plan you want - if so you can compare it with plan(a)
and see where it varies. You could patch with just the cardinality hint, or
you could work out a minimum set of "action" hints to use as a patch
c) take the full hint set from plan (a), make sure the leading hint for
vw_nso_1 and po_lines_all puts them in the right order, get rid of the
push_pred into vw_nso_1 hint, change the use_nl hint into vw_nso_1 to be a
use_nl into po_lines_all, get rid of the full() hint on po_lines_all.
Check to see if there are any other hints that might be relevant to the
join between the two objects. Test to see if that gives you the right plan.
If it does make the WHOLE outline into an SQL patch and test what happens.
Regards
Jonathan Lewis
On Wed, 15 Nov 2023 at 15:52, Amit Saroha <eramitsaroha@xxxxxxxxx> wrote:
Hello Jonathan,
My fault I believe I messed it up when taking the tests. I dropped and
reapplied the patch, and I generated the expanded hint report once more.
Because I know we are only changing one row from the front end, the view
must run first, followed by the table.
I'm not sure if VIEW PUSH PREDICATE is causing the leading hint to fail,
but it also didn't work when I used NO PUSH PREDICATE even though it was
used but LEADING was not.
Best Regards,
AMIT
On Wed, Nov 15, 2023 at 10:40 AM Jonathan Lewis <jlewisoracle@xxxxxxxxx>
wrote:
That hint report and patch generation statement aren't consistent with
the previous output.
*3 - SEL$AC90CD92 / PO_LINE_LOCATIONS_ALL@SEL$2 U -
USE_NL_WITH_INDEX(@SEL$AC90CD92 PO_LINE_LOCATIONS_ALL@SEL$2) 5 -
SET$632CC7ED N - LEADING(@SET$632CC7ED VW_NSO_1@SEL$AC90CD92
PO_LINE_LOCATIONS_ALL@SEL$2) *
You have *@SET$632CC7ED* in the leading hint and that should be
*@SEL$AC90CD92. *
For an initial test of the patch I'd also use just use_nl() rather than
use_nl_with_index().
Regards
Jonathan Lewis
