在ABAP的SQL语句中写Oracle Hints

最新推荐文章于 2024-01-15 16:07:11 发布

转载最新推荐文章于 2024-01-15 16:07:11 发布 · 421 阅读

1 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/highmayor/archive/2011/09/23/2186325.html

文章标签：

#数据库 #runtime

本文详细介绍了Oracle SQL优化技巧，包括使用hint指定全表扫描或索引，以及在ABAP中如何正确地在SELECT语句中指定索引。通过实例演示了如何在不同场景下选择合适的索引，并提供了在ABAP中应用这些优化策略的方法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

①用过的两个写法：
　　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.
　　Code
　　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"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

转载于:https://www.cnblogs.com/highmayor/archive/2011/09/23/2186325.html