abap insert oracle,在ABAP的SQL语句中写Oracle Hints[转]

当执行SQL时，如果有符合选择条件的INDEX存在，但是数据库并未按照INDEX来执行，那么我们可以在SQL语句中明确的写上执行的INDEX。

①用过的两个写法：

1、指定使用全表扫描：%_HINTS

ORACLE 'FULL(table_name)'

2、指定索引：%_HINTS

ORACLE 'INDEX(table_name index_name)'

其他Oracle Hints的写法可以参见这篇文章：Oracle

Hint的用法,在SQL语句优化过程中，经常会用到hint。

②Using secondary indexes

Consider the following

example:

SELECT * FROM SPFLI

%_HINTS ORACLE 'INDEX("SPFLI"

"SPFLI~001")'

.......

ENDSELECT.

In the above example, 001 is the

secondary index of the table SPFLI. It's a well-known fact that the

efficient way of retrieving data from the database tables is by

using secondary indexes. Many database vendors provide the

optimizer hints for the same. From SAP v4.5, optimizer hints can be

provided by the %_HINTS parameter. This is dependent on the

database systems that support optimizer hints. The point to be

noted here is these optimizer hints are not standardized by the SQL

standards. Each database vendor is free to provide the optimizer

hints.

Now to know which index to use for our table:

1. Go to SE11 and there specify the table name

2. Now from the menu, goto --> indexes

3. select the required index.

Now suppose that the identifier 001

represents a non-unique secondary index comprising of the columns

CITYFROM and CITYTO. The index name should be defined

as: ~like SPFLI~001 in the above example.The

sequence of fields in the WHERE condition is of no relevance in

using this optimizers index. If you specify hints incorrectly, ABAP

ignores them but doesn't return a syntax error or runtime error.The

code was written in R/3 4.6C.

Consider the following

example: REPORT Suresh_test.

TABLES: spfli.

DATA : t_spfli LIKE spfli OCCURS 0

WITH HEADER LINE.

SELECT * FROM

spfli

INTO TABLE

t_spfli

%_HINTS

ORACLE 'INDEX("SPFLI" "SPFLI~001")'.

LOOP AT

t_spfli.

WRITE

:/ t_spfli.

ENDLOOP.

③ABAP--如何在SELECT语句中指定索引(example)

report z_generic_test_program

.

tables: csks.

start-of-selection.

select * up to 10

rows

from

csks

where

kokrs <> space

and kostl

<>

space

%_hints

oracle

'index(csks"csks~J")'.

write:

/ csks.

endselect.

④Control over FOR ALL ENTRIES Hints Under the heading

Database Interface Hints, Note 129385 describes the options you

have for influencing the database interface by entering hints. The

hints are evaluated in the database interface itself and are not

passed on to the database. Starting with kernel Release 4.6B all

the above mentioned FOR ALL ENTRIES parameters can be set via such

a hint for a single statement. In the

example:

SELECT *

FROM

[..]

FOR

ALL ENTRIES IN [..]

WHERE

[..]

%_HINTS

ORACLE '&prefer_in_itab_opt 1&&prefer_fix_blocking

-1&'.

This way, the boolean parameter

'prefer_in_itab_opt' is explictly set and the boolean parameter

'prefer_fix_blocking' is set to its default value. FOR ALL ENTRIES

hints, like hints are generally only used as a a corrective device

in emergency situations.

转自：http://flashlighten.blog.163.com/blog/static/14854530720109166478669