index

[Former Member]

hi viewers,

what is the use of index in tables ?

wat is the use of creating index ?

are the indexs are created only to ztables or we can create index to standard tables ?

when exactly we have to go with index ?

Regards,

Rajesh.

[Former Member]

Hi,

An index is an efficiency mechanism for quickly finding rows within a table. It is needed because data is stored in a table in the order in which it was added (the order of arrival).

The commonly used index is analogous to the primary index of a table.

Each R/3 table has a primary index built on the primary key of the table. This index is needed because the data in the table is stored in the order it was added to the table. When a row is added, the key information is sorted and copied to the index, along with a pointer to the original row.

We can also create secondary indexes. A secondary index enables you to search quickly on columns other than those in primary key. To support a where clause on a non-primary key field, a secondary index can be defined.

Each row in a table is given a sequential number when it is added to a table. The important columns are copied to an index and sorted. To find any piece of data, the system searches the index first to find the row number and then quickly finds the row.

Each R/3 table has a primary index built on the primary key of the table. This index is needed because the data in the table is stored in the order it was added to the table. When a row is added, the key information is sorted and copied to the index, along with a pointer to the original row. For example take a table(ztxlfa1). The data from the lifnr field in ztxlfa1 is copied to the index, sorted alphabetically, and stored along with the row number of the original row.

So when we create secondary indexes, the searching becomes much more faster.

Kindly reward points if useful

Regards,

Shanthi.

Edited by: Shanthi on Mar 3, 2008 1:03 PM

[Former Member]

Hi,

Primary and secondary indexes

Index: Technical key of a database table.

Primary index: The primary index contains the key fields of the table and a pointer to the non-key fields of the table. The primary index is created automatically when the table is created in the database.

Secondary index: Additional indexes could be created considering the most frequently accessed dimensions of the table.

Structure of an Index

An index can be used to speed up the selection of data records from a table.

An index can be considered to be a copy of a database table reduced to certain fields. The data is stored in sorted form in this copy. This sorting permits fast access to the records of the table (for example using a binary search). Not all of the fields of the table are contained in the index. The index also contains a pointer from the index entry to the corresponding table entry to permit all the field contents to be read.

When creating indexes, please note that:

An index can only be used up to the last specified field in the selection! The fields which are specified in the WHERE clause for a large number of selections should be in the first position.

Only those fields whose values significantly restrict the amount of data are meaningful in an index.

When you change a data record of a table, you must adjust the index sorting. Tables whose contents are frequently changed therefore should not have too many indexes.

Make sure that the indexes on a table are as disjunctive 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.)

Accessing tables using Indexes

The database optimizer decides which index on the table should be used by the database to access data records.

You must distinguish between the primary index and secondary indexes of a table. The primary index contains the key fields of the table. The primary index is automatically created in the database when the table is activated. If a large table is frequently accessed such that it is not possible to apply primary index sorting, you should create secondary indexes for the table.

The indexes on a table have a three-character index ID. '0' is reserved for the primary index. Customers can create their own indexes on SAP tables; their IDs must begin with Y or Z.

If the index fields have key function, i.e. they already uniquely identify each record of the table, an index can be called a unique index. This ensures that there are no duplicate index fields in the database.

When you define a secondary index in the ABAP Dictionary, you can specify whether it should be created on the database when it is activated. Some indexes only result in a gain in performance for certain database systems. You can therefore specify a list of database systems when you define an index. The index is then only created on the specified database systems when activated

Check out this link,

Hope this helps u,

Regards,

Arunsri

[Former Member]

*index* = index is created by system with primary field.

every time table is create the index is generated called as primary index.

when the data is added to the table it is added in a next row.

the index then created copies content of this & row num is assigned to it.

we can create index.

use 'index' radio button from the se11 to find or create index.

every table has primary index & secondary indexes they have unique identyfier = 'id'.