ABAP--在查询条件只包含部分索引字段时，如何使用索引

Jack Wu

浏览: 894842 次
来自: ...

最近访客更多访客>>

chuandaojun

devcang

fordsupr

fanfree

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

Oracle DB2 UP Go Blog

One of the most important considerations when writing a select statement against a large table is the effective use of an index. However this is sometimes more easily said than done. Have you ever found that your WHERE clause is missing just one field of an index and that field is not at the end of the index?

There are some situations where you can make more effective use of the entire index even if you are missing a field. Here is a simple trick to help you do just that. If the field you are missing cannot contain too many entries, then if you create a range table with all possible entries and add that range table to your WHERE clause, you can dramatically speed up a SELECT statement. Even when you take into account the extra time needed to retrieve the key fields, the results are worth it. This may seem a bit counter-intuitive, but the example code shows what I'm doing (but be careful – if you run this code in a QA environment, it may take a while):

代码如下：

REPORT ztest_indexed_selects.

PARAMETERS: p_bukrs   LIKE bkpf-bukrs MEMORY ID buk      OBLIGATORY,
            p_belnr   LIKE bkpf-belnr MEMORY ID bln      OBLIGATORY,
            p_gjahr   LIKE bkpf-gjahr MEMORY ID gjr      OBLIGATORY.

TYPES: BEGIN OF bkpf_fields,
         bukrs    LIKE bkpf-bukrs,
         belnr    LIKE bkpf-belnr,
         gjahr    LIKE bkpf-gjahr,
         blart    LIKE bkpf-blart,
         budat    LIKE bkpf-budat,
       END   OF bkpf_fields.

DATA: bkpf TYPE bkpf,
dd07l TYPE dd07l.

DATA: bkpf_int TYPE TABLE OF bkpf_fields,
bkpf_wa TYPE bkpf_fields.

DATA: start   TYPE i,
      end     TYPE i,
      dif     TYPE i.

START-OF-SELECTION.
PERFORM get_one_document.
PERFORM unindexed_select_bkpf.
PERFORM indexed_select_bkpf.
PERFORM unindexed_select_bkpf_2.
PERFORM indexed_select_bkpf_2.

*&---------------------------------------------------------------------*
*& Form get_one_document
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
FORM get_one_document.

* First we get a single document using a select statement that is
* fully qualified on the primary key. Because buffering may be an issue,
* the first select will be disregarded in this test. However, in real
* life, this would be the important time.

* Initial select
SELECT bukrs belnr gjahr blart budat
    FROM bkpf
    INTO TABLE bkpf_int
    WHERE bukrs = p_bukrs
    AND   belnr = p_belnr
    AND   gjahr = p_gjahr.

IF sy-subrc <> 0.
MESSAGE ID '00' TYPE 'E' NUMBER '001' WITH
'Document does not exist'.
ENDIF.

* Next we get the same document using the same fully qualified select
* statement. We will use the time for this in comparisons.

REFRESH bkpf_int.
GET RUN TIME FIELD start.

SELECT bukrs belnr gjahr blart budat
    FROM bkpf
    INTO TABLE bkpf_int
    WHERE bukrs = p_bukrs
    AND   belnr = p_belnr
    AND   gjahr = p_gjahr.

GET RUN TIME FIELD end.
dif = end - start.
WRITE: /001 'Time for first (fully qualified) select',
067 ':', dif, 'microseconds'.
SKIP 1.

* So we can use these fields later on
READ TABLE bkpf_int INTO bkpf_wa INDEX 1.

ENDFORM. " get_one_document

*&---------------------------------------------------------------------*
*& Form unindexed_select_bkpf
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
FORM unindexed_select_bkpf.

* Now we select a group of documents using a select statement that is
* missing the company code from the primary key. This may return a
* different set of documents from the first select, but we are just
* interested in how long it takes.

* Initial select

REFRESH bkpf_int.
SELECT bukrs belnr gjahr blart budat
    FROM bkpf
    INTO TABLE bkpf_int
    WHERE belnr = p_belnr
    AND   gjahr = p_gjahr.

REFRESH bkpf_int.
GET RUN TIME FIELD start.

* Use this select in comparisons
SELECT bukrs belnr gjahr blart budat
    FROM bkpf
    INTO TABLE bkpf_int
    WHERE belnr = p_belnr
    AND   gjahr = p_gjahr.

GET RUN TIME FIELD end.
dif = end - start.
WRITE: /001 'Time for second (unindexed) select',
067 ':', dif, 'microseconds'.

ENDFORM. " unindexed_select_bkpf

*&---------------------------------------------------------------------*
*& Form indexed_select_bkpf
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
FORM indexed_select_bkpf.

* Now we're going to use the first trick. Go to table T001 (company
* codes) and retrieve all the company codes and put them into a range
* table. We'll put the range table into the select. So long as the
* number of company codes is not too great, this will speed up the
* select on BKPF.

RANGES: r_bukrs FOR bkpf-bukrs.

* Preliminary selects
r_bukrs-option = 'EQ'.
r_bukrs-sign   = 'I'.
SELECT bukrs
    FROM t001
    INTO r_bukrs-low.
    APPEND r_bukrs.
ENDSELECT.

REFRESH bkpf_int.
SELECT bukrs belnr gjahr blart budat
    FROM bkpf
    INTO TABLE bkpf_int
    WHERE bukrs IN r_bukrs
    AND   belnr = p_belnr
    AND   gjahr = p_gjahr.

REFRESH: bkpf_int,
r_bukrs.
GET RUN TIME FIELD start.

* Use these selects in comparison
r_bukrs-option = 'EQ'.
r_bukrs-sign   = 'I'.
SELECT bukrs
    FROM t001
    INTO r_bukrs-low.
    APPEND r_bukrs.
ENDSELECT.

SELECT bukrs belnr gjahr blart budat
    FROM bkpf
    INTO TABLE bkpf_int
    WHERE bukrs IN r_bukrs
    AND   belnr = p_belnr
    AND   gjahr = p_gjahr.

GET RUN TIME FIELD end.
dif = end - start.
WRITE: /001 'Time for third select',
'(indexed by selecting from the check table)',
067 ':', dif, 'microseconds'.
SKIP 1.

ENDFORM. " indexed_select_bkpf

*&---------------------------------------------------------------------*
*& Form unindexed_select_bkpf_2
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
FORM unindexed_select_bkpf_2.

* Now we'll get another group of records from BKPF. There is a
* secondary index on BKPF with fields BUKRS, BSTAT and BUDAT.
* We're going to leave BSTAT out of the select and use
* BUKRS and BUDAT from the first document we selected.

* Preliminary select - to be ignored.
REFRESH bkpf_int.
SELECT bukrs belnr gjahr blart budat
    FROM bkpf
    INTO TABLE bkpf_int
    WHERE bukrs = p_bukrs
    AND   budat = bkpf_wa-budat.

REFRESH bkpf_int.
GET RUN TIME FIELD start.

SELECT bukrs belnr gjahr blart budat
    FROM bkpf
    INTO TABLE bkpf_int
    WHERE bukrs = p_bukrs
    AND   budat = bkpf_wa-budat.

GET RUN TIME FIELD end.
dif = end - start.
WRITE: /001 'Time for fourth (partially indexed) select',
067 ':', dif, 'microseconds'.

ENDFORM. " unindexed_select_bkpf_2

*&---------------------------------------------------------------------*
*& Form indexed_select_bkpf_2
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
FORM indexed_select_bkpf_2.

* Finally, we will use the domain values of BSTAT in the select. If you
* are sure that you know all of the values, you can hardcode them. I am
* using all of the possible values of BSTAT so that all three selects
* return the same data. In practice, we would probably narrow it down.
* But since normal FI postings have BSTAT = SPACE, this won't help
* unless we are looking for 'not' normal documents.

RANGES: r_bstat FOR bkpf-bstat.
DATA : d1 LIKE dd07l-domvalue_l,
d2 LIKE dd07l-domvalue_h.

* Hardcoded values
* Preliminary select.
REFRESH bkpf_int.
SELECT bukrs belnr gjahr blart budat
    FROM bkpf
    INTO TABLE bkpf_int
    WHERE bukrs EQ p_bukrs
    AND   bstat IN (' ', 'A', 'B', 'D', 'M', 'S', 'V', 'W', 'Z')
    AND   budat = bkpf_wa-budat.

REFRESH bkpf_int.
GET RUN TIME FIELD start.
* Use this select in comparisons
SELECT bukrs belnr gjahr blart budat
    FROM bkpf
    INTO TABLE bkpf_int
    WHERE bukrs EQ p_bukrs
    AND   bstat IN (' ', 'A', 'B', 'D', 'M', 'S', 'V', 'W', 'Z')
    AND   budat = bkpf_wa-budat.

GET RUN TIME FIELD end.
dif = end - start.
WRITE: /001 'Time for fifth select',
'(indexed by hardcoding the domain values)',
067 ':', dif, 'microseconds'.

* After an upgrade, the values in a domain may change. It's safer to
* retrieve all of the values from the data dictionary. There is a very
* slight increase in the time.

r_bstat-sign   = 'I'.
SELECT domvalue_l domvalue_h
    FROM dd07l
    INTO (d1, d2)
    WHERE domname = 'BSTAT'
    AND   as4local = 'A'.
    IF d2 IS INITIAL.
      r_bstat-option = 'EQ'.
      r_bstat-low    = d1.
      CLEAR r_bstat-high.
    ELSE.
      r_bstat-option = 'BT'.
      r_bstat-low    = d1.
      r_bstat-high   = d2.
    ENDIF.
    APPEND r_bstat.
ENDSELECT.

REFRESH bkpf_int.
SELECT bukrs belnr gjahr blart budat
    FROM bkpf
    INTO TABLE bkpf_int
    WHERE bukrs EQ p_bukrs
    AND   bstat IN r_bstat
    AND   budat = bkpf_wa-budat.

REFRESH: bkpf_int,
r_bstat.

* Use this select in comparisons.
GET RUN TIME FIELD start.

REFRESH bkpf_int.
SELECT bukrs belnr gjahr blart budat
    FROM bkpf
    INTO TABLE bkpf_int
    WHERE bukrs EQ p_bukrs
    AND   bstat IN r_bstat
    AND   budat = bkpf_wa-budat.

GET RUN TIME FIELD end.
dif = end - start.
WRITE: /001 'Time for sixth select',
'(indexed by selecting the domain values)',
067 ':', dif, 'microseconds'.

ENDFORM. " indexed_select_bkpf_2

I ran the above code in QA instances with a DB2 environment in both 4.6C and 4.7. There are more indexes on BKPF in 4.7, but I tried to use one that is in both versions. I also ran a similar program in Oracle with comparable results. But I really don’t know if it will work with other databases – please let me know!

I ran this many times in both active and quiet systems. Here are some typical results:

Time for first (fully qualified) select : 148 microseconds Time for second (unindexed) select : 1,873,906 microseconds Time for third select (indexed by selecting from the check table) : 455 microseconds Time for fourth (partially indexed) select : 816,253 microseconds Time for fifth select (indexed by hardcoding the domain values) : 43,259 microseconds Time for sixth select (indexed by selecting the domain values) : 43,332 microseconds

Some things to note:

In the above times, the first select shows what happens in the ideal world. We are comparing select 2 against select 3 and select 4 against selects 5 and 6. But selects 2 and 3 should return the same results as should selects 4, 5 and 6.

But the point is that even though start out knowing nothing about (and presumably not caring about) the company code in selects 2 and 3 and the document status in selects 4, 5 and 6, if you put all possible values of these fields into the select statement, the results are dramatic.

If you try to combine both tricks, you will probably find that they don’t work very well together. Once seems to be enough.

https://www.sdn.sap.com/irj/sdn/weblogs?blog=/pub/wlg/4370

分享到：