INDEX CREATION

[Former Member]

Hi There

I have the following query to be optimized:

select a_{vbeln a}auart a_{vkorg a}vtweg a~spart

a_{angdt a}bnddt a_{guebg a}gueen a~vkgrp

a_{vkbur a}gsber a_{kunnr a}erdat a~erzet

a_{waerk a}vbtyp a_{autlf a}vsbed a~kvgr1

a_{kvgr2 a}kvgr3 a_{kvgr4 a}kvgr5 a~abrvw

a~abdis

into table t_vbak

from vbak as a inner join vbuk as b on b_{vbeln = a}vbeln

where

( ( a~erdat > pre_dat ) and

( a~erdat <= w_date ) ) and

a~vbtyp in s_doccat and

a~vbeln in s_ordno and

a~vkorg in s_vkorg and

a~vtweg in s_vtweg and

a~spart in s_spart and

( ( a~lifsk in s_lifsk ) or

( a~lifsk = ' ' ) ) and

b~abstk ne 'C'.

select w~mandt

w_{vbeln w}posnr w_{meins w}matnr w_{werks w}netwr

w_{kwmeng w}vrkme w_{matwa w}charg w~pstyv

w_{posar w}prodh w_{grkor w}antlf w_{kztlf w}lprio

w_{vstel w}route w_{umvkz w}umvkn w_{abgru w}untto

w_{awahr w}erdat w_{erzet w}fixmg w_{prctr w}vpmat

w_{vpwrk w}mvgr1 w_{mvgr2 w}mvgr3 w_{mvgr4 w}mvgr5

w_{bedae w}cuobj w~mtvfp

x_{etenr x}wmeng x_{bmeng x}ettyp x_{wepos x}abart

x~edatu

x_{tddat x}mbdat x_{lddat x}wadat x_{abruf x}etart

x~ezeit

into table t_vbap

from vbap as w

inner join vbep as x on x_{vbeln = w}vbeln and

x_{posnr = w}posnr and

x_{mandt = w}mandt

• BEGIN OF Change for EUCHG352069

for all entries in t_vbak

• End of changes for EUCHG352069

where

• BEGIN OF Change for EUCHG352069

w~vbeln in s_ordno and

w~vbeln = t_vbak-vbeln and

• ( ( w_{erdat > pre_dat ) and ( w}erdat <= w_date ) ) and

( ( ( erdat > pre_dat and erdat < p_syndt ) or

( erdat = p_syndt and erzet <= p_syntm ) ) ) and

• End of changes for EUCHG352069

w~matnr in s_matnr and

w~pstyv in s_itmcat and

w~lfrel in s_lfrel and

w~abgru = ' ' and

w~kwmeng > 0 and

w~mtvfp in w_mtvfp and

x~ettyp in w_ettyp and

x~bdart in s_req_tp and

x~plart in s_pln_tp and

x~etart in s_etart and

x~abart in s_abart and

( ( x_{lifsp in s_lifsp ) or ( x}lifsp = ' ' ) ).

Is it advisable to create an INDEX for improving this query performance?

If yes, upon which field of which table, I can create an index?

Plz suggest!

[Former Member]

Hi There

I have the following query to be optimized:

select a_{vbeln a}auart a_{vkorg a}vtweg a~spart

a_{angdt a}bnddt a_{guebg a}gueen a~vkgrp

a_{vkbur a}gsber a_{kunnr a}erdat a~erzet

a_{waerk a}vbtyp a_{autlf a}vsbed a~kvgr1

a_{kvgr2 a}kvgr3 a_{kvgr4 a}kvgr5 a~abrvw

a~abdis

into table t_vbak

from vbak as a inner join vbuk as b on b_{vbeln = a}vbeln

where

( ( a~erdat > pre_dat ) and

( a~erdat <= w_date ) ) and

a~vbtyp in s_doccat and

a~vbeln in s_ordno and

a~vkorg in s_vkorg and

a~vtweg in s_vtweg and

a~spart in s_spart and

( ( a~lifsk in s_lifsk ) or

( a~lifsk = ' ' ) ) and

b~abstk ne 'C'.

select w~mandt

w_{vbeln w}posnr w_{meins w}matnr w_{werks w}netwr

w_{kwmeng w}vrkme w_{matwa w}charg w~pstyv

w_{posar w}prodh w_{grkor w}antlf w_{kztlf w}lprio

w_{vstel w}route w_{umvkz w}umvkn w_{abgru w}untto

w_{awahr w}erdat w_{erzet w}fixmg w_{prctr w}vpmat

w_{vpwrk w}mvgr1 w_{mvgr2 w}mvgr3 w_{mvgr4 w}mvgr5

w_{bedae w}cuobj w~mtvfp

x_{etenr x}wmeng x_{bmeng x}ettyp x_{wepos x}abart

x~edatu

x_{tddat x}mbdat x_{lddat x}wadat x_{abruf x}etart

x~ezeit

into table t_vbap

from vbap as w

inner join vbep as x on x_{vbeln = w}vbeln and

x_{posnr = w}posnr and

x_{mandt = w}mandt

• BEGIN OF Change for EUCHG352069

for all entries in t_vbak

• End of changes for EUCHG352069

where

• BEGIN OF Change for EUCHG352069

w~vbeln in s_ordno and

w~vbeln = t_vbak-vbeln and

• ( ( w_{erdat > pre_dat ) and ( w}erdat <= w_date ) ) and

( ( ( erdat > pre_dat and erdat < p_syndt ) or

( erdat = p_syndt and erzet <= p_syntm ) ) ) and

• End of changes for EUCHG352069

w~matnr in s_matnr and

w~pstyv in s_itmcat and

w~lfrel in s_lfrel and

w~abgru = ' ' and

w~kwmeng > 0 and

w~mtvfp in w_mtvfp and

x~ettyp in w_ettyp and

x~bdart in s_req_tp and

x~plart in s_pln_tp and

x~etart in s_etart and

x~abart in s_abart and

( ( x_{lifsp in s_lifsp ) or ( x}lifsp = ' ' ) ).

Is it advisable to create an INDEX for improving this query performance?

If yes, upon which field of which table, I can create an index?

Plz suggest!

[Former Member]

Hai

Santo,

The creation of index is required whenever your are retrieving data frequently from fields in a table other than primary keys.because , an primary index is automatically cretaed when you activate the table first time upon creation.

To get solution to your question,

You are identifying which fields other than primary key fields are frequently used in ur program. Then , u create index on those fields only.

I hope this gives req. answer for ur question.

If useful, reward points.

Thank you,

G.V.K.Prasad

[Former Member]

Hi,

An index is a copy of a database table that is reduced to certain fields. This copy is always in sorted form. Sorting provides faster access to the data records of the table, for example, when using a binary search. A table has a primary index and a secondary index. The primary index consists of the key fields of the table and is automatically created in the database along with the table. Secondary Indexes are necessary if the table is frequently accessed in a way that does not take advantage of the primary index. Different indexes for the same table are distinguished from one another by a separate index name. The index name must be unique. Whether or not an index is used to access a particular table, is decided by the database system optimizer. This means that an index might improve performance only with certain database systems. You specify if the index should be used on certain database systems in the index definition. Indexes for a table are created when the table is created (provided that the table is not excluded for the database system in the index definition). If the index fields represent the primary keys of the table, that is, if they already uniquely identify each record of the table, the index is referred to as an unique index.

Procedure :

1. Choose the Indexes tab.

2. To create an index, choose New.

3. Enter a name for the index. Index names, like table names, also have a prefix, followed by an underscore.

If the name of an index was registered on the name server, it cannot be deleted.

4. To select table fields, choose New.

5. if the index is a unique index,

6. If the index is used for all databases, choose and whether it is to be created for all databases. Choose the appropriate checkboxes.

7. Choose File® Save All Metadata.

Primary index : Its the index which is automatically created for the PRIMARY KEY FIELD(S) of the table.

The primary index is always created automatically in the R/3 System. It consists of the primary key fields of the database table. This means that for each combination of fields in the index, there is a maximum of one line in the table. This kind of index is also known as UNIQUE.

Secondary index : Its created as and when required,

based upon other field(s) of the table,

on which search criteria is used in sqls.

Secondary indexes should contain columns that you use frequently in a selection, and that are as highly selective as possible. The fewer table entries that can be selected by a certain column, the higher that column’s selectivity. Place the most selective fields at the beginning of the index. Your secondary index should be so selective that each index entry corresponds to at most five percent of the table entries. If this is not the case, it is not worth creating the index. You should also avoid creating indexes for fields that are not always filled, where their value is initial for most entries in the table.

Regards,

Shiva.

[Former Member]

Question closed as no related answers were obtained