Solved: Table Index

Former Member · ‎2007 Jul 26

Hi friends

what is use of index? and How can we make it and use it in our table?

plz reply me.

thanks in advance.

Former Member · ‎2007 Jul 26

Hi,

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

Regards,

Priyanka.

seshatalpasai_madala · ‎2007 Jul 26

Hi,

Indexes

Indexes - What are they and how can I use them?

An index helps to speed up selection from the database. An index is a sorted copy of selected database table fields.

The primary index is always automatically created in an ABAP-based SAP system. It consists of the primary key fields of the database table. This means, for each combination of the index fields exists a maximum of one record in the table. This kind of index is called a UNIQUE index.

If the primary index cannot be used to determine selection result, (for example, the WHERE condition does not contain any primary index fields), the system searches the whole table. To prevent this, and determine the selection result by searching through a restricted number of database records, you can create a secondary index.

However, you should not define an index for all possible fields in the WHERE condition.

Creating a secondary index

You can use the transaction ABAP Dictionary Change → Indexes... → Create to create an index. To make the index unique, select UNIQUE. To specify the fields that will comprise the index, choose "Choose fields". Then save and activate the index.

When to create an index

It is worth creating a secondary index when:

You want to select table entries based on fields that are not contained in an index, and the response times are very slow.

The EXPLAIN function in the SQL trace shows which index the system is using. You can generate a list of the database queries involved in an action by entering Transaction ST05 and choosing Trace on → Execute action → Trace off → List trace. If you execute the EXPLAIN SQL function on a EXEC, REEXEC, OPEN, REOPEN or PREPARE statement, the system returns a list containing the index used in the database query.

The field or fields of the new secondary index are so selective that each index entry corresponds to at most 5% of the total number of table entries. Otherwise, it is not worth creating the index.

The database table is accessed mainly for reading entries.

Using an index consisting of several fields

Even if an index consists of several fields, you can still use it when only a few of the fields actually appear in the WHERE clause. The sequence in which the fields are specified in the index is important. You can only use a field in the index if all of the preceding fields in the index definition are included in the WHERE condition.

An index can only support search criteria which describe the search value positively, such as EQ or LIKE. The response time of conditions including NEQ is not improved by an index.

Optimal number of fields for an index

An index should only consist of a few fields; as a rule, no more than four. This is because the index has to be updated each time you change its fields in a database operation.

Fields to include in an index

Include fields that are often selected and have a high selectivity. In other words, you need to check the proportion of the table entries that can be selected with this field. The smaller the proportion, the more selective the field. You should place the most selective fields at the beginning of the index.

If all of the fields in a SELECT statement are contained in the index, the system does not access the data a second time following the index access. If there are only a few fields in the SELECT statmeent, you can improve performance significantly by including all of these fields in the index.

You should not include a field in an index if its value is initial for most of the table entries.

Optimal number of indexes for a table

You should not create more than five indexes for any one table because:

Whenever you change table fields that occur in the index, the index itself is also updated.

The amount of data increases.

The optimizer has too many chances to make mistakes by using the 'wrong' index.

If you are using more than one index for a database table, ensure that they do not overlap.

Avoiding OR conditions

The optimizer generally stops if the WHERE condition contains an OR expression. In other words, it does not evaluate the fields in the OR expression with reference to the index.

An exception to this are OR statements standing on their own. Try to reformulate conditions containing an OR expression for one of the indexed fields. For example, replace:

SELECT * FROM SPFLI

WHERE CARRID = 'LH'

AND (CITYFROM = 'FRANKFURT' OR CITYFROM = 'NEW YORK').

with:

SELECT * FROM SPFLI

WHERE (CARRID = 'LH' AND CITYFROM = 'FRANKFURT')

OR (CARRID = 'LH' AND CITYFROM = 'NEW YORK').

Problems with IS NULL

The value NULL is not stored in the index structure of some database systems. The consequence of this is that the index is not used for that field.

Regards,

Sesh

Former Member · ‎2007 Jul 26

Index is used for faster access of data base tables.

Indexes - Form and Use

Indexes help to speed up selection from the database. They consist of a sorted copy of certain database table fields.

The primary index is always created automatically in the SAP System. It consists of the primary key fields of the database table, and there is at most one record in the table matching each possible combination of these fields. This kind of index is called a UNIQUE index.

If you cannot use the primary index to determine a selection result (for example, WHERE condition may not contain any primary index fields), the system searches the whole table. To prevent this, and determine the selection result by searching through a restricted number of database records, you can create a secondary index.

However, you should not define an index for all possible fields in the WHERE condition.

Creating an index

You can create an index in Transaction SE11 by choosing Change → Indexes... → Create. To make the index unique, select UNIQUE. To specify the fields that will comprise the index, choose "Choose fields". You then need to save and activate the index.

When to create an index

It is worth creating an index when:

You want to select table entries based on fields that are not contained in an index, and the response times are very slow.

The EXPLAIN function in the SQL trace shows which index the system is using. You can generate a list of the database queries involved in an action by entering Transaction ST05 and choosing Trace on → Execute action → Trace off → List trace. If you execute the EXPLAIN SQL function on a EXEC, REEXEC, OPEN, REOPEN or PREPARE statement, the system returns a list containing the index used in the database query.

The field or fields of the new secondary index are so selective that each index entry corresponds to at most 5% of the total number of table entries. Otherwise, it is not worth creating the index.

The database table is accessed mainly for reading entries.

Using an index consisting of several fields

Even if an index consists of several fields, you can still use it when only a few of the fields actually appear in the WHERE clause. The sequence in which the fields are specified in the index is important. You can only use a field in the index if all of the preceding fields in the index definition are included in the WHERE condition.

An index can only support search criteria which describe the search value positively, such as EQ or LIKE. The response time of conditions including NEQ is not improved by an index.

Optimal number of fields for an index

An index should only consist of a few fields; as a rule, no more than four. This is because the index has to be updated each time you change its fields in a database operation.

Fields to include in an index

Include fields that are often selected and have a high selectivity. In other words, you need to check the proportion of the table entries that can be selected with this field. The smaller the proportion, the more selective the field. You should place the most selective fields at the beginning of the index.

If all of the fields in a SELECT statement are contained in the index, the system does not access the data a second time following the index access. If there are only a few fields in the SELECT statmeent, you can improve performance significantly by including all of these fields in the index.

You should not include a field in an index if its value is initial for most of the table entries.

Optimal number of indexes for a table

You should not create more than five indexes for any one table because:

Whenever you change table fields that occur in the index, the index itself is also updated.

The amount of data increases.

The optimizer has too many chances to make mistakes by using the 'wrong' index.

If you are using more than one index for a database table, ensure that they do not overlap.

Avoiding OR conditions

The optimizer generally stops if the WHERE condition contains an OR expression. In other words, it does not evaluate the fields in the OR expression with reference to the index.

An exception to this are OR statements standing on their own. Try to reformulate conditions containing an OR expression for one of the indexed fields. For example, replace:

SELECT * FROM SPFLI

WHERE CARRID = 'LH'

AND (CITYFROM = 'FRANKFURT' OR CITYFROM = 'NEW YORK').

with:

SELECT * FROM SPFLI

WHERE (CARRID = 'LH' AND CITYFROM = 'FRANKFURT')

OR (CARRID = 'LH' AND CITYFROM = 'NEW YORK').

Problems with IS NULL

The value NULL is not stored in the index structure of some database systems. The consequence of this is that the index is not used for that field.

1. PRIMARY INDEX.

To locate distinct records.

(1 field or 2 fields or 3 fields

COMBINATION

which can be used to UNIQUELY identify

a record)

2. SECONDARY INDEX.

They are generally used for faster access.

EG.

In a table there are 10 fields.

1,2 are primary fields (primary index)

3. But the table is queried many times

on field number 6 (eg).

So we can create a NEW Index

(Secondary index)

only on that 6th field.

4. Due to this,

the sql will become faster

because NOW

the database will search on the

basis of secnodary index (made on 6th field)

What is the difference between primary index and secondary index?

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.

CREATION

go to SE11

enter table name

press display

from menu GOTO-> indexes

press create button

enter the fields for which you need the index

save and activate

use those fields in the Where condition of the select statement and see

regards,

srinivas

*reward for useful answers*

Former Member · ‎2007 Jul 26

Hi

Indexes are useful to fetch the data from the tables Faster

By default eevry table have a Primary index based on the Primary key fields

when we use other than Key fields in the where condition of the select then we creat a secondary index for those fiuelds used in select where condition

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.

CREATION

go to SE11

enter table name

press display

from menu GOTO-> indexes

press create button

enter the fields for which you need the index

save and activate

use those fields in the Where condition of the select statement and see

Reward points for useful Answers

Regards

Anji

Former Member · ‎2007 Jul 26