Solution : https://service.sap.com/sap/support/notes/130480 (SAP Service marketplace login required)
Summary :
This SAP Note provides guidance for improving Open SQL statement efficiency through Oracle optimizer hints and R/3 database hints. It clarifies that all Oracle hints can be utilized both at statement and subquery levels, with table and index names to be specified within quotes and placeholders &TABLE&. Several hint texts for a (SELECT..FROM..WHERE) block must be combined into a single text, separated by spaces. Incorrect or conflicting hints are ignored by Oracle but still force the selection of the cost-based optimizer, except for a RULE hint, which triggers the rule_based optimizer. Additionally, hints with syntax errors remain unutilized yet are treated as comments. The note also outlines interface hints reparse, substitute literals, substitute values, effective from Release 4.5B. Examples elucidate correct syntax and hint implications, highlighting practical application while cautioning against the overuse of hints for simple statements.
Key words :
oracle ignores invalid hint entries, database interface hints &reparse& implemented, -mentioned general notes apply, 5b &substitute literals& implemented, 5b &substitute values& implemented, oracle ignores hint entries, werks = '1100'%_hints oracle 'first_rows', werks = '1100'%_hints oracle 'index, oracle optimizer ignores, oracle-specific rules
Related Notes :
| 1009808 | MM_MATNR: Performance problems during reading of EQUI or OBJ |
| 998448 | MM_MATNR: Performance problems when reading table AFIH |
| 977867 | DB6: Plan overhead costs performance |
| 932243 | Conversion from IPPE to BOM |
| 908231 | Performance in CUOB_GET_ALL_OBJECTS_OF_ROOT w/ ORACLE-DB |
| 818321 | Specified hint is not used |
| 817810 | PM-AA: Runtime problems in EQUI_CHECK_ASSET function module |
| 815486 | Rule-based optimizer no longer supported |
| 811852 | Substitute_Literals to improve access to LTAP |
| 797765 | Oracle DB: Overhead cost planning performance |
| 770471 | MM_MATNR: Performance problems reading AFVC |
| 766349 | FAQ: Oracle SQL optimization |
| 748688 | RSWWCOND: Datenbankhints |
| 687246 | Move-out: Performance problems ISU_DB_EVER_SINGLE_TRANSVER |
| 429146 | CC04, CC03: long runtime due to Full Table Scan |
| 338009 | Performance in variant configuration (ORACLE,DB2) |
| 187291 | Performance when calling up packing with ORACLE |
| 176754 | Problems with CBO and RBO |
| 131372 | CBO: Tuning of expensive SQL statements |
| 129385 | Database hints in Open SQL |
| 111017 | Performance when reading shipping units |