Skip to Content
author's profile photo Former Member
Former Member

Force use of an INDEX in ABAP

Is there a way to force the use of an index for a select statement in ABAP?

Is it possible ? Maybe with EXEC SQL . . .

Here find a trace

SELECT STATEMENT ( Estimated Costs = 28.747 , Estimated #Rows = 9 )

5 2 TABLE ACCESS BY INDEX ROWID EDIDC

( Estim. Costs = 28.747 , Estim. #Rows = 9 )

1 INDEX RANGE scan EDIDC~3

( Estim. Costs = 1.207 , Estim. #Rows = 269.667 )

As we can see sql optimizer choose index 3 but I though index 1 is better....

regards,

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

4 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Dec 14, 2005 at 10:09 AM

    Hi,

    You can force the use of index you want with the statement

    %_HINTS oracle 'INDEX("<index name>")'

    This works in 4.6C and onwards

    example:

    SELECT *

    INTO TABLE T_MARA

    FROM MARA

    WHERE ERDAT = SY-DATUM

    %_HINTS oracle 'INDEX("Z01")'.

    Hope this solves your problem.

    Cheers

    Satya

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Dec 28, 2005 at 02:23 PM

    How about force use of an INDEX in ABAP for MSSQL2K?

    Can we use HINT for ABAP to use a custom index for MSSQL database?

    Many thanks.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Dec 28, 2005 at 02:26 PM

    Hi Stephan,

    1. Even if it is possible using NATIVE SQL

    it is recommended not to use them.

    2. Because it would be DATABASE DEPENDENT.

    3. In future, if the database changes,

    then the program won't work.

    4. Moreover, its always better (in fact BEST)

    to use ABAP OPEN SQL.

    R/3 is meant for that only.

    This is just a personal opinion.

    Regards,

    Amit M.

    Add a comment
    10|10000 characters needed characters exceeded

    • Hi Guys,

      Just an update on this, for ECC 6.0 the select statement to force an index search will be as follows:

      SELECT * FROM SPFLI

      %_HINTS ORACLE 'INDEX("<Table Name>" "<Table Name>~<Index Id>")'

      Sample :

      SELECT * FROM SPFLI

      %_HINTS ORACLE 'INDEX("SPFLI" "SPFLI~001")'

      You can check if it is working or not by using the SQL trace, The index will be specified in the table fetch 'Explain' description.

  • author's profile photo Former Member
    Former Member
    Posted on Feb 25, 2009 at 02:22 PM

    Hi Stephan,

    If your select statement is correct you might need to redo the database table statistics for EDIDC.

    But check your select statement first. You can try to force the index selection by choosing the appropriate fields, like:

    select * from edidc where and status in s_status and mestyp = l_mestyp
    

    where s_status has all the possible values.

    explore this possibilities before using native SQL.

    regards,

    Edgar

    Add a comment
    10|10000 characters needed characters exceeded

    • Hi Guys,

      With reference to Indexes, the best way to use them is to check out all the fields that are available in the index and make your Select Query in such a way that :

      (1) The fields in the index are present in your query.

      (2) The fields are in the same order in the index and the query.

      (3) OR conditions are avoided in the same line. E.g. Where ( matnr = '10000034' OR maktx = 'BB Prod.')

      - Such statements are to be avoided.

      If in a table there are many indexes SAP will decide by itself which index to use. This decision is based on a sampling percentage. This data is filled based on your configuration and can be viewed in ST05. In case the data has not been updated, you may find that SAP will use an index which you might not find suitable. Hence a situation may come up when you are required to force an index to be used in your SELECT query.

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.