topic Steps for creating a database index in Application Development and Automation Discussions

Steps for creating a database index

Former Member — Mon, 26 Jun 2006 18:19:55 GMT

Do we just create it from SE11? Does Basis needs to be involved for any furthur steps?

Re: Steps for creating a database index

RichHeilman — Mon, 26 Jun 2006 18:23:20 GMT

You can just create it via SE11. No need to have Basis involved, but I would make them aware of it. It really depends on your procedures. Are you supposed to make them aware? Is this for a custom DB, or a standard SAP table. If it is a standard SAP table, I would suggest making them aware, so that they can monitor any spikes in performance during UPDATES.

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

http://help.sap.com/saphelp_470/helpdata/en/cf/21eb47446011d189700000e8322d00/frameset.htm

Regards,

Rich Heilman

Re: Steps for creating a database index

Former Member — Mon, 26 Jun 2006 18:36:37 GMT

hi Amrutha,

<b>Index:</b>

You can create an index (also called a secondary key) to make it quicker to find data records in a table. An index is a database object that can be defined for an individual column or a series of columns in a database table. An index consists of one or more inversion lists.

An index name must be unique when combined with the table name. When defining an index, you determine whether the column values of different rows in the indexed columns have to be unique or not.

Since indexes are derived entirely from data already in the database, you can delete them without data being lost.

Note that, in certain circumstances, the database system may have to update the index corresponding to any data changes, which takes time.

An index can have the following statuses:

Check these out too..

http://help.sap.com/saphelp_erp2005/helpdata/en/1c/252640632cec01e10000000a155106/frameset.htm

http://help.sap.com/saphelp_erp2005/helpdata/en/c7/55833c4f3e092de10000000a114027/frameset.htm

Regards,

Santosh

Re: Steps for creating a database index

Former Member — Mon, 26 Jun 2006 19:18:44 GMT

hi amrutha ,

this is satish raju from hyderabad.ur question is very simple to answer ,many people have said their own way of answering.

Q ]. steps for creating a database index.

first think to keep it mind that we have two type of indexes 1.primary index

2. secondary index or database index.

by defalult when we assign the field as a primary key in database table it creates a primary index for that field or column.

why do we use indexes ?

we use indexes inorder to fetch data fasteror extract data faster.when we create index on a field the system automatically generates a unique id for that column entries.for columns with primary key gets retrieval of data much faster.

so when we wana fetch data with other than primary key field we use secondary index or data base index.

create a table which is having three fields .1st field and 2 nd field are primary key columns.

3rd field is ordinary field.select the field and click on index button at right top.automatically secondary index will be created on that key field.now to check whether the data retrieval is faster use select statement in a program by using a where condition and use the secondary index field to retrieve data in where condition.

finally what i wana say is we use database indexes only for data retrieval to be faster.try this ones and doubts call me if ur in andhrapradesh.

bye

satish raju

9948054163

Re: Steps for creating a database index

Former Member — Mon, 26 Jun 2006 19:21:02 GMT

hi amrutha ,

i wana say u something of that we can create a max of 9 seconary indexes for a table . this is an important note.

ok bye .

Re: Steps for creating a database index

Former Member — Mon, 26 Jun 2006 20:06:09 GMT

Satis,

Your statement about a max of 9 secondary indexes might be a fact on your current system. But that is a decision made and/or forced by your current SAP R/3 and DB set-up.

I just created 10 secondary indexes on a table in our 5.0 env with an Oracle DB underneath it.

Re: Steps for creating a database index

Former Member — Wed, 28 Jun 2006 18:44:59 GMT

sorrry yaar , actually the max no of secondary indexes is max up to 15 .i have seen this yesterday in a book .confirm and make it sure and also try for that .ok give reply for this .where r u working .r u indian or from states friend.bye

Re: Steps for creating a database index

Former Member — Wed, 28 Jun 2006 19:28:08 GMT

Hi Amrutha,

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 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. 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 HAVING clause, 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 specify the fields of secondary indexes using the Abap Dictionary. You can also determine whether the index is unique or not. 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.

Index:

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

Creating Secondary Index

http://help.sap.com/saphelp_nw04/helpdata/en/cf/21eb47446011d189700000e8322d00/content.htm

regards,

keerthi.