Posted: Mon Aug 04, 2008 2:20 pm Post subject: Note 129385 - Database hints in Open SQL
Symptom
A note is to be provided to the optimizer of a database or the R/3 database interface to process an Open SQL statement as efficiently as possible.
Reason and Prerequisites
Complex database statements or large numbers of secondary indexes that can be very similar make it difficult for the database optimizer and the R/3 database interface to process most efficiently.
Solution
In some database systems, you can expressly influence decisions made by database optimizer by what are known as hints. As of Release 4.5, you can also enter hints for Open SQL statements.
You can also evaluate some hint entries directly with the database interface of the R/3 kernel, which will then also be available for database systems that do not know any database hints.
This note describes how to enter database hints for Open SQL statements. Entering hints for Native SQL statements is not described in this note.
The following generally apply:
Open SQL hints are only evaluated for transparent tables or views on these tables. They are not evaluated for a statement on the R/3 database buffer or for pool or cluster tables.
Only use Open SQL hints as a means of correction if there is no other solution available. You may only use Open SQL hints following consultation with an experienced application developer and you must exercise caution when using them.
Users themselves take full responsibility for deciding if a hint is useful and if it is entered correctly.
You can only evaluate hints at runtime and they cannot be checked with ABAP syntax analysis. Incorrect entries can cause a runtime error. Invalid hints may also be ignored, depending on the database.
Hints are always specific to just one release of just one database system.
Therefore, ABAP program examples are not included here but rather in the database-specific notes listed below.
- Users are responsible for evaluating and adjusting, if necessary, a hint entry for each certified database release.
- Although several databases may know the same hint syntax, evaluate every database system separately to decide if a hint entry is appropriate.
Also, hints are specific for one system configuration and its quantity structure. For this reason only use hints in the standard SAP system with caution.
A database hint (for the purposes of this note) is entered for one Open SQL statement or one subquery and it also only affects these. A hint does not have any functional effects in this case. This means that although it influences the process duration, it does not influence the resulting set of a statement. For this reason you cannot usually check in the program if a hint was ever evaluated.
Some database systems provide an index hint, but SAP application developers need a means of specifying, for instance, the instruction: "As the access path to table XXX use the index with the SAP ID M."
Check the database-specific notes for hints about how you can specify index hints for your underlying database system. In the case of MSSQL for example, a direct specification of the SAP index hint is supported for Open SQL. See the corresponding note for more information.
This note describes the attributes of Open SQL hints. These attributes remain constant in all database systems. Database-specific notes, which supplement this description, determine what hints you use, in what situation and how you can enter them.
Do not enter hints for databases that are not listed here:
130480 Database hints in Open SQL for Oracle
133381 Database hints in Open SQL for MS SQL Server
150037 Database hints in Open SQL for DB6 (DB2 UDB)
152913 Database hints in Open SQL for Informix
162034 DB2/390: Database hints in Open SQL
485420 iSeries: Database hints for Open SQL/Native SQL
652096 Database hints in Open SQL for SAPDB / MaxDB
Open SQL notation
In the clause %_HINTS, hints are entered as the last clause of a SELECT, DELETE, UPDATE Open SQL statement or subquery. Hints cannot be entered as SELECT, DELETE or UPDATE in work area statements or other statements:
SELECT [..] FROM [..]
WHERE [..] GROUP BY [..] HAVING [..]
ORDER BY [..]
%_HINTS <selector> '<text>' <selector> '<text>' [..] .
The %_HINTS clause consists of a list of pairs that consist of a database selector and the actual hint text.
Selectors are keywords and are therefore not included in '..': ADABAS, AS400, DB2, DB6, INFORMIX, MSSQLNT, ORACLE
In an actual system, only hint texts with a suitable selector are considered. A hint text is ignored, if it is empty after the evaluation of the substitutions described below or if it only contains blanks.
A hint text is either a '...' literal character or a character area. If changeable character fields or (FORM-) parameters are entered, the entire statement will become dynamic. In this case a statement cache is more difficult to use.
You can enter several hint texts per database. The database-specific note mentioned above describes if you can process several hint texts and if so, how you do so.
Text substitutions
The database interface evaluates hint texts before forwarding to a database and carries out some substitutions in these texts.These substitutions are a pure text operation without consideration of the surrounding text. In each case two '&' symbols are used for the compound. Invalid or unknown substitutions result in a runtime error. - && --> &
Technical addition: So that you can enter the character '& ' in a hint text
&TABLE [[<block>,] <table>]& --> <table name>
is replaced with the name which uses the database interface for the entered table opposite the database. Application developers generally do not know this, since the R/3 database interface generates synthetic table aliases of its own accord.
<Table> is the number of a table in the FROM clause, counted from 1, if the FROM clause is read from 'left to right.'The default is '1', that is the first table of the FROM clause.
<Block> specifies which FROM clause is meant: The default '0' specifies the FROM clause of the SELECT..FROM..WHERE block where the hint is also entered. In a subquery '-1', '-2' ... describe the FROM clause of the next outer block along the nesting of blocks/subqueries.
Due to the defaults mentioned above, &TABLE& is synonymous with &TABLE 1& and &TABLE 0,1&.
&table [[<block>,] <table>]& --> < table name>
The same as &TABLE& where upper case is replaced with lower case in the substitution result.
Hints of the database interface
These hints are evaluated by the R/3 database interface itself and are therefore immediately recorded for all databases if they were implemented for this database. The database-specific note mentioned above discusses this topic further. Evaluate each database system separately and independent of the standardized syntax and significance, to decide if an entry is appropriate.
DBI hints are recorded as text substitutions, which are replaced in each case by an empty string, which means they are deleted. For this reason, they are only entered so that they can influence the database interface and are not visible for the database itself.
DBI hints can only be entered for the external block SELECT..FROM..WHERE <(>&<)>REPARSE&
If possible, the SQL statement is translated and optimized again while bypassing caches. This is intended to be an adjustment to a considerably altered situation, such as a table where many records were inserted. The hint does not support the new optimization by supplying current data or optimizer statistics. Users must do that themselves.
&SUBSTITUTE LITERALS&
Explicit request to the database interface, to insert all literal values directly into the statement text. Literals are direct literals in the ABAP program text as well as global ABAP constants but not constant (FORM-)parameters. This is to supply information to the optimizer which makes it possible to utilize statistics via the value distributions.
As of Release 4. 5, the database interface for some databases with more complex statements will attempt to insert literals directly into the statement text. You should therefore verify in the SQL trace (Transaction ST05) if this require this hint.
Depending on the database, it is possible that all ABAP literals are not passed on to the database as a literal. As an example, such restrictions can apply to certain data types.
&SUBSTITUTE VALUES&
are similar to &SUBSTITUTE LITERALS& and the explicit request to insert values directly in the statement text, to allow the optimizer to better use statistics for value distributions for optimization.
In contrast to &SUBSTITUTE LITERALS&, however, all values are inserted and not only literals. As a result, every call of the Open SQL statement in question generates a new statement and as a result a statement cache can no longer be used.
In the case of &SUBSTITUTE LITERALS& for instance, there may be database-dependent restrictions whose values are actually passed on to the database as a literal.
Related Notes
1069739 - CIC: Poor performing access to data environ - bill doc data
1009808 - MM_MATNR: Performance problems during reading of EQUI or OBJ
998448 - MM_MATNR: Performance problems when reading the AFIH
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum You cannot attach files in this forum You can download files in this forum
All product names are trademarks of their respective companies. SAPNET.RU websites are in no way affiliated with SAP AG. SAP, SAP R/3, R/3 software, mySAP, ABAP, BAPI, xApps, SAP NetWeaver and any other are registered trademarks of SAP AG. Every effort is made to ensure content integrity. Use information on this site at your own risk.
