%_hints oracle - ignoring index

[davidkingrs]

Hi,

I've two copies of the same SQL statement, the only difference is the where condition.

The two statements behave completely differently though and I can see within the explain plan that the poor performing one is ignore index which it should use.

I've been experimenting with hints to try and force it to perform like the better performing version, but it seems to just ignore my hints

    select  a~matnr,
            a~satnr,
            a~bflme,
            a~matkl,
            w~wladg,
            a~tragr,
            a~meins,  "Base UOM
            coalesce( u2~umrez, u1~umrez ) as umrez, "Order Unit Numerator
            coalesce( u2~umren, u1~umren ) as umren, "Order Unit Denominator
            coalesce( u2~meinh, u1~meinh ) as meinh,  "Order UOM
            s1~deink as xs_deink,
            s2~deink as xdc_deink
      from mara as a
        inner join maw1 as w
          on a~matnr = w~matnr
        inner join marm as u1
          on a~matnr = u1~matnr
          and a~meins = u1~meinh
        left join marm as u2
          on w~matnr = u2~matnr
          and w~wausm = u2~meinh
        left join t141 as s1
          on a~mstae = s1~mmsta
        left join t141 as s2
          on a~mstav = s2~mmsta
       where a~satnr in @it_satnr
        and a~bflme = @me->gc_logvar_purch
       %_hints  oracle 'INDEX("MARA" "MARA~ZLV")'
                oracle 'INDEX("MAW1" "MAW1~0")'
                oracle 'INDEX("MARM" "MARM~0")'
                oracle 'INDEX("T141" "T141~0")'
      into table @et_mara.

The only thing I can think of is that I'm using table alias within the joins, but the hints i'm using the logical name, should i be using the alias within the hint?

Such as:

%_hints  oracle 'INDEX("a" "MARA~ZLV")'

If yes, I assume where I've joined the same table twice under different alias, i should add both to the hint?

Finally, i'm assuming that writing "MARA~0" in the hint will force it to use the primary key, as that's how it's shown within the explain plan, although this index cannot be seen as an index in DDIC.

Many thanks,

Dave

[davidkingrs · Accepted solution]

I've answered my own question through trial and error.

For reference:

• Hints must refer to the table alias if used
• If a table is aliased multiple times, a hint is required for each alias
• "MARA~0" does in fact force it to use the primary key, although you cannot see "MARA~0" in the DDIC

The corrected code is below which works exactly how I wish the optimiser had made it work:

    select  a~matnr,
            a~satnr,
            a~bflme,
            a~matkl,
            w~wladg,
            a~tragr,
            a~meins,  "Base UOM
            coalesce( u2~umrez, u1~umrez ) as umrez, "Order Unit Numerator
            coalesce( u2~umren, u1~umren ) as umren, "Order Unit Denominator
            coalesce( u2~meinh, u1~meinh ) as meinh,  "Order UOM
            s1~deink as xs_deink,
            s2~deink as xdc_deink
      from mara as a
        inner join maw1 as w
          on a~matnr = w~matnr
        inner join marm as u1
          on a~matnr = u1~matnr
          and a~meins = u1~meinh
        left join marm as u2
          on w~matnr = u2~matnr
          and w~wausm = u2~meinh
        left join t141 as s1
          on a~mstae = s1~mmsta
        left join t141 as s2
          on a~mstav = s2~mmsta
       where a~satnr in @it_satnr
        and a~bflme = @me->gc_logvar_purch
       %_hints  oracle 'INDEX("A" "MARA~ZLV")'
                oracle 'INDEX("W" "MAW1~0")'
                oracle 'INDEX("U1" "MARM~0")'
                oracle 'INDEX("U2" "MARM~0")'
                oracle 'INDEX("S1" "T141~0")'
                oracle 'INDEX("S2" "T141~0")'
      into table @et_mara.

[matt · Accepted solution]

I was going to answer, but needed to check my notes. There's a transaction where you can enter SQL in the form of the explain, and add hints etc. I forget where it is, (DB04 perhaps?), but it was very useful for getting the syntax right.