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: 

Secondary indexes

Former Member
0 Kudos
63

HI all,

How many types of Secondary indexes are there and what are they.

Regards,

Suresh.D

2 REPLIES 2

Former Member
0 Kudos
36

How well an existing index for a particular table 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.

The following selection is frequently made on address file ADRTAB:

SELECT * FROM ADRTAB WHERE TITEL = ‘Prof.’ AND NAME = X AND

VORNAME = Y.

The field TITLE would rarely restrict the records specified with NAME and

FIRSTNAME in an index on NAME, FIRSTNAME and TITLE, since there are

probably not many people with the same name and different titles. This would not

make much sense in this index. An index on field TITLE alone would make sense for

example if all professors are frequently selected.

Additional indexes can also place a load on the system since they must be adjusted each time

the table contents change. Each additional index therefore slows down the insertion of records in

the table.

For this reason, tables in which entries are very frequently written generally should only have a

few indexes.

The database system sometimes does not use a suitable index for a selection, even

if there is one. The index used depends on the optimizer used for the database

system. You should therefore check if the index you created is also used for the

selection (see How to Check if an Index is Used [Page 65]).).

Creating an additional index could also have side effects on the performance. This is

because an index that was used successfully for selection might not be used any

longer by the optimizer if the optimizer estimates (sometimes incorrectly) that the

newly created index is more selective.

BC - ABAP Dictionary SAP AG

What to Keep in Mind for Secondary Indexes

64 April 2001

The indexes on a table should therefore be as disjunct as possible, that is they

should contain as few fields in common as possible. If two indexes on a table have a

large number of common fields, this could make it more difficult for the optimizer to

choose the most selective index.

Former Member
0 Kudos
36

Hi Suresh,

Secondary Indexs are of 2 types.

1.B-Tree (Binary Tree) Index and

2.Bitmap Index.

When we create the Secondary Index, system automatically generates B-Tree or Bitmap Index based on the volume of data present in the cube.

A secondary Index is a way to efficiently access records in a database table by means of some piece of information other than the usual (primary) key. This index is simply another database whose keys are these pieces of information and whose data are the primary keys. Indexes speed up data selection from the database.

Hope this helps.

Please reward if useful.

Thanks,

Srinivasa