Using an Index When You Don't Have all of the Fields

[Former Member]

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.  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.  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.