Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

creating secondary index

Former Member
0 Kudos
116

hi,

what r secondanry indexes.How to create my program selects from database table zuip5a. and selection screen consists of so many select options and parameters. so while testing the program is giving poor performance. if i create secondary indexes will this improve the performance of the code.please help me

types: begin of ty_data1,

ryear like zupi5a-ryear, "Fiscal year

rbukrs like zupi5a-rbukrs, "Company code

racct like zupi5a-racct, "Account number

rzzps_posid like zupi5a-rzzps_posid, "WBS element

rzzmtit like zupi5a-rzzmtit, "MPM title

rzzmfor like zupi5a-rzzmfor, "MPM format

rzzmatnr like zupi5a-rzzmatnr, "Material number

rzzcou like zupi5a-rzzcou, "Country

rzzfow like zupi5a-rzzfow, "Financial owner

rzzoow like zupi5a-rzzoow, "Operational owner

rzzcon like zupi5a-rzzcon, "Licensee Contract

rzzloc like zupi5a-rzzloc, "Licensor Contract

kostl like zupi5a-kostl, "Cost center

zzfam like zupi5a-zzfam, "Fame Number

zzfor like zupi5a-zzfor, "Format

zzprd like zupi5a-zzprd, "Product Line

zzwin like zupi5a-zzwin, "Window group

zzwig like zupi5a-zzwig, "Window

rtcur like zupi5a-rtcur, "Currency Key

TSL like zupi5a-TSL,

HSL like zupi5a-hSL,

KSL like zupi5a-KSL,

MSL like zupi5a-MSL,

end of ty_data1.

types: begin of ty_final,

ryear like zupi5a-ryear, "Fiscal year

rbukrs like zupi5a-rbukrs, "Company code

racct like zupi5a-racct, "Account number

rzzps_posid like zupi5a-rzzps_posid, "WBS element

rzzmtit like zupi5a-rzzmtit, "MPM title

rzzmfor like zupi5a-rzzmfor, "MPM format

rzzmatnr like zupi5a-rzzmatnr, "Material number

rzzcou like zupi5a-rzzcou, "Country

rzzfow like zupi5a-rzzfow, "Financial owner

rzzoow like zupi5a-rzzoow, "Operational owner

rzzcon like zupi5a-rzzcon, "Licensee Contract

rzzloc like zupi5a-rzzloc, "Licensor Contract

kostl like zupi5a-kostl, "Cost center

rtcur like zupi5a-rtcur, "Currency Key

zzfam like zupi5a-zzfam, "Fame Number

zzfor like zupi5a-zzfor, "Format

zzprd like zupi5a-zzprd, "Product Line

zzwin like zupi5a-zzwin, "Window group

zzwig like zupi5a-zzwig, "Window

tsl like zupi5a-tsl, "Amount Transaction currency

hsl like zupi5a-hsl, "Amount Co. code currency

ksl like zupi5a-ksl, "Amount Group currency

msl like zupi5a-msl, "Quantity

msg(83) type c, "message--Ramya

end of ty_final.

data:

i_data type standard table of ty_data1,

i_final type standard table of ty_final,

data:

wa_data type ty_data1,

wa_final type ty_final,

selection-screen begin of block 1 with frame title text-001.

parameters:

p_rldnr like zupi5a-rldnr obligatory default 'Z5', "Ledger

p_rrcty like zupi5a-rrcty obligatory default '0', "Record type

p_rvers like zupi5a-rvers obligatory default '001', "Version

p_ryear like zupi5a-ryear obligatory. "Fiscal year

select-options:

s_rpmax for zupi5a-poper obligatory no-extension. "Period

parameters:

p_rbukrs like zupi5a-rbukrs obligatory, "Company code

p_rtcur like zupi5a-rtcur. "obligatory. "Currency Key

select-options:

s_racct for zupi5a-racct obligatory, "Account number

s_rbusa for zupi5a-rbusa, "Business area

s_rzzps for zupi5a-rzzps_posid obligatory no intervals, "WBS Element

s_rzmtit for zupi5a-rzzmtit, "MPM title

s_rzmfor for zupi5a-rzzmfor, "MPM format

s_zmatnr for zupi5a-rzzmatnr, "Material number

s_rzzcou for zupi5a-rzzcou, "Country

s_rkunnr for zupi5a-rzzkunnr, "Customer number

s_rzzfow for zupi5a-rzzfow, "Financial owner

s_rzzoow for zupi5a-rzzoow, "Operational owner

s_rzzcon for zupi5a-rzzcon, "Licence contract

s_raufnr for zupi5a-rzzaufnr, "Order number

s_rzzloc for zupi5a-rzzloc, "Licensor contract

s_blart for zupi5a-blart, "Document type

s_zzrrc for zupi5a-zzrrc, "Return reason code

s_zztsc for zupi5a-zztsc, "Type of sales/Sales channel

s_zzfam for zupi5a-zzfam, "Fame number

s_zzfor for zupi5a-zzfor, "Format

s_zzgen for zupi5a-zzgen, "Movie Genre

s_zzprd for zupi5a-zzprd, "Product Line

s_zzfmt for zupi5a-zzfmt, "Price point

s_zzwin for zupi5a-zzwin, "Window group

s_zzwig for zupi5a-zzwig, "Window

s_kostl for zupi5a-kostl. "Cost center

selection-screen end of block 1.

select ryear rbukrs racct rzzps_posid rzzmtit

rzzmfor rzzmatnr rzzcou rzzfow rzzoow rzzcon

rzzloc kostl

zzfam zzfor zzprd zzwin zzwig

rtcur

TSL HSL KSL MSL

from zupi5a

into table i_data bypassing buffer

where rldnr = p_rldnr

and rrcty = p_rrcty

and rvers = p_rvers

and ryear = p_ryear

  • and rtcur = p_rtcur

  • and rpmax in s_rpmax

and POPER in s_rpmax

and rbukrs = p_rbukrs

and racct in s_racct

and rbusa in s_rbusa

and rzzps_posid in s_rzzps

and rzzmtit in s_rzmtit

and rzzmfor in s_rzmfor

and rzzmatnr in s_zmatnr

and rzzcou in s_rzzcou

and rzzkunnr in s_rkunnr

and rzzfow in s_rzzfow

and rzzoow in s_rzzoow

and rzzcon in s_rzzcon

and rzzaufnr in s_raufnr

and rzzcon in s_rzzloc

and blart in s_blart

and zzrrc in s_zzrrc

and zztsc in s_zztsc

and zzfam in s_zzfam

and zzfor in s_zzfor

and zzgen in s_zzgen

and zzprd in s_zzprd

and zzfmt in s_zzfmt

and zzwin in s_zzwin

and zzwig in s_zzwig

and kostl in s_kostl.

if sy-subrc NE 0.

message i001.

endif.

FORM populate_final_table.

loop at i_data into wa_data.

wa_final-ryear = wa_data-ryear.

wa_final-rbukrs = wa_data-rbukrs.

wa_final-racct = wa_data-racct.

wa_final-rzzps_posid = wa_data-rzzps_posid.

wa_final-rzzmtit = wa_data-rzzmtit.

wa_final-rzzmfor = wa_data-rzzmfor.

wa_final-rzzmatnr = wa_data-rzzmatnr.

wa_final-rzzcou = wa_data-rzzcou.

wa_final-rzzfow = wa_data-rzzfow.

wa_final-rzzoow = wa_data-rzzoow.

wa_final-rzzcon = wa_data-rzzcon.

wa_final-rzzloc = wa_data-rzzloc.

wa_final-kostl = wa_data-kostl.

wa_final-zzfam = wa_data-zzfam.

wa_final-zzfor = wa_data-zzfor.

wa_final-zzprd = wa_data-zzprd.

wa_final-zzwin = wa_data-zzwin.

wa_final-zzwig = wa_data-zzwig.

wa_final-rtcur = wa_data-rtcur.

wa_final-tsl = wa_data-tsl.

wa_final-hsl = wa_data-hsl.

wa_final-ksl = wa_data-ksl.

wa_final-mSL = wa_data-mSL.

append wa_final to i_final.

clear : wa_final-ryear,

wa_final-rbukrs, wa_final-racct,

wa_final-rzzps_posid, wa_final-rzzmtit, wa_final-rzzmfor,

wa_final-rzzmatnr, wa_final-rzzcou,

wa_final-rzzfow, wa_final-rzzoow, wa_final-rzzcon,

wa_final-rzzloc,

wa_final-kostl,

wa_final-zzfam,

wa_final-zzfor,

wa_final-zzprd, wa_final-zzwin,

wa_final-zzwig,

wa_final-rtcur,

wa_final-tsl,

wa_final-hsl, wa_final-ksl, wa_final-msl.

endloop.

please help me out.

Thanks and Regards,

Ramya.

6 REPLIES 6

Former Member
0 Kudos
62

Hi,

Go to SE11 and open table zuip5a, click on the tab INDEX next to technical settings ... then you will see option to create the index.

The index created this way are secondary index, primary index is the primary kes in the table.

Rgds,

Pankaj

Former Member
0 Kudos
62

Hi,

there are two types of indexes:

Primary

Secondary

Primary index is the one which is made up with the combination of the primary keys in the table

Secondary index is the one which we ourselve create on the basis of the requirement.

suppose if u have to write a select where you are using the fields which are not the primary fields of the table, you can create a secondary index for the group of the fields on which you want to select the data from database table.

Index enhances the performance of the program hence its recommanded to create a secondary index incase if you are using those fields frequently in the select queries in the program.

below is the method of creating the secondary index.

Go to SE11:

Table ZUIP5A, click INDEX next to technical settings

Here you can create the secondary index having the fields as per your requirement.

I hope it will be helpful for you.

Former Member
0 Kudos
62

Hi,

Pls check ur select statements. In that, have u given the filed names in the wright order which is in ur table?..

Regards,

Ari

0 Kudos
62

hi,

i have given in the same order as in table zupi5a(database table)

Former Member
0 Kudos
62

hi ramya,

the secondary index is used to increase the performance.we caanot create a query basing on the non-primary key (which is not a primary key) if the secondary index is not created.so we need to create the secondary index first to make a search basing on the non-primary keys.

and one important thing to note is u can create a maximum 15 secondary indexes of one table.

steps to create the secondary index:

1. open your ztable.

2.click on indexes

3.index name = 1.

4.enter.

5.short des: secondary index

6. click the unique index (database index required) radio button.

7.

-


field name short description

-


mandt

non-primary field name description

non-primary field name description

-


Former Member
0 Kudos
62

Dear Ramya,

Creating Secondary Indexes

Proceed as follows to create a secondary index on a table:

In the field maintenance screen for the table, choose Goto --> Indexes.

1. If you went to the field maintenance screen of the table in display mode, only correct the index (and not the table).

If indexes already exist on the table, a list of these indexes is displayed. Choose Create. A dialog box appears in which you must enter the three-place index identifier. If there are no indexes, go directly to the dialog box.

2. Enter the index identifier and choose Continue.

You will go to the maintenance screen for indexes.

3. Enter an explanatory short text in the field Short text.

Choose TabFields.

4. A list of all the fields of the table is displayed.

5. Select the fields which you want to copy to the index.

6. Choose Copy.

The selected fields are copied to the index.

7. If the values in the index fields already uniquely identify each record of the table, select Unique index.

A unique index is automatically created on the database during activation because a unique index also has a functional meaning (prevents double entries of the index fields).

8. If it is not a unique index, leave Non-unique index selected. In this case you can use the corresponding radio buttons to define whether the index should be created automatically on the database for all database systems, for selected database systems or for no database system.

9. If you chose For selected database systems, you must specify these systems.

You have two possibilities here:

List of inclusions: The index is only created automatically during activation for the database systems specified in the list. The index is not created on the database for the other database systems.

List of exclusions: The index is not created automatically on the database during activation for the specified database systems. The index is automatically created on the database for the other database systems.

Click on the arrow symbol behind the radio buttons. A dialog box appears in which you can define up to 4 database systems. Use the corresponding radio buttons to decide whether this list should be treated as a list of inclusions or exclusions.

10. Activate the index with Index ® Activate. The activation log tells you about the flow of the activation. Call it with Utilities ® Act.log. If an error occurred when activating the secondary index, you will automatically go to this log.

The secondary index is automatically created on the database during activation if the corresponding table has already been created there and index creation was not excluded for the database system.

Best Regards,

Rajesh.

Please reward points if found helpful.