INDEX

[Former Member]

Hi

What are the things I shoud consider when creating an Index?

Hw can I select the best index?

Hw it will affect the db access?

How to create an index for a table?

Thx

[former_member156446]

Goto > Indexes. A list of all the table indexes that exist in the ABAP Dictionary is displayed. Select the required index by double-clicking. The following functions are offered in the next screen:

• Create database index: Create a secondary index or the primary index of a transparent table in the database.

• Delete database index: Delete a secondary index of a transparent table in the database. The primary index of a transparent table created in the database cannot be deleted as long as the table still exists in the database.

• Activate and adjust database: The index is deleted from the database. The revised version of the index is activated. The index is then created and built again in the database.

Create index :

http://www.sapdb.org/7.4/htmhelp/cd/adf27ab99711d6b2a300508b6b8a93/content.htm

[Former Member]

Hi

Indexes are being selected basd on the fields used in where conditions, if you all the fields of primary keys then system selects the primary keys and if not then you need to go for secondary insexes.

for secondary indexes you need to be very careful because sometimes you wont get the improvement and will harm the performance

Refer the links

http://help.sap.com/saphelp_nw70/helpdata/en/cf/21eb20446011d189700000e8322d00/content.htm

http://www.sapdb.org/7.4/htmhelp/cd/adf27ab99711d6b2a300508b6b8a93/content.htm

http://web.mit.edu/sapr3/dev/indexes.htm

regards

Shiva

Edited by: Shivakumar Hosaganiger on Feb 1, 2008 9:30 AM

[Former Member]

Hi,

Indexes provide access to the table data using non-key columns. Maintaining these indexes, however, can be quite complex in the case of an INSERT, UPDATE, or DELETE statement.

The index is created across the specified table columns. The secondary key consists of the specified columns of the table, in the specified order.

· The specified table must be an existing base table, and not a temporary table. The index name must not be identical with an existing index name of the table.

· The column defined by the column name must be a column in the specified table. This column must not be a LONG column. All of the column name pairs must be different.

· The current user must have the INDEX privilege type for the columns.

UNIQUE

If UNIQUE is specified, the database system ensures that no two rows of the specified table have the same values in the indexed columns. In this way, if two rows both contain the NULL value for all columns of an index, the two index values are not considered to be identical. If there is not at least one column that does not contain the NULL value, two rows that have the same value in all non-NULL columns are considered to be identical.

ASC | DESC

The index values are stored in ascending or descending order. If the specification of ASC or DESC is omitted, ASC is implicitly assumed.

Regards,

Renjith Michael.

[Former Member]

Hi,

An index is a copy of a database table that is reduced to certain fields. This copy is always in sorted form. Sorting provides faster access to the data records of the table, for example, when using a binary search. A table has a primary index and a secondary index. The primary index consists of the key fields of the table and is automatically created in the database along with the table. You can also create further indexes on a table in the Java 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 primary index. Different indexes for the same table are distinguished from one another by a separate index name. The index name must be unique. Whether or not an index is used to access a particular table, is decided by the database system optimizer. This means that an index might improve performance only with certain database systems. You specify if the index should be used on certain database systems in the index definition. Indexes for a table are created when the table is created (provided that the table is not excluded for the database system in the index definition). If the index fields represent the primary keys of the table, that is, if they already uniquely identify each record of the table, the index is referred to as an unique index.

Procedure :

1. Choose the Indexes tab.

2. To create an index, choose New.

3. Enter a name for the index. Index names, like table names, also have a prefix, followed by an underscore.

If the name of an index was registered on the name server, it cannot be deleted.

4. To select table fields, choose New.

5. if the index is a unique index,

6. If the index is used for all databases, choose and whether it is to be created for all databases. Choose the appropriate checkboxes.

7. Choose File® Save All Metadata.

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.

regards,

vasavi.

kindly reward if helpful.

[Former Member]

Tx