If there are many indexes that contain common fields (or for any other reason), the database optimizer cannot decide the right index to be used for a particular query, and then use a wrong index that may not be of optimal performance. From SAP Release 4.5, hints can be provided using the %_HINTS
parameter. In this recipe, we will see the syntax for specifying HINTS
within your SELECT
clause in order for a particular index to be used by the database optimizer. We will see how the hints may be specified when the underlying database is MS SQL Server.
In this recipe, we will have a small program that runs a SELECT
statement on the table ZST9_VBAK
. We will use the index (Z12
) that we created in the previous Creating Secondary Indexes in Database Tables recipe.
For creating the program containing the SELECT
clause with the HINT
parameter, proceed as follows:
P_AUFNR
is declared for taking as input an order number.myvbeln
is defined.SELECT
statement is then written. The addition %_HINTS
followed by the database name, the table, and table index name is made to the SELECT
clause.It is a very simple addition. There is a special syntax used for specifying the name of the database index to be used for the particular SELECT
statement. The name of the index we used can be taken from the index name as defined in the ABAP Dictionary. The table name and the index name are specified within TABLE <tablename> ABINDEX(<suffix>). The syntax should be proper because other than the database name check, the syntax checker does not check the index name. Therefore, if a wrong index is used, the corresponding query will not give a syntax error but the desired results will not be achieved.
In our case, we use MSSQLNT
as the database name. However, you may use other database names such as ADABAS
, AS400
, DB2
, DB6
, INFORMIX
, MSSQLNT
, and ORACLE
(depending on what is applicable to your underlying database). The code pertaining to the HINT
parameter of one database may differ from that of another.
For example, if we have an ORACLE
database, the same Z12
index may be specified in the SELECT
statement in the following manner:
Note the database name added along with the changed format for index specification.
18.191.239.48