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
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

INdex

Former Member

‎06-17-2008 7:55 AM

0 Kudos

Hi Friends,

Can any body tell me wht is the use of indexes?

8 REPLIES 8

former_member202474
Contributor

‎06-17-2008 7:58 AM

0 Kudos

Hi,

Indexes are used in tables so that data retrieval will be fasten.

Check out the indexes for the std table BUT000.

      • reward if useful ***

Regards,

Ruby.

Former Member

‎06-17-2008 8:00 AM

0 Kudos

HI,

In data dictionary, we maintain the INDEXES to the DataBase table to retrive the data faster, we create the INDEXES if the present primary key set is not relavent and when we maintain the special set of known fields.

former_member195383
Active Contributor

‎06-17-2008 8:00 AM

0 Kudos

Hi...

If u are talking about indexes in dictionary table...then its used to improve the performance of select queries....

suppose there are two fields which u are fetching from dictionary table to an internal table....

If indexes are created then ur select query will use only those two fields while fetching from the dictionary table...and not the other fields...

It will improve the performance....

Reward points..if the above explanation is useful...

Former Member

‎06-17-2008 8:03 AM

0 Kudos

Hi

Indexes can be defined in the ABAP Dictionary to speed up access to data in a table. These indexes are also created in the database.

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.

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

Former Member

‎06-17-2008 8:04 AM

0 Kudos

hi

They are generally used for faster access.

EG.

In a table there are 10 fields.

1,2 are primary fields (primary index)

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

3. Due to this,

the sql will become faster

because NOW

the database will search on the

basis of secnodary index (made on 6th field)

Indexes:

An index can be considered a copy of a database table that has been reduced to certain fields. This copy is always in sorted form. Sorting provides faster access to the data records of the table, for example using a binary search. The index also contains a pointer to the corresponding record of the actual table so that the fields not contained in the index can also be read.

The primary index is distinguished from the secondary indexes of a table. 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

You can also create further indexes on a table in the ABAP Dictionary. These are called secondary indexes. This is necessary if the table is frequently accessed in a way that does not take advantage of the sorting of the primary index for the access.

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 system automatically creates the primary index. 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 HAVINGclauses, 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 create secondary indexes using the ABAP Dictionary. There you can create its columns and define it as UNIQUE. 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.

What is the difference between primary index and secondary index?

http://help.sap.com/saphelp_47x200/helpdata/en/cf/21eb2d446011d189700000e8322d00/frameset.htm

A difference is made between Primary & Secondary indexes to a table. the primary index consists of the key fields of the table and a pointer to the non-keys-fields of the table. The Primary index is generated automatically when a table is created and is created in the datebase as the same times as the table. It is also possible to define further indexes to a table in the ABAP/4 dictionary, which are then referred to as Secondary indexes.

Always it is not mandatory that an index should have all the key fields of a table. To see the index of a table

goto SE11->specify table name->click on the indexes... button on the application toolbar.

Based on your requirement you can you any of those index fields in the where clause of your query. Always its a better practice to use the index fields in the order specified. While selecting the records from a table it is always better to select the fields in the same order as specified in the table.

go thru this: http://sap.ittoolbox.com/documents/popular-q-and-a/specifying-the-index-to-be-used-2462

Cheers

Snehi Chouhan

Former Member

‎06-17-2008 8:04 AM

0 Kudos

Hi Ravi,

Let us go in a simple manner for understanding INDEX in a bette way.

What an INDEX can do in a text book. It is used for quick accessing of pages. Similarly in Databases if you could have been given INDEX concept that would fetch the records in a fast manner.

INDEX's are generally 2 types

1) PRIMARY INDEX

2) SECONDARY INDEX

primary index can be generated at the time of table creation, based on the primary fields that you have selected for that table.

whereas secondary index can be created by the developer, on the required fields. but the problem with secondary index is, whenever you are inserting a record into that perticular table, will take more amount of time and also leads to complex. i.e., more number of secondary index's will cause problems at the time of insertion of data.

How to use the index?

you can use the index's by giving the complete index in the WHERE clause of any SELECT statement.

thanks & regards

Kishore Kumar Maram