secondary index

[Former Member]

is there any disadvantages by using seconadary index?

How to create secondary index?

Regards,

pandu

[Former Member]

Hi Pandu,

It is totally depend on the table on which you are creating index and the fields.

To create index refer below link

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

Regards,

Atish

[Former Member]

Thank you Atish,

Generally what is the disadvantages for secondary index? we can we use this seconadry index?

Regards,

pandu.

[Former Member]

Hi pandu,

the main disadvantage is Index take the space in DB. So lets say you create index on table like BSIS without due consideration it will consume lot of space so it can have adverse effect as slow data retrieval.

Regards,

Atish

[Simha_]

Hi,

Indexes speed up data selection from the database. They consist of selected fields of a table, of which a copy is then made in sorted order. If you specify the index fields correctly in a condition in the WHERE or HAVING clause, the system only searches part of the index (index range scan).

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.

If you cannot use the primary index to determine the result set because, for example, none of the primary index fields occur in the WHERE or HAVING clause, the system searches through the entire table (full table scan). For this case, you can create secondary indexes, which can restrict the number of table entries searched to form the result set.

You specify the fields of secondary indexes using the ABAP Dictionary. You can also determine whether the index is unique or not. However, you should not create secondary indexes to cover all possible combinations of fields.

Only create one if you select data by fields that are not contained in another index, and the performance is very poor. Furthermore, you should only create secondary indexes for database tables from which you mainly read, since indexes have to be updated each time the database table is changed. As a rule, secondary indexes should not contain more than four fields, and you should not have more than five indexes for a single database table.

If a table has more than five indexes, you run the risk of the optimizer choosing the wrong one for a particular operation. For this reason, you should avoid indexes with overlapping contents.

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.

If all of the columns in the SELECT clause are contained in the index, the system does not have to search the actual table data after reading from the index. If you have a SELECT clause with very few columns, you can improve performance dramatically by including these columns in a secondary index.

Disadvantages:

If you are writting select statement on the table where in where clause you have non-primary keys then it takes longer to retriev data from DB table.

If you have select in dbtable on non-primary key you can create Secondary index with keys as per your where clause which result in faster DB table access.

But remember not to create too many secondary indexes on same table which could result in slow / degrage performance..

Hope this will give you clear idea about Secondary index.

Cheers,

SIMha.

[Former Member]

Hi pandurangarao ,

A secondary index is automatically used during searching when it improves the efficiency of the search.

Secondary indexes are maintained by the system and are invisible to the user.

Any column(s) may be specified as a secondary index, except columns declared using a LOB data type.

Columns in the PRIMARY KEY, the columns of a FOREIGN KEY and columns defined as UNIQUE are automatically indexed, (in the order in which they are defined in the key), and therefore creation of an index on these columns will not improve performance.

Secondary index tables are purely for Mimer SQL's internal use - you create the index, and Mimer SQL handles the rest.

If, for instance, you want to know which products were released on a specific date, Mimer SQL would have to search successively through the entire ITEMS table to find all items that matched the date you specified. If, however, you create a secondary index on release date, Mimer SQL would locate that date directly in the secondary index, which would save time.

Secondary indexes can improve the efficiency of data retrieval; but introduces an overhead for write operations (UPDATE, INSERT, DELETE). In general, you should create indexes only for columns that are frequently searched.

Indexes cannot be created directly on columns in views. However, since searching in a view is actually implemented as searching in the base table, an index on the base table will also be used in view operations.

Examples of Secondary Index

Create a secondary index called ITM_RELEASE_DATE on the RELEASE_DATE column in the ITEMS table:

CREATE INDEX itm_release_date ON items(release_date);

Primary key columns may also be included in a secondary index. If a table has the primary key columns A, B, and C, the primary index would cover all three columns of the primary key.

The following combinations of the columns in the primary key are automatically indexed: A, AB, and ABC. In addition, you could create secondary indexes on columns B, C, BC, AC etc.

An index may also be defined as UNIQUE, which means that the index value may only occur once in the table. (For this purpose, NULL is treated as equal to NULL). However, it is preferable to use unique constraints.

reward points if its usefull for u..