在ABAP的SQL语句中写Oracle Hints

本文详细介绍了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

以下代码怎么定义的查询,可以怎么优化查询速度: *&---------------------------------------------------------------------* *& 包含 ZMM_REP_001_SCREEN_1001 *&---------------------------------------------------------------------* *&---------------------------------------------------------------------* *& Module STATUS_1001 OUTPUT *&---------------------------------------------------------------------* * text *----------------------------------------------------------------------* MODULE status_1001 OUTPUT. SET PF-STATUS '1001'. * SET TITLEBAR 'xxx'. IF tree IS INITIAL. PERFORM create_tree. ENDIF. IF grid IS INITIAL. CREATE OBJECT container2 EXPORTING container_name = 'GIRD'. CREATE OBJECT grid EXPORTING i_parent = container2. PERFORM write_data. CALL METHOD grid->set_table_for_first_display EXPORTING is_layout = layout CHANGING it_fieldcatalog = fieldcatalog it_outtab = itab_out. ELSE. CALL METHOD grid->refresh_table_display. IF sy-subrc <> 0. ENDIF. ENDIF. ENDMODULE. *&---------------------------------------------------------------------* *& Module USER_COMMAND_1001 INPUT *&---------------------------------------------------------------------* * text *----------------------------------------------------------------------* MODULE user_command_1001 INPUT. CASE sy-ucomm. WHEN 'SELECT'. PERFORM get_data USING node-node_key node-relatkey. WHEN 'BACK'. SET SCREEN 0. WHEN 'EXIT' OR 'CANCEL'. LEAVE PROGRAM. WHEN OTHERS. ENDCASE. ENDMODULE. *&---------------------------------------------------------------------* *& Form CREATE_TREE *&---------------------------------------------------------------------* * text *----------------------------------------------------------------------* * --> p1 text * <-- p2 text *----------------------------------------------------------------------* FORM create_tree . CLEAR:event. REFRESH:events. event-eventid = 21 . event-appl_event = 'X'. APPEND event TO events. CLEAR:node. REFRESH:node_table. node-node_key = 'X'. node-isfolder = 'X'. node-text = TEXT-001. APPEND node TO node_table . LOOP AT ztree. CLEAR:node. node-node_key = 'X' && ztree-num1. node-relatkey = 'X'. node-isfolder = 'X'. node-text = ztree-text1. APPEND node TO node_table . CLEAR:node. node-node_key = 'X' && ztree-num1 && ztree-num2. node-relatkey = 'X' && ztree-num1. node-isfolder = 'X'. node-text = ztree-text2. APPEND node TO node_table . CLEAR:node. node-node_key = 'X' && ztree-num1 && ztree-num2 && ztree-num3. node-relatkey = 'X' && ztree-num1 && ztree-num2. node-isfolder = 'X'. node-text = ztree-text3. node-n_image = '@5B@'. APPEND node TO node_table . ENDLOOP. SORT node_table BY node_key relatkey. DELETE ADJACENT DUPLICATES FROM node_table COMPARING ALL FIELDS. CREATE OBJECT container EXPORTING container_name = 'TREE'. CREATE OBJECT tree EXPORTING parent = container node_selection_mode = cl_gui_simple_tree=>node_sel_mode_single. CALL METHOD tree->add_nodes EXPORTING table_structure_name = 'MTREESNODE' node_table = node_table EXCEPTIONS error_in_node_table = 1 failed = 2 dp_error = 3 table_structure_name_not_found = 4 OTHERS = 5. CALL METHOD tree->set_registered_events EXPORTING events = events EXCEPTIONS cntl_error = 1 cntl_system_error = 2 illegal_event_combination = 3 OTHERS = 4. CREATE OBJECT application. SET HANDLER application->single_click FOR tree. CLEAR:node."不清空会导致首次查找有问题 ENDFORM. *&---------------------------------------------------------------------* *& Form WRITE_DATA *&---------------------------------------------------------------------* * text *----------------------------------------------------------------------* * --> p1 text * <-- p2 text *----------------------------------------------------------------------* FORM write_data . CLEAR:fieldcatalog. REFRESH:fieldcatalog. layout-no_toolbar = 'X'. * layout-cwidth_opt = 'X'. CLEAR wa_fieldcatalog. wa_fieldcatalog-fieldname = 'MATNR'. wa_fieldcatalog-scrtext_l = TEXT-002. wa_fieldcatalog-outputlen = 40. APPEND wa_fieldcatalog TO fieldcatalog. CLEAR wa_fieldcatalog. wa_fieldcatalog-fieldname = 'MAKTX'. wa_fieldcatalog-scrtext_l = TEXT-003. wa_fieldcatalog-outputlen = 20. APPEND wa_fieldcatalog TO fieldcatalog. CLEAR wa_fieldcatalog. wa_fieldcatalog-fieldname = 'MAKTX_L'. wa_fieldcatalog-scrtext_l = TEXT-004. wa_fieldcatalog-outputlen = 40. APPEND wa_fieldcatalog TO fieldcatalog. CLEAR wa_fieldcatalog. wa_fieldcatalog-fieldname = 'WERKS'. wa_fieldcatalog-scrtext_l = TEXT-005. wa_fieldcatalog-outputlen = 4. APPEND wa_fieldcatalog TO fieldcatalog. CLEAR wa_fieldcatalog. wa_fieldcatalog-fieldname = 'LGORT'. wa_fieldcatalog-scrtext_l = TEXT-006. wa_fieldcatalog-outputlen = 5. APPEND wa_fieldcatalog TO fieldcatalog. CLEAR wa_fieldcatalog. wa_fieldcatalog-fieldname = 'BISMT'. wa_fieldcatalog-scrtext_l = TEXT-007. wa_fieldcatalog-outputlen = 10. APPEND wa_fieldcatalog TO fieldcatalog. CLEAR wa_fieldcatalog. wa_fieldcatalog-fieldname = 'LABST'. wa_fieldcatalog-scrtext_l = TEXT-008. wa_fieldcatalog-outputlen = 15. APPEND wa_fieldcatalog TO fieldcatalog. CLEAR wa_fieldcatalog. wa_fieldcatalog-fieldname = 'BDMNG'. wa_fieldcatalog-scrtext_l = TEXT-009. wa_fieldcatalog-outputlen = 15. APPEND wa_fieldcatalog TO fieldcatalog. CLEAR wa_fieldcatalog. wa_fieldcatalog-fieldname = 'LABST2'. wa_fieldcatalog-scrtext_l = TEXT-010. wa_fieldcatalog-outputlen = 15. APPEND wa_fieldcatalog TO fieldcatalog. CLEAR wa_fieldcatalog. wa_fieldcatalog-fieldname = 'MENGE'. wa_fieldcatalog-scrtext_l = TEXT-011. wa_fieldcatalog-outputlen = 15. APPEND wa_fieldcatalog TO fieldcatalog. CLEAR wa_fieldcatalog. wa_fieldcatalog-fieldname = 'EISBE'. wa_fieldcatalog-scrtext_l = TEXT-012. wa_fieldcatalog-outputlen = 15. APPEND wa_fieldcatalog TO fieldcatalog. CLEAR wa_fieldcatalog. wa_fieldcatalog-fieldname = 'MEINS'. wa_fieldcatalog-scrtext_l = TEXT-013. wa_fieldcatalog-outputlen = 4. APPEND wa_fieldcatalog TO fieldcatalog. *-----ECCUpgradeS4--------SMY--------20250210-------START " 根据工厂判断是否添加 budat_mkpf 字段 IF p_werks = '7010' OR p_werks = '7011'. CLEAR wa_fieldcatalog. wa_fieldcatalog-fieldname = 'BUDAT_MKPF'. wa_fieldcatalog-scrtext_l = TEXT-014. wa_fieldcatalog-outputlen = 10. APPEND wa_fieldcatalog TO fieldcatalog. ENDIF. *-----ECCUpgradeS4--------SMY--------20250210-------END ENDFORM. *&---------------------------------------------------------------------* *& Form GET_DATA *&---------------------------------------------------------------------* * text *----------------------------------------------------------------------* * -->P_NODE_NODE_KEY text * -->P_NODE_RELATKEY text *----------------------------------------------------------------------* FORM get_data USING p_node_key p_relatkey. REFRESH: itab, itab_out. *zhouyan 2025/8/6 " 优化主查询 - 只选择必要字段 SELECT mard~matnr, mard~werks, mard~lgort, mard~labst, mara~matkl, mara~meins, marc~eisbe INTO CORRESPONDING FIELDS OF TABLE @itab FROM mard INNER JOIN mara ON mara~matnr = mard~matnr INNER JOIN marc ON marc~matnr = mard~matnr AND marc~werks = mard~werks WHERE mard~werks = @p_werks AND marc~lvorm = '' AND mara~matkl <> '' AND mara~mtart = 'ERSA' AND mara~lvorm = '' AND mara~mstae = '' AND mard~lvorm = ''. *zhouyan 2025/8/6 LOOP AT itab. IF p_relatkey = ''. ELSEIF p_relatkey = 'X'. IF itab-matkl(1) <> p_node_key+1(1). DELETE itab. ENDIF. ELSE. IF strlen( p_node_key ) = 3. IF itab-matkl(2) <> p_node_key+1(2). DELETE itab. ENDIF. ELSEIF strlen( p_node_key ) = 4. IF itab-matkl(3) <> p_node_key+1(3). DELETE itab. ENDIF. ELSE. IF itab-matkl <> p_node_key+1. DELETE itab. ENDIF. ENDIF. ENDIF. ENDLOOP. " 首先检查itab是否为空 CHECK itab[] IS NOT INITIAL. " 收集所有需要查询的matnr(用于MAKT和MSEG查询) DATA: lt_matnr_range TYPE RANGE OF matnr. lt_matnr_range = VALUE #( FOR wa IN itab ( sign = 'I' option = 'EQ' low = wa-matnr ) ). " 收集所有需要查询的matnr+werks+lgort组合(用于ZMM_BISMT查询) DATA: lt_bismt_keys TYPE STANDARD TABLE OF ty_bismt_key. LOOP AT itab ASSIGNING FIELD-SYMBOL(<fs_itab>). APPEND VALUE #( matnr = <fs_itab>-matnr werks = <fs_itab>-werks lgort = <fs_itab>-lgort ) TO lt_bismt_keys. ENDLOOP. SORT lt_bismt_keys BY matnr werks lgort. DELETE ADJACENT DUPLICATES FROM lt_bismt_keys. " 将RANGE表转换为标准表 DATA: lt_matnr_list TYPE STANDARD TABLE OF matnr. lt_matnr_list = VALUE #( FOR ls_range IN lt_matnr_range ( ls_range-low ) ). " 使用FOR ALL ENTRIES查询 SELECT matnr, maktx FROM makt FOR ALL ENTRIES IN @lt_matnr_list WHERE matnr = @lt_matnr_list-table_line AND spras = @sy-langu INTO TABLE @DATA(lt_makt). " 批量查询ZMM_BISMT数据 IF lt_bismt_keys IS NOT INITIAL. SELECT matnr, werks, lgort, bismt FROM zmm_bismt FOR ALL ENTRIES IN @lt_bismt_keys WHERE matnr = @lt_bismt_keys-matnr AND werks = @lt_bismt_keys-werks AND lgort = @lt_bismt_keys-lgort INTO TABLE @DATA(lt_bismt). ENDIF. " 批量查询MSEG数据(最大过账日期) IF ( p_werks = '7011' OR p_werks = '7010' ) AND lt_matnr_range IS NOT INITIAL. SELECT matnr, MAX( budat_mkpf ) AS max_budat FROM mseg WHERE matnr IN @lt_matnr_range AND ( ( werks = '7011' AND bwart IN ('201','311') ) OR ( werks = '7010' AND bwart = '201' ) ) GROUP BY matnr INTO TABLE @DATA(lt_max_budat). ENDIF. " 更新itab数据 LOOP AT itab ASSIGNING <fs_itab>. " 更新MAKTX(物料描述) READ TABLE lt_makt ASSIGNING FIELD-SYMBOL(<fs_makt>) WITH KEY matnr = <fs_itab>-matnr. IF sy-subrc = 0. <fs_itab>-maktx = <fs_makt>-maktx. ENDIF. " 更新BISMT READ TABLE lt_bismt ASSIGNING FIELD-SYMBOL(<fs_bismt>) WITH KEY matnr = <fs_itab>-matnr werks = <fs_itab>-werks lgort = <fs_itab>-lgort. IF sy-subrc = 0. <fs_itab>-bismt = <fs_bismt>-bismt. ENDIF. " 更新BUDAT_MKPF(最大过账日期) IF p_werks = '7011' OR p_werks = '7010'. READ TABLE lt_max_budat ASSIGNING FIELD-SYMBOL(<fs_budat>) WITH KEY matnr = <fs_itab>-matnr. IF sy-subrc = 0. <fs_itab>-budat_mkpf = <fs_budat>-max_budat. ENDIF. ENDIF. ENDLOOP. LOOP AT itab. IF matnr <> ''. IF itab-matnr <> matnr. DELETE itab. CONTINUE. ENDIF. ENDIF. IF lgort <> ''. IF itab-lgort <> lgort. DELETE itab. CONTINUE. ENDIF. ENDIF. IF bismt <> ''. IF itab-bismt CS bismt. ELSE. DELETE itab. CONTINUE. ENDIF. ENDIF. IF itab-maktx CS maktx OR itab-maktx_l CS maktx. ELSE. DELETE itab. CONTINUE. ENDIF. IF eisbe_r1 = 'X'. IF itab-labst >= itab-eisbe. DELETE itab. CONTINUE. ENDIF. ENDIF. ENDLOOP. * 收集所有需要查询的matnr+werks+lgort组合 DATA: lt_resb_keys TYPE STANDARD TABLE OF ty_resb_key. LOOP AT itab ASSIGNING <fs_itab>. APPEND VALUE #( matnr = <fs_itab>-matnr werks = <fs_itab>-werks lgort = <fs_itab>-lgort ) TO lt_resb_keys. ENDLOOP. * 去重 SORT lt_resb_keys BY matnr werks lgort. DELETE ADJACENT DUPLICATES FROM lt_resb_keys. * 批量查询RESB数据(使用自定义结构) DATA: lt_resb_data TYPE STANDARD TABLE OF ty_resb_result. IF lt_resb_keys IS NOT INITIAL. SELECT matnr, werks, lgort, bdmng, enmng FROM resb FOR ALL ENTRIES IN @lt_resb_keys WHERE matnr = @lt_resb_keys-matnr AND werks = @lt_resb_keys-werks AND lgort = @lt_resb_keys-lgort AND xloek = '' AND kzear = '' AND shkzg = 'H' INTO CORRESPONDING FIELDS OF TABLE @lt_resb_data. ENDIF. * 更新itab数据 LOOP AT itab ASSIGNING <fs_itab>. CLEAR <fs_itab>-bdmng. * 查找匹配的RESB记录并累加 LOOP AT lt_resb_data ASSIGNING FIELD-SYMBOL(<fs_resb>) WHERE matnr = <fs_itab>-matnr AND werks = <fs_itab>-werks AND lgort = <fs_itab>-lgort. <fs_itab>-bdmng = <fs_itab>-bdmng + <fs_resb>-bdmng - <fs_resb>-enmng. ENDLOOP. ENDLOOP. * 收集查询条件(去重) DATA lt_po_keys TYPE ty_po_key_tab. lt_po_keys = VALUE #( FOR <wa> IN itab ( matnr = <wa>-matnr werks = <wa>-werks lgort = <wa>-lgort ) ). SORT lt_po_keys BY matnr werks lgort. DELETE ADJACENT DUPLICATES FROM lt_po_keys. * 执行批量查询 DATA lt_po_data TYPE ty_po_result_tab. IF lt_po_keys IS NOT INITIAL. " 查询EKPO数据 DATA lt_ekpo_data TYPE ty_ekpo_temp_tab. SELECT matnr,werks,lgort,menge,ebeln FROM ekpo FOR ALL ENTRIES IN @lt_po_keys WHERE matnr = @lt_po_keys-matnr AND werks = @lt_po_keys-werks AND lgort = @lt_po_keys-lgort AND elikz = '' AND loekz = '' INTO TABLE @lt_ekpo_data. " 查询已审批的采购订单 IF lt_ekpo_data IS NOT INITIAL. DATA lt_approved_po TYPE ty_ebeln_tab. SELECT ebeln FROM ekko FOR ALL ENTRIES IN @lt_ekpo_data WHERE ebeln = @lt_ekpo_data-ebeln AND frgke = '5' INTO TABLE @lt_approved_po. " 在应用层汇总数据 SORT lt_approved_po BY ebeln. LOOP AT lt_ekpo_data ASSIGNING FIELD-SYMBOL(<fs_ekpo>). READ TABLE lt_approved_po TRANSPORTING NO FIELDS WITH KEY ebeln = <fs_ekpo>-ebeln BINARY SEARCH. IF sy-subrc = 0. COLLECT VALUE ty_po_result( matnr = <fs_ekpo>-matnr werks = <fs_ekpo>-werks lgort = <fs_ekpo>-lgort menge = <fs_ekpo>-menge ) INTO lt_po_data. ENDIF. ENDLOOP. ENDIF. ENDIF. * 更新主表数据 LOOP AT itab ASSIGNING <fs_itab>. READ TABLE lt_po_data ASSIGNING FIELD-SYMBOL(<fs_po>) WITH KEY matnr = <fs_itab>-matnr werks = <fs_itab>-werks lgort = <fs_itab>-lgort. IF sy-subrc = 0. <fs_itab>-menge = <fs_po>-menge. ELSE. CLEAR <fs_itab>-menge. ENDIF. " 计算可用库存2 <fs_itab>-labst2 = <fs_itab>-labst - <fs_itab>-bdmng. ENDLOOP. *zhouyan 2025/8/7 itab_out[] = itab[]. *zhouyan 2025/8/7 ENDFORM.
最新发布
08-09
### 测试 ABAP SQL 语句执行时间的工具 为了评估和优化 ABAPSQL 查询的性能,开发人员可以利用多种内置工具和技术来测量查询的响应时间和资源消耗情况。 #### 使用 SQL Trace 工具 SQL Trace 是一种强大的诊断工具,能够记录数据库访问的时间戳和其他相关信息。通过启用此功能,开发者可以获得详细的日志文件,其中包含了每次数据库交互的具体耗时数据[^4]。 ```abap CALL FUNCTION 'ST05_SET_TRACE' EXPORTING trace_on_off = 'X'. ``` #### 利用 SE38 或者 SE80 内置调试器 当在事务码 SE38 (Programs) 或者 SE80 (Object Navigator) 下运行程序时,可以选择开启 Debugging 模式,在这里不仅可以逐行跟踪代码逻辑,还可以查看每条 SQL 命令的实际执行计划以及所花费的时间开销[^1]。 #### 应用 ST05 性能分析仪 ST05 提供了一个图形化的界面用于展示 SQL 调用统计信息,包括但不限于读取次数、CPU 时间、等待事件等重要指标。这有助于识别潜在瓶颈并指导进一步调优工作[^2]。 #### 实施自定义计时函数模块 对于更精细粒度的需求,可以在应用程序内部编专门负责计算特定部分(如单个 OPEN SQL 请求)所需毫秒数的小型服务类或方法: ```abap DATA: lv_start TYPE timestampl, lv_end TYPE timestampl. lv_start = cl_abap_tstmp=>current_utc_timestamp( ). SELECT * INTO TABLE @lt_data FROM dba_objects WHERE owner = 'SYSTEM'. lv_end = cl_abap_tstmp=>current_utc_timestamp( ). WRITE: / |Execution Time(ms):|, ( lv_end - lv_start ) * 1000. ```
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值