sap abap question

[Former Member]

can we add secondary indexes in sap defined tables

[Former Member]

Yes we can add indexes into standard SAP tables.

To do that, in SE11 give the table name and display it. You will get INDEXES.. button on the application tooolbar. Press that, and there is an option to create your own index

Regards,

Sandip

[Former Member]

Hi

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.

Goto SE11

enter table

from the menu goto Indexes

create the indexes by pressing the CREATE button with the required fields

and use those fields when fetching data from this table in the where condition of the select statement

Reward points for useful Answers

Regards

Anji

[Former Member]

Yes, you can, but you probaly shouldn't. You need to do some analysis before doing this.

Rob

[Former Member]

yes you can add secondary indexes to sap tables.

[Former Member]

Hi Ramesh,

Yes, we can...

reward for all useful asnwers and close the thread.

[Former Member]

Yes we can add secondary index for sap table ... it is one of the Performance tuning point .

Secondary Database

First it must be stated that table design is a more logical work while index design is rather technical. In table design it might make sense to place certain fields (client, company code, ...) in the beginning. In index design, this is not advisable. Very important for an index is that it contains very selective fields in the beginning. Those are fields like object numbers. Not selective are client, company code, ...

Indexes should be small (few fields). The Database optimizer can combine two or more indexes to execute a query.

Indexes of one table should be disjoint (have few common fields), in order not to confuse the optimizer which index to use.

Note that each index slows the inserts into the table down. Updates are only slowed down if indexed fields are updated. In general, heavy inserted tables should have only few indexes while heavy selected tables might have more.

Creating Secondary Indexes

Procedure

1.In the maintenance screen of the table, choose Indexes.
If indexes already exist on the table, a list of these indexes is displayed. Choose .

2. In the next dialog box, enter the index ID and choose
The maintenance screen for indexes appears.

3.Enter an explanatory text in the field Short text.
You can then use the short text to find the index at a later time, for example with the R/3 Repository Information System.

4.Select the table fields to be included in the index using the input help for the Field name column.
The order of the fields in the index is very important. See What to Keep in Mind for Secondary Indexes.

5.If the values in the index fields already uniquely identify each record of the table, select Unique index.
A unique index is always created in the database at activation because it also has a functional meaning (prevents double entries of the index fields).

6.If it is not a unique index, leave Non-unique index selected.
In this case you can use the radio buttons to define whether the index should be created for all database systems, for selected database systems or not at all in the database.

7.Select for selected database systems if the index should only be created for selected database systems.
Click on the arrow behind the radio buttons. A dialog box appears in which you can define up to 4 database systems with the input help. Select Selection list if the index should only be created on the given database systems. Select Exclusion list if the index should not be created on the given database systems. Choose .

8.Choose acitvate .

Result

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

You can find information about the activation flow in the activation log, which you can call with Utilities ® Activation log. If errors occurred when activating the index, the activation log is automatically displayed.

What to Keep in Mind for Secondary Indexes

How well an existing index supports data selection from a table largely depends on whether the data selected with the index represents the data that will ultimately be selected. This can best be shown using an example.

An index is defined on fields FIELD1, FIELD2, FIELD3 and FIELD4 of table BSPTAB in this order. This table is accessed with the SELECT statement:

SELECT * FROM BSPTAB WHERE FIELD1 = X1 AND FIELD2 = X2 AND FIELD4= X4

.

Since FIELD3 is not specified more exactly, only the index sorting up to FIELD2 is of any use. If the database system accesses the data using this index, it will quickly find all the records for which FIELD1 = X1 and FIELD2 = X2. You then have to select all the records for which FIELD4 = X4 from this set.

The order of the fields in the index is very important for the accessing speed. The first fields should be those which have constant values for a large number of selections. During selection, an index is only of use up to the first unspecified field.

Only those fields that significantly restrict the set of results in a selection make sense for an index.

You can also create further indexes on a table in the ABAP Dictionary. These are called secondary indexes. This is necessary if the table is frequently accessed in a way that does not take advantage of the sorting of the primary index for the access. Different indexes on the same table are distinguished with a three-place index identifier.

reward points if it is usefull ...

Girish

[Former Member]

hi ramesh,

we can add secondary indexes in sap defined tables. just goto se11...enter table name--> display

now inside u will get indexes push button on application toolbar..just click on it..and create ....

in that in index name enter table name and give an ID for that index by Z....

enter the fields on which u want to do indexing...click on save and activate it...

hope this will be usefull...

please reward in case usefull...

regards,

prashant