wht is th epurpose of using oracle hints on BSEG & BKPF tables.

[former_member209914]

Hi,

wht is the difference between writing a query using/with-out using %_HINTS ORACLE on BSEG & BKPF tables.

Is there any performance issue if we write the queries using hints.

Please see the below queries.

SELECT bukrs

belnr

gjahr

FROM bkpf INTO TABLE i_bkpf

WHERE AND bukrs EQ p_bukrs

AND gjahr EQ p_gjahr

AND monat EQ p_monat

AND blart IN s_blart

AND stgrd EQ ''

%_HINTS ORACLE

'INDEX("T_00" "BKPF~0" "BKPF^0" "BKPF______0" "BKPF______0__X")'.

SELECT bukrs

belnr

gjahr

FROM bseg INTO TABLE i_bseg

WHERE bukrs EQ i_bkpf-bukrs

%_HINTS ORACLE

'INDEX("T_00" "BSEG~0" "BSEG^0" "BSEG______0" "BSEG______0__X")'.

Regards,

vinod

[Former Member]

Hi

Check this note

Note 140825 - Optimizer Hints in Open SQL

Regards

[ThomasZloch]

You should ask in , there is a few experts on these topics that just look in that forum.

I can only interpret these as forcing usage of the primary key of these tables. Did you find this code in an SAP standard program?

Thomas

[former_member194613]

> There is no need to specify index like this, as SAP is wise enough to pick the

> correct index if you give all the index fields in same order as that of index.

... Hints are not for SAP but for the database software, here Oracle. Hints should not be used generally. Only if you did encounter a problem when the database did not use the

correct index and it was possible to influence it by using a hint.

Hint usage should be explained in a comment, with data and problematic database release.

Siegfried

[Former Member]

BSEG is a cluster table, so there are no secondary inexes available to use. Unfortunately, I doubt if you would get any sort of syntax error if you tried it.

Rob

[former_member194613]

no synatx error for hints, if not 100% correct then they are just ignored.

[Former Member]

Hi

Aftern analyzing the WHERE clause, the system should use the more appropriate index for the select.

Which index to be used depends on how the fields were place in WHERE conditions:

SELECT * FROM <TABLE> WHERE FIELD1
                                                     AND FIELD2
                                                     AND FIELD3
                                                     ................. 

so the system'll try to use an index having the fields FIELD1, FIELD2 and FIELD3.

So which index to be used should be decided by the system automatically, but it sometimes needs to force an index, i.e. indicate which index has to be used.

The statament %_HINTS ORACLE is to set the index to be used for the select.

In your case:

SELECT bukrs belnr gjahr FROM bkpf INTO TABLE i_bkpf
  WHERE bukrs EQ p_bukrs
         AND gjahr EQ p_gjahr
        AND monat EQ p_monat
        AND blart IN s_blart
        AND stgrd EQ ''
  %_HINTS ORACLE
        'INDEX("T_00" "BKPF~0" "BKPF^0" "BKPF______0" "BKPF______0__X")'.

BKPF table has BUKRS, BELNR and GJAHR has key fields, so the primary index has those fields.

But in that select the fields BUKRS and GJAHR are used and BELNR is not used, so the system can't understand which index to be used and so it doesn't use any index.

To improve the performance the developer has tried to set the primary index (BKPF~0 is the primary index generated by the keys of BKPF).

The same thing was done for the BSEG select.

Max

[Former Member]

Hi,

You should avoid using the BSEG table. Instead you should try using BSID,BSAD,BSIS,BSAS,BSIK,BSAK tables.

Thanks

Nidhi

[Sathish]

Hi,

%HINTS when to use it:

1. When an Open SQL with FOR ALL ENTRIES is taking a long time for execution.

2. When the query has complex WHERE clause with OR and IN conditions (typically this is how an internal table would be split internally)

Using %HINTS would improve the performance.

When reading data from BKPF or BSEG its better to use OPEN CURSOR like below:

OPEN CURSOR WITH HOLD db_cursor FOR

SELECT (field_list) FROM bkpf

• use all keys if possible in the select query

DO.

FETCH NEXT CURSOR db_cursor

INTO CORRESPONDING FIELDS OF TABLE buf_bkpf

PACKAGE SIZE package_size.

ENDO.

The above definitely improves the performance. Its always better to have package size of 500.