Common Optimization Techniques

Two primary methods for optimizing SQL performance in ABAP with Oracle databases:

1. Full Table Scan Directive: %_HINTS ORACLE 'FULL(table_name)'
This forces the database to perform a complete table scan.

2. Index Specification: %_HINTS ORACLE 'INDEX("table_name" "index_name")'
This directs the database to utilize a specific index for data retrieval.

Implementing Secondary Indexes

Seconadry indexes significantly improve database query performance. Since SAP version 4.5, the %_HINTS parameter enables optimizer hints for databases that support this feature.

SELECT * FROM SPFLI %_HINTS ORACLE 'INDEX("SPFLI" "SPFLI~001")' 
INTO TABLE flight_data.
ENDSELECT.

In this example, SPFLI~001 represents a secondary index on table SPFLI. To identify available indexes:

1. Access transaction SE11
2. Enter your table name
3. Navigate to the Indexes section
4. Select the appropriate index

If index SPFLI~001 contains columns CITYFROM and CITYTO, the field sequence in WHERE clauses doesn't affect hint functionality. Incorrect hints are ignored by ABAP without generating errors.

Practical Implementation Example

REPORT flight_data_report.

TABLES: spfli.
DATA: flight_records TYPE TABLE OF spfli.

SELECT * FROM spfli 
INTO TABLE flight_records
%_HINTS ORACLE 'INDEX("SPFLI" "SPFLI~001")'.

LOOP AT flight_records INTO DATA(flight).
  WRITE: / flight.
ENDLOOP.

FOR ALL ENTRIES Optimization

Database interface hints provide control over FOR ALL ENTRIES behavior. These hints are processed by the database interface rather than being passed to the underlying database.

REPORT cost_center_report.

TABLES: csks.

START-OF-SELECTION.
  SELECT * UP TO 10 ROWS FROM csks
  WHERE kokrs <> space 
    AND kostl <> space
  %_HINTS ORACLE 'INDEX("CSKS" "J")'.
  WRITE: / csks.
  ENDSELECT.

For FOR ALL ENTRIES queries:

SELECT * FROM sflight 
FOR ALL ENTRIES IN internal_table
WHERE carrid = internal_table-carrid
%_HINTS ORACLE '&prefer_in_itab_opt 1&&prefer_fix_blocking -1&'.

These optimization hints should be applied judiciously and primarily for performance troubleshooting scenarios.