topic Re: secondry indexes in Application Development and Automation Discussions

secondry indexes

Former Member — Thu, 04 Oct 2007 06:47:27 GMT

what will happen when we create secondary index. why don't we create more than 5 secondary indexes and how the system will(the flow) search for records after creating an index.

Re: secondry indexes

former_member404244 — Thu, 04 Oct 2007 06:51:56 GMT

Hi,

check the belwo links on secondary indexes..

http://help.sap.com/saphelp_nw04/helpdata/en/cf/21eb20446011d189700000e8322d00/content.htm

http://help.sap.com/saphelp_nw04/helpdata/en/cf/21eb47446011d189700000e8322d00/content.htm

Regards,

Nagaraj

Re: secondry indexes

Former Member — Thu, 04 Oct 2007 07:06:13 GMT

Hi Dharani,

Primary indexes are existing indexes we can use directly those indexes. we need to create the secondary indexes. In tables we will use both primary and secondary indexes.

Create secondary indexes step by step procedure.

1. Secondary index is created to enhance the performance. If you create wrongly defined index, it will decreases the performance.

Follow the below steps to create secondary index.

A.Go to t_code (se11) display your table name.

B.To create new index, click on index button on tool bar.

C.Give the index name.

D.Pass short text description for your index.

E.Bottom of the table pass field names on which you want index.

F.Save and activate.

2. You can create up to 16 indexes. But 5 is recommended...

Reward if helpful.

Regards,

Harini.S

Re: secondry indexes

Former Member — Thu, 04 Oct 2007 07:09:03 GMT

Hi,

It is not advisable to cretae a Secondary index unless the Performance is very poor , killing time.

It is not good to cretae Secondary index b'coz it will increase the LOAD ON DATABASE, however it will improve the prformance of program, but LOAD ON DB will be incraesed.

When ever u write a select statement to fetch the data from DB table, fisrt the control will go to DB table taking the FIELDS in WHERE condition , then it will check whetehr the Field (In where condn) , exist in PRIMARY INDEX(PRIMERY KEY) of the DB table , IF not, then control moves to Secondary index (which u have cretaed ) and fetch the data, If field doesnt exist even in Secondary index , then it will go to remaing fields in the sequence maintaind in DB table.

Thats the reason in "WHere condn., it is suggested to USE INDEX fields".

As far as i know , when u craete secondary indeces more than 5, it wont affect any improve in performance , i.e Data base will treat the remaing Secondary indeces (all above 5) as normal key and not as Secondary index.

Rvert back if any issues,

Reward with points if helpful.

regards,

Naveen

Re: secondry indexes

paruchuri_nagesh — Thu, 04 Oct 2007 07:11:52 GMT

secondary indexes can be used to speed up data retrieving

when u r using non primary key fields in where condition of a select statement

with out secondary index it wont fetch data

it fetches data only when there is corresponding secondary indexes

u can craete more than 15 indexes in atable

abap optimizer will assign secondary indexes user has nothing to do with this

to avoid complications

it is better to resrict 5 indexes for achieving performances

reward for use ful points

regards

Nagesh.Paruchuri

Re: secondry indexes

Former Member — Thu, 04 Oct 2007 07:12:07 GMT

econdary index: Additional indexes could be created considering the most frequently accessed dimensions of the table.

The database optimizer decides which index on the table should be used by the database to access data records.

You must distinguish between the primary index and secondary indexes of a table. The primary index contains the key fields of the table. The primary index is automatically created in the database when the table is activated. If a large table is frequently accessed such that it is not possible to apply primary index sorting, you should create secondary indexes for the table.

The indexes on a table have a three-character index ID. '0' is reserved for the primary index. Customers can create their own indexes on SAP tables; their IDs must begin with Y or Z.

If the index fields have key function, i.e. they already uniquely identify each record of the table, an index can be called a unique index. This ensures that there are no duplicate index fields in the database.

When you define a secondary index in the ABAP Dictionary, you can specify whether it should be created on the database when it is activated. Some indexes only result in a gain in performance for certain database systems. You can therefore specify a list of database systems when you define an index. The index is then only created on the specified database systems when activated