在ABAP的SQL语句中写Oracle Hints
来源:互联网 发布:剑灵灵女萝莉捏脸数据 编辑:程序博客网 时间:2024/05/29 17:11
在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
Consider the following example:
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:
The code was written in R/3 4.6C.
Code
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:
<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.
阅读全文
0 0
- 在ABAP的SQL语句中写Oracle Hints
- 在ABAP的SQL语句中写Oracle Hints(文章内容来自网络,加以编辑)
- 在ABAP的SQL语句中关联SAP表索引
- Oracle 10g中新的SQL optimizer hints
- 在oracle数据库中需要查询出前8条记录的sql语句怎么写
- Oracle hints语句
- magento -- 在Magento中使用自己写的SQL语句
- 在myeclipse中写sql语句的细节问题
- 在cmd命令中写oracle语句
- Oracle SQL Hints
- oracle sql hints
- Oracle SQL hints
- oracle hints SQL提示
- BIEE中oracle提示(hints)的使用
- 在Oracle中写出性能优良的SQL语句
- 在Oracle中写出性能优良的SQL语句
- 在Oracle中写出性能优良的SQL语句
- 在Oracle中跟踪后台所执行的SQL语句
- POJ-2299 Ultra-QuickSort (树状数组 离散 求逆序对数)
- 最新 PHP5.2, 5.3, 5.4, 5.5在windows安装memcached 服务端的具体方法以及各版本 php_memcache.dll 下载
- 从源码看angular/material2 中dialog模块的实现
- github中工程代码多仓库多子模块切换同步问题解决
- Spring 注解
- 在ABAP的SQL语句中写Oracle Hints
- Java中Semaphore(信号量)的使用
- Android开发必备知识:为什么说Kotlin值得一试
- 2017第10届中国国际高端饮用水产业(北京)博览会会刊(参展商名录)
- mysql单个字段区分大小写
- qq插件开发
- Java异常机制Throwable
- Android 键盘弹出头被顶上去
- vue.js在windows本地下搭建环境和创建项目