warning message:Index completely contains the fields of index 0

[Former Member]

Hi All,

I am creating an index named as ZC1 on VBAK table with the following fields.

VBELN

AUART

VKORG

VKBUR

BSTDK

But during the activation of the index i am getting warning message like

"Index VBAK-ZC1 completely contains the fields of index 0"

I already checked the previous indexes created on VBAK table but there are no indexes created with the same fields.

But why this warning message is displaying...

Any clues...

Regards

Ram

[Former Member]

Ram,

Its just the warning message and this is coming since the key field VBELN is also the part of this index. Index 0 is generally the key fields.

Regards,

Hardik Mehta

[Former Member]

hello,

remove the field VBELN and see if the warning goes. Since this is a warning you should still be able to Activate and use the secondary index. As Hardik said it seems to be due to Primary key VBELN used in the secondary index you created.

best regards,

swanand

[Former Member]

Hi,

Actually in my where clause following fields are there

VBELN

AUART

VKORG

VKBUR

BSTDK

so i created index with those fields.

when i see the F1 help(Diagnosis) it is telling

"

You created an index whose fields are completely contained in another index. This index is therefore unnecessary and should be deleted."

But i checked there are no indexes created with those fields...

What can i do whether can i delete the index ?

Regards

Ram

[ThomasZloch]

Index 0 is the primary key of a table, and VBELN is the primary key of table VBAK. It does not make sense to add an additional index with VBELN as leading field, there is no advantage over the existing primary key.

You can safely delete ZC1 again. If there is any performance issue, look for for information referenced here:

Thomas

[raymond_giuseppi]

You are creating an index where the first index key is the unique primary key (I hope you also have client MANDT) so the first key will identify a single unique record and the following keys will serve no purpose at all, neither selection nor sort ?

The index in the message refer to index VBAK~0 the primary index key index. (Of course you cannot delete it, I hope)

Please Read before Posting in the ABAP Testing and Troubleshooting Space!

Regards,

Raymond

[raymond_giuseppi]

You were faster

[ThomasZloch]

I had my statistics updated just recently

[Former Member]

Hi Raymond,

If i dont want that index can i delete the index?

If so what is procedure for deleting the index.

Actually i did not transport the index till production client it is transported to till quality client.

For my understanding i am asking.

Regards

Ram

[raymond_giuseppi]

Just delete the index ZC1 from SE11, trash can icon. Then transport deletion to quality.

Regards,

Raymond

[yuri_ziryukin]

Well, guys, you are not 100% right.

Sometimes it makes sense. You can spare table block access if you select all fields directly from the index. Let's imagine there is a SELECT statement with FAE and VBELN is always passed. But in addition some other fields are checked like the ones that are mentioned bythe topic starter. And these fields are restricting the result list significantly. By sparing table block access you might improve the performance by factors.

Please keep that in mind.

Yuri

[Former Member]

Hi Yuri...

I am using like this select statement

   SELECT  vkbur vkorg vtweg vbeln kunnr angdt bnddt  bstdk spart
        INTO TABLE it_vbak FROM vbak
        WHERE vbeln IN s_vbeln
          AND auart = 'ZABC'
          AND vkorg = 'ZORG'
          AND vkbur IN s_vkbur
          AND bstdk IN s_bstdk
          AND kunnr IN s_kunnr.

This select statement is taking long time so i created an index on VBAK with all the fields like

MANDT

VBELN

AUART

VKORG

VKBUR

BSTDK

KUNNR

Can any one Please conclude whether it is advisable to create an index on VBAK with the following fields or not?

Whether creation of index leads to any other performance issues?

But during activation i got warning message "Index VBAK-ZC1 completely contains the fields of index 0"

And also i created another index on VBAP table with the following fields

MANDT

VBELN

MATNR

MATKL

SPART

WERKS

In this case i did not get any warning message like above.

Please clarify.

Regards

Ram

[yuri_ziryukin]

Hello Ram,

1) what is the most typical case for the ranges?

        vbeln IN s_vbeln
        vkbur IN s_vkbur
       bstdk IN s_bstdk
       kunnr IN s_kunnr.

Depending on that I will give you advices.

If s_vbeln is always provided with the list of single entries, forget about creating an index for VBAK.

If it is not, then as Raymond advised above, make sure you have at least MANDT, AUART and VKORG in the beginning of the index.

If you much more often select with KUNNR, then you'll better use the table VAKPA instead of VBAK. It is optimized for the usage with partner number (field KUNDE). And sales org + sales office are also part of the table. Actually AUART is also there so you can make the following join:

SELECT a~vkbur a~vkorg a~vtweg a~vbeln a~kunnr b~angdt b~bnddt b~bstdk a~spart
        INTO corresponding fields of TABLE it_vbak FROM vakpa AS a

        INNER JOIN vbak AS b ON a~vbeln = b~vbeln
        WHERE a~vbeln IN s_vbeln
          AND a~auart = 'ZABC'
          AND a~vkorg = 'ZORG'
          AND a~vkbur IN s_vkbur
          AND b~bstdk IN s_bstdk
          AND a~kunde IN s_kunnr

          AND a~parvw = 'AG'.  "this one for sold-to party

* I explicitly did not use DISTINCT after SELECT because I know that there should be only one entry in VAKPA for each sales document where PARVW is equal to "AG". In unlikely case of duplicate entries in the result list you can include DISTINCT.

And then depending on the search criteria in the ranges you can create one or more indexes for VAKPA that better support your selection.

2) From the VBAP you most probably want to select based on the material number, right?

Please make a join then with the table VAPMA in the same way as I proposed above.

Good luck,

  Yuri

[Former Member]

Hi,

Actually my select query is like this..

SELECT  vkbur vkorg vtweg vbeln kunnr angdt bnddt  bstdk spart
        INTO TABLE it_vbak FROM vbak
        WHERE vbeln IN s_vbeln
          AND auart = 'ZABC'
          AND vkorg = 'ZORG'
          AND vkbur IN s_vkbur
          AND bstdk IN s_bstdk
          AND kunnr IN s_kunnr.

   IF it_vbak[] IS NOT INITIAL.
      SELECT vbeln posnr matnr kwmeng ntgew werks spart charg

       INTO TABLE it_vbap FROM vbap
       FOR ALL ENTRIES IN it_vbak
       WHERE vbeln = it_vbak-vbeln
         AND matnr IN s_matnr
         AND matkl IN s_matkl
         AND spart IN s_spart    

         AND werks IN s_werks.

endif.

Here none of the selection screen fields are mandatory ...user may or may not be enter.

in this scenario i need to optimise...for this only i created indexes.

Now tell me how to optimise the code?

If any index had to be create then what is the fields order i need to create the indexes on VBAK and VBAP tables?

Please Advice...

Regards

Ram

[yuri_ziryukin]

Hello Ram,

you cannot perfectly optimize all use cases. This is in real life practically impossible. You'll need to create many indexes depending on the user input which is never recommended by SAP.

Normally we talk to business process owner / power user and get information what is the most frequent use cases and we optimize those.

If you totally have no idea what will be used for the query, then let the Database decide!

SELECT a~vkbur a~vkorg a~vtweg a~vbeln a~kunnr b~angdt b~bnddt b~bstdk a~spart

c~posnr c~matnr d~kwmeng d~ntgew d~werks c~spart AS spart_pos d~charg
        INTO TABLE it_vbak_vbap FROM vakpa AS a

        INNER JOIN vbak AS b ON a~vbeln = b~vbeln

        INNER JOIN vapma AS c ON c~vbeln = b~vbeln

        INNER JOIN vbap AS d ON d~vbeln = c~vbeln AND d~posnr = c~posnr
        WHERE a~vbeln IN s_vbeln
          AND a~auart = 'ZABC'
          AND a~vkorg = 'ZORG'
          AND a~vkbur IN s_vkbur
          AND b~bstdk IN s_bstdk
          AND a~kunde IN s_kunnr

          AND a~parvw = 'AG'  "this one for sold-to party

          AND c~matnr IN s_matnr

          AND d~matkl IN s_matkl

          AND c~spart IN s_spart    

          AND c~werks IN s_werks.

Ofc. you need to define the internal table it_vbak_vbap with the fields like I specified in the SELECT clause.

This way the DB will always make a decision on the access path based on the data in ranges.

This way you are "protected" from bad access in ABAP.

For example, the user does not specify customer and other fields from header. In that case your first select from VBAK will most probably delivery thousands of orders and you'll do a second select with huge FAE table, although material number in the second select will be quite selective.

In such kind of generic selections I'd always let DB decide. Remember, databases are designed for doing JOINS

Regards,

  Yuri

[raymond_giuseppi]

Move VBELN to the end of the key list of the index.

(Order of keys in WHERE statement does not matter, but order ok keys in the indexes is of the utmost importance.)

In your case the most important part of the index is  MANDT, AUART VKORD then it depends on actual selection criteria input.

For VBAP, as you did not use POSNR in the key list, SE11 did not notice that your index is very unlikely to be useful.

Regards,

Raymond

[ThomasZloch]

So you are saying that if all selected columns are part of an index, then access if faster because only the separately stored index data needs to be accessed?

I now remember reading about this, but in my limited experience I have not come across a practical use case yet.

Thanks for pointint it out.

Thomas

[Former Member]

Hi,

Then can u please tell me the fields order for creating the index for VBAK and VBAP tables.

As per my query.

SELECT  vkbur vkorg vtweg vbeln kunnr angdt bnddt  bstdk spart
        INTO TABLE it_vbak FROM vbak
        WHERE vbeln IN s_vbeln
          AND auart = 'ZABC'
          AND vkorg = 'ZORG'
          AND vkbur IN s_vkbur
          AND bstdk IN s_bstdk
          AND kunnr IN s_kunnr.

   IF it_vbak[] IS NOT INITIAL.
      SELECT vbeln posnr matnr kwmeng ntgew werks spart charg

       INTO TABLE it_vbap FROM vbap
       FOR ALL ENTRIES IN it_vbak
       WHERE vbeln = it_vbak-vbeln
         AND matnr IN s_matnr
         AND matkl IN s_matkl
         AND spart IN s_spart    

         AND werks IN s_werks.

endif.

Please advice...

Regards

Ram

[ThomasZloch]

If you really want to create a new index just for this one use case, I would only include the fields MANDT, AUART and VKORG, since the latter have an EQ condition and are hopefully somewhat selective (several  distinct values, small percentage of hits compared to overall data) . The sequence of AUART and VKORG depends on which field is more selective, with the more selective one coming first. You can find out using transaction TAANA.

I don't think it makes sense to include anything else, since the S_... ranges could hold anything at runtime.

You might also want to consider a join of VBAK and VBAP, then the CBO could maybe take advantage of any selection criteria filled for VBAP at runtime, avoiding selecting unnecessary VBAK records. You could also try including some of the tables as mentioned by Yuri, VAKPA, VAKMA, etc. in case users do fill S_KUNNR or S_MATNR at runtime. See SAP note 185530 for SD performance tips.

Thomas

[Former Member]

Hi,

Then for VBAP there is no need of creating index ?

IF it_vbak[] IS NOT INITIAL.
      SELECT vbeln posnr matnr kwmeng ntgew werks spart charg 

       INTO TABLE it_vbap FROM vbap
       FOR ALL ENTRIES IN it_vbak
       WHERE vbeln = it_vbak-vbeln
         AND matnr IN s_matnr
         AND matkl IN s_matkl
         AND spart IN s_spart    

         AND werks IN s_werks.

endif.

If index requires then with what fields i need to create index on VBAP?

Regards

Ram

[yuri_ziryukin]

Yes, Thomas, exactly lke this. We (SAP Support) are using it from time to time during performance optimization.

[Former Member]

Hi,

Thnaks for the help yuri,thomas and raymond.

Points are awarded.

Regards

Ram