Index

[Former Member]

Hi All,

My query is how to retrieve data from a table using an index?

We all know that an index can make data access faster.

But i'm confused how to use this?

Thanks & Regards

Santhosh

[Former Member]

Hi Santosh,

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.

<b>Formulating Conditions for Indexes</b>

You should bear in mind the following when formulating conditions for the WHERE and HAVING clauses so that the system can use a database index and does not have to use a full table scan.

Check for Equality and Link Using AND

The database index search is particularly efficient if you check all index fields for equality (= or EQ) and link the expressions using AND.

Use Positive Conditions

The database system only supports queries that describe the result in positive terms, for example, EQ or LIKE. It does not support negative expressions like NE or NOT LIKE.

If possible, avoid using the NOT operator in the WHERE clause, because it is not supported by database indexes; invert the logical expression instead.

Using OR

The optimizer usually stops working when an OR expression occurs in the condition. This means that the columns checked using OR are not included in the index search. An exception to this are OR expressions at the outside of conditions. You should try to reformulate conditions that apply OR expressions to columns relevant to the index, for example, into an IN condition.

Using Part of the Index

If you construct an index from several columns, the system can still use it even if you only specify a few of the columns in a condition. However, in this case, the sequence of the columns in the index is important. A column can only be used in the index search if all of the columns before it in the index definition have also been specified in the condition.

Checking for Null Values

The IS NULL condition can cause problems with indexes. Some database systems do not store null values in the index structure. Consequently, this field cannot be used in the index.

Avoid Complex Conditions

Avoid complex conditions, since the statements have to be broken down into their individual components by the database system.

<b>Reward points, if useful.</b>

Regards,

Atin

[Former Member]

Hi Santhosh,

It is simple. First make sureabout the order of the fields declaration while declare the internal tables and in select statement should match with the fields order in data base table fields order. Now while write the select statement check the where condition. In where condition you mentioned secondary indexes. Make sure those orders with the data base table secondary indexes order. For that open required table and menu path UTILITIES>DATABASE OBJECT>DISPLAY. at last you will get the secondary index list for that perticular table.

Hope this helps you. Reply for queries, shall post the updates.

Regards.

Kumar.

[Former Member]

Hi,

In general there r 2 types of index.

1) Primary

2) Secondary

Primary index nothing but key fields in table.

For display/create Secondary index - Go to table in SE11 and choose 'indexes' push buton(CTRL+F5)

For improving performance of query try to give as many primary index fields in Where Condition

i.e ) Key Fields.

If Secondary index is available then.....

Further u can specify Secondary index fields in the same order as that of in the Table definition

If No Secondary index , then create new secondary index .

Set index over the fields that is often occurs in where clause , and dont set index over the fields that is often updatable.

Reward Points if useful.

Regards,

A.Thuyavan.

[Former Member]

Indexes

are described as a copy of a database table reduced to specific fields. This data exists in sorted form. This sorting form ease fast access to the field of the tables. In order that other fields are also read, a pointer to the associated record of the actual table are included in the index. Yhe indexes are activated along with the table and are created automatically with it in the database.

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).).

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.

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.

How to Check if an Index is Used

Procedure

Open a second session and choose System ® Utilities ® Performance trace.

The Trace Requests screen appears.

Select Trace on.

The SQL trace is activated for your user, that is all the database operations under your user are recorded.

In the first window, perform the action in which the index should be used.

If your database system uses a cost-based optimizer, you should perform this action with as representative data as possible. A cost-based optimizer tries to determine the best index based on the statistics.

In the second session, choose Trace off and then Trace list.

Result

The format of the generated output depends on the database system used. You can determine the index that the database used for your action with the EXPLAIN function for the critical statements (PREPARE, OPEN, REPOPEN).

reward points if it is usefull ...

Girish

[Former Member]

Santhosh,

Select statement is same like general.Only difference is when you create INDEX

it it will work as key column and access will be faster.(Just like primary key)>But creating INdexes also have some constraints.

Don't forget to reward if useful......

[seshatalpasai_madala]

HI,

You cannot force the USE of an INDEX. Its upto the Database Optimizer that is there in the Work process that decides wether to use the INDEX Or to IGNORE.

It depends upon your fields in the WHERE Clause.

More the fields of the INDEX in your WHERE clause more the chance of INDEX being used.

Also make sure you use fields in the same sequnce as you have declared in your INDEX.

Regards,

Sesh

[Former Member]

Hi,

Built u r query like this

Select Field1 field 2 INTO -- -- From Table Where Field3 = field3 AND

field4 = field4.

field3 it may be a primary key of u r table or

field3 and field4 it should be in sequence as per the index u r using.

If u use the where condition as per the Index field sequence it will take automatically the index or it will search all records

If can check the index in sto5 analysis.

Regards,

Nandha

Reward if it helps

[Former Member]

You can force the sql statement to use a particular index by using %_HINTS parameter.

For eg:

SELECT carrid connid cityfrom
FROM spfli INTO (xcarrid, xconnid, xcityfrom)
WHERE carrid = 'LH ' AND cityfrom = 'FRANKFURT'
%_HINTS ORACLE 'INDEX("SPFLI" "SPFLI~001")'.
WRITE: / xcarrid, xconnid, xcityfrom.
ENDSELECT.

[Former Member]

The Index(s) should be used only if required or else it will effect the performance

and if u r using the indexes then they should be used in series in the where condition or else they may cause some performance problem