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
Showing results for 
Search instead for 
Did you mean: 

Creating Index to a master table

0 Kudos

Hello All,

I have a database table with the following fields

CLIENT

MATNR

KORDX

KCATV

VARIANT_NBR

With the initial four fields making the primary key. ( CLIENT, MATNR, KORDX, KCATV).

I would like to put an index to the table with the fields (CLIENT, VARIANT_NBR), because there are many reads in the program for the field VARIANT_NBR. Could you please put across on views on this. What all factors should I consider for creating an index in performance perspective.

Thanks in advance

Sudha

1 ACCEPTED SOLUTION

former_member251078
Participant
0 Kudos

Hello Sudha,

Please check the selectivity of the fields VARIANT_NBR through transaction DB05. If this fields is very selective you can go ahead and create an index on fields MANDT, VARIANT_NBR. Also make sure that the field VARIANT_NBR is mentioned in '=' sign in where clause of the sql statement.

Yours Sincerely

Dileep

Edited by: Dileep Kumar K J on Jun 27, 2008 6:46 PM

4 REPLIES 4

Former Member
0 Kudos

Hi Sudha,

you can create an index for this. but just a thought, u cant make it a primary key

field???

regards,

madhu

former_member212653
Active Contributor
0 Kudos

Its better to see the performance before creating an index. Just keep in mind that creating a secondary index should be your last option for performance tuning.

1)Try to hit the database as few as possible to better the performance.

2)Don't retrieve data using SELECT - ENDSELECT statement.Retrieve the data from one table in one shot using FOR ALL ENTRIES addition

3)Don't use nested nested LOOPs.Use parallel cursor instead.

4)Hit one table only once.

5)Use READ TABLE statement using BINARY SEARCH option before sorting it.

Some key points to remember before secondary index creation:

It is worth creating an index when:

1)You want to select table entries based on fields that are not contained in an index, and the response times are very slow.

The EXPLAIN function in the SQL trace shows which index the system is using. You can generate a list of the database queries involved in an action by entering Transaction ST05 and choosing Trace on u2192 Execute action u2192 Trace off u2192 List trace. If you execute the EXPLAIN SQL function on a EXEC, REEXEC, OPEN, REOPEN or PREPARE statement, the system returns a list containing the index used in the database query.

2)The field or fields of the new secondary index are so selective that each index entry corresponds to at most 5% of the total number of table entries. Otherwise, it is not worth creating the index.

3)The database table is accessed mainly for reading entries.

Using an index consisting of several fields

Even if an index consists of several fields, you can still use it when only a few of the fields actually appear in the WHERE clause. The sequence in which the fields are specified in the index is important. You can only use a field in the index if all of the preceding fields in the index definition are included in the WHERE condition.

An index can only support search criteria which describe the search value positively, such as EQ or LIKE. The response time of conditions including NEQ is not improved by an index.

former_member251078
Participant
0 Kudos

Hello Sudha,

Please check the selectivity of the fields VARIANT_NBR through transaction DB05. If this fields is very selective you can go ahead and create an index on fields MANDT, VARIANT_NBR. Also make sure that the field VARIANT_NBR is mentioned in '=' sign in where clause of the sql statement.

Yours Sincerely

Dileep

Edited by: Dileep Kumar K J on Jun 27, 2008 6:46 PM

Former Member
0 Kudos

Hi,

Regarding indexes information check this link...

http://help.sap.com/saphelp_nw04/helpdata/en/cc/7c58b369022e46b629bdd93d705c8c/content.htm

and

http://www.ncsu.edu/it/mirror/mysql/doc/maxdb/en/6a/c943401a306f13e10000000a1550b0/content.htm

And also go through the below information...

They may help you in optimizing your program.

The Optimizer

Each database system uses an optimizer whose task is to create the execution plan for SQL statements (for example, to determine whether to use an index or table scan). There are two kinds of optimizers:

1) Rule based

Rule based optimizers analyze the structure of an SQL statement (mainly the SELECT and WHERE clauses without their values) and the table index or indexes. They then use an algorithm to work out which method to use to execute the statement.

2) Cost based

Cost based optimizers use the above procedure, but also analyze some of the values in the WHERE clause and the table statistics. The statistics contain low and high values of the fields, or a histogram containing the distribution of data in the table. Since the cost based optimizer uses more information about the table, it usually leads to faster database access. Its disadvantage is that the statistics have to be periodically updated.

---

Minimize the Search Overhead

You minimize the size of the result set by using the WHERE and HAVING clauses. To increase the efficiency of these clauses, you should formulate them to fit with the database table indexes.

---

Database Indexes

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.

---

Formulating Conditions for Indexes

You should bear in mind the following when formulating conditions for the WHERE and HAVING clauses so that the system can use a database index and does not have to use a full table scan. Check for Equality and Link Using AND The database index search is particularly efficient if you check all index fields for equality (= or EQ) and link the expressions using AND.

---

Use Positive conditions

The database system only supports queries that describe the result in positive terms, for example, EQ or LIKE. It does not support negative expressions like NE or NOT LIKE. If possible, avoid using the NOT operator in the WHERE clause, because it is not supported by database indexes; invert the logical expression instead.

---

Using OR

The optimizer usually stops working when an OR expression occurs in the condition. This means that the columns checked using OR are not included in the index search. An exception to this are OR expressions at the outside of conditions. You should try to reformulate conditions that apply OR expressions to columns relevant to the index, for example, into an IN condition.

---

Using Part of the Index

If you construct an index from several columns, the system can still use it even if you only specify a few of the columns in a condition. However, in this case, the sequence of the columns in the index is important. A column can only be used in the index search if all of the columns before it in the index definition have also been specified in the condition.

---

Checking for Null Values

The IS NULL condition can cause problems with indexes. Some database systems do not store null values in the index structure. Consequently, this field cannot be used in the index.

---

Avoid Complex Conditions

Avoid complex conditions, since the statements have to be broken down into their individual components by the database system.

Hope this information had helped you.

Regards

Narin Nandivada.