在ABAP的SQL语句中写Oracle Hints详解编程语言
①用过的两个写法:
1、指定使用全表扫描:%_HINTS ORACLE FULL(table_name)
2、指定索引:%_HINTS ORACLE INDEX(table_name index_name)
其他Oracle Hints的写法可以参见这篇文章:Oracle Hint的用法
在SQL语句优化过程中,经常会用到hint。
②Using secondary indexes
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:
tablename ~ Index Identifier 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 emergency situations; Note 129385 goes into this. The hints described here should only be used with careful consideration.
原创文章,作者:ItWorker,如若转载,请注明出处:https://blog.ytso.com/20117.html
cgooracle相关文章
- 学习Oracle编程,开启程序设计之门(oracle编程语言)
- 使用 Oracle 语句插入记录(oracle插入sql)
- sql通过Oracle实现批量执行SQL语句(oracle批量执行)
- PL/SQLUnlock the Power of Oracle PL/SQL: 01033.(01033oracle)
- 浅析Oracle数据挖掘的优势(oracle数据挖掘)
- 查看Oracle数据库中所有表名(oracle显示表名)
- 掌握PL/SQL如何与Oracle数据库连接(plsql怎么连接oracle)
- 安装Oracle:指南与体验(oracle的安装过程)
- Exploring the Power of Bitwise Operations in Oracle SQL with the Bitwise OR Operator(oracle位或)
- 快捷高效:Oracle批量执行SQL脚本技巧指南(oracle批量执行sql脚本)
- 利用Oracle解决空字段判断问题(oracle 空字段判断)
- mdf文件导入Oracle数据库,极速简便(.mdf导入oracle)
- 实战Oracle内连 快速学习SQL语句写法(oracle内连怎么写)
- 探究Oracle中高效率SQL语句的秘诀(oracle中 的语句)
- Oracle使用Udev构建自动化设备配置管理(oracle使用udev)
- Oracle中深度挖掘多种查询方式(oracle中的各种查询)
- SQL在Oracle中如何测试SQL语句(oracle 中怎么测试)
- Oracle 18c新特性评论(oracle_01843)
- Oracle PPV一种更高效准确的定价模型(oracle ppv)
- 探索Oracle的PLB编程语言(oracle .plb)