Generation of optimizer hints for Oracle
The parameter determines whether hints for the cost-based optimizer aregenerated for certain types of SQL statement.
Parameter value 0 means that no optimizer hints are generated. Hints areusually generated for other values. The values '1' / '-1' activate hintsaccording to standard parameterization. You can also specify in detail,as a parameter, when one of the supported hints is to be generated orpassed to the database.
-1/1/0 / Hint detail specification as described below
Who is allowed
Limitation for db
In the detailed form, the parameter value consists of individualcomponents separated by ",". The format
of each sub-specification is "<(><<)>name> -> <(><<)>number>", where
- "abap -> 0/1" In ABAP programs for Open SQL statements, specified hintsare either ignored or passed to the database.
- "dbif -> 0/1" The R/3 database interface analyzes hints entered for OpenSQL statements to determine whether entries were made that can beanalyzed by the interface itself. These entries are either ignored oranalyzed.
- "rule_in -> <(><<)>number>" A RULE hint is generated if the SQLstatement contains an IN clause with at least <(><<)>number> entries.
- "rule_fae -> <(><<)>number>" A RULE hint is generated if at least <(>
<<)>number> rows of a FOR ALL ENTRIES table have been processed by thiscall.
- "concat_fae -> <(><<)>number>" A USE_CONCAT hint is generated if atleast <(><<)>number> rows of a FOR ALL ENTRIES table are processed bythis call.
- "upto -> <(><<)>number>" A FIRST_ROWS hint is generated if the SQLstatement generated by the R/3 database interface contains a conditionfor the virtual ROWNUM field. In addition, an UPTO limit must be set forthe database statement, whereby a SELECT SINGLE implicitly sets thelimit to 1.
- The value "0" switches off the generation of the corresponding hints.
- For each SQL statement, multiple ABAP hints can be transferred and up totwo additional hints can be generated: FIRST_ROWS, and independently ofthis, RULE or USE_CONCAT, whereby usability is tested in the order RULE--> USE_CONCAT.
The above short forms correspond to the following details:
- 'use_hints = -1' ->'abap->1,dbif-> 1,rule_in->0,rule_fae->0
,concat_fae->0,upto->0' -> All hints are deactivated except for Open SQL
- 'use_hints = 0' ->'abap->0,dbif-> 0,rule_in->0,rule_fae->0
,concat_fae->0,upto->0' -> All hints are deactivated.
- 'use_hints = 1' -> 'abap->1, dbif->1, rule_in->10, rule_fae->1,concat_fae->0, upto->[MAXINT]' -> Open SQL hints are activated, a RULEhint is generated for large IN clauses and for FOR ALL ENTRIESstatements. In addition, a FIRST ROW hint is generated for ROWNUMstatements.