Indexes

[Former Member]

What is an index

and what are different type of indexes?

[Former Member]

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

Regards.

[Former Member]

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

Regards,

[Former Member]

Hi

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.

for mor infor

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

[Former Member]

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

Regards.

[Former Member]

Hi Kittu,

to explain you about an index, let us go generalized manner.....

what an INDEX can do in a text book....it is used for QUICK accessing of pages. In the similar way if an index is provided in our database, which will result in quick accessing of data from the data base.

There are basically 2 types of indexes are available.

1) Primary Index

2) Secondary Index

No one can create a primary index, since it is created by SAP at the time of creation of a table, irrespective of custom or pre defined table. based on the primary key fields, the primary index can be generated by the system.

Developer can create a Secondary index.

[Former Member]

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 disjunct as possible.

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.

do reard if useful

[Former Member]

hi,

An index is easy way to sort the records, it 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 :Take any textbook at the begining of it we we have index to fetch the topic easily similar way, the fields or data is fetched in short duration efficently.

Regards.

kavitha.k

[Former Member]

hi,

There are basically 2 types of indexes are available.

1) Primary Index

2) Secondary Index

Primary index, is created at the time of creation of a table, irrespective of custom or pre defined table. based on the primary key fields, the primary index can be generated by the system.

Secondary index. we can create this index according to our requirement.

For example consider the appendix of the text book, similar words repeted in different pages can be searched in easy way the use of secondary index is that only.

regards,

kavitha.

[Former Member]

Index

Indexes speed up access to rows in a table. They can be created for a single column or for a series of columns. When defining indexes, you specify whether the indexed column values in the different rows must be unique or not.

The assigned index name and table name must be unique.

Creating Indexes

Use

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

2. A table has a primary index and a secondary index.

3. The primary index consists of the key fields of the table and is automatically created in the database along with the table

4. We 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.

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

6.We can 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)

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

Prerequisites

You have opened a table for edit.

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

Key is nothing but Primary key (primary index) /secondary key(secondary index) .

Primary key in the table remains unique thruoghout the data base table.

wenevr u check primary key while creating a dbtable , the system internaly will create aprimary index for that primary key. i.e Pkey and Pindex are one and same.

Secondary key is craeted to improve teh performance of select statements.

suppose if there is a situation where we r writing select statement in which in where condition we are considering non primary key, then secondarindex(key will be craeted ) to that field in the db table and this will improve performance.

[Former Member]

index is improve the perfomence of the table.

There are basically 2 types of indexes are available.

1) Primary Index

2) Secondary Index

Primary index, is created at the time of creation of a table, irrespective of custom or pre defined table. based on the primary key fields, the primary index can be generated by the system.

Secondary index. we can create this index according to our requirement.

For example consider the appendix of the text book, similar words repeted in different pages can be searched in easy way the use of secondary index is that only.