Re: Hint is not used in SQL

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(_at_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_at_gmail.com> 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_at_gmail.com>
> wrote:
>
>> That hint report and patch generation statement aren't consistent with
>> the previous output.
>>
>>
>>
>>
>>
>>
>> *3 - SEL$AC90CD92 / PO_LINE_LOCATIONS_ALL_at_SEL$2 U -
>> USE_NL_WITH_INDEX(_at_SEL$AC90CD92 PO_LINE_LOCATIONS_ALL_at_SEL$2) 5 -
>> SET$632CC7ED N - LEADING(_at_SET$632CC7ED VW_NSO_1_at_SEL$AC90CD92
>> PO_LINE_LOCATIONS_ALL_at_SEL$2) *
>>
>> You have *_at_SET$632CC7ED* in the leading hint and that should be
>> *_at_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
>>
>>
>>

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