topic Re: index creation in Application Development and Automation Discussions

index creation

Former Member — Thu, 12 Apr 2007 11:53:44 GMT

can any body tel wats the use of using index in a table

how to create index in a table

how to use in a pgm

Re: index creation

Former Member — Thu, 12 Apr 2007 11:56:25 GMT

Hi,

Indexes are used to fetch the data from the table fastly.

We will create the indexes on few fields which will serve as a fast search criteria to fetch the data

go to SE 11

enter table name

change

from menu GOTO -> INDEXES

Create Index - enter name

enter fields

save

activate

while fetching data from the table in the where condition use these fields on which the index has been created

reward if useful

regards,

ANJI

Message was edited by:

Anji Reddy Vangala

Re: index creation

Former Member — Thu, 12 Apr 2007 11:58:08 GMT

Index is used for the faster fetching of the data.

Primary index is already in the table which combines

to give u an unique key.

Secondary index is the index which u create by clicking

on the Indexes button on application toolbar in se11 txn.

Hope this helps.

Regds

Seema

Re: index creation

Former Member — Thu, 12 Apr 2007 11:59:29 GMT

See if you are fetching data from some table let us take RESB reservations which has as primary key but you have only matnr and werks with you to hit that RESB table,..

it less the performance but Matnr and werks are used as secondary indexes in RESB so you can hit the table with these two fields,,

Indexes help to speed up selection from the database. They consist of a sorted copy of certain database table fields.

Creating an index

You can create an index in Transaction SE11 by choosing Change → Indexes... → Create. To make the index unique, select UNIQUE. To specify the fields that will comprise the index, choose "Choose fields". You then need to save and activate the index.

When to create an index

It is worth creating an index when:

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 → Execute action → Trace off → 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.

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.

The database table is accessed mainly for reading entries.

you can ue in the program straight away as you do for primary keys..

select ** from RESB where matnr = It_mara-marnt...

hope you understood,

Reward points if usefull

regards

nazeer

Re: index creation

Former Member — Thu, 12 Apr 2007 12:00:02 GMT

Hi Arun,

There are 2 type of indexes available.

Primary index: with out this we can't create the table.

Secondary index: this is for using in the where condition in the select statement.

For this INDEX button is available on the application tool bar, if u press that then all indexess list wil be avaialble.

Hope this helps you, reply for queries, Shall post you the updates.

Regards.

Kumar. .

Re: index creation

Former Member — Thu, 12 Apr 2007 12:00:12 GMT

Hi,

1.In SE11 Transaction display your table.

2.press button index... in tool bar, it will ask you

to create a new one.

3.give the index name

4. give short text description

5. in bottom table give the field name on which

you want index.

6. save and activate.

check this.

http://www.oreilly.com/catalog/sapadm/chapter/ch01.html

To create Secondary Index --> http://help.sap.com/saphelp_nw2004s/helpdata/en/cf/21eb47446011d189700000e8322d00/content.htm

Regards

Sudheer

Re: index creation

Former Member — Thu, 12 Apr 2007 12:04:17 GMT

Creating Secondary Indexes

Procedure

...

1. In the maintenance screen of the table, choose Indexes.

If indexes already exist on the table, a list of these indexes is displayed. Choose .

2. In the next dialog box, enter the index ID and choose

The maintenance screen for indexes appears.

3. Enter an explanatory text in the field Short text.

You can then use the short text to find the index at a later time – for example through the Information System.

4. Select the table fields to be inserted in the index using the input help for the Field name column.

The order of the fields in the index is very important. See What to Keep in Mind for Secondary Indexes

5. If the values in the index fields already uniquely identify each record of the table, select Unique index.

A unique index is always created in the database at activation because it also has a functional meaning (prevents double entries of the index fields).

6. If it is not a unique index, leave Non-unique index selected.

In this case you can use the radio buttons to define whether the index should be created for all database systems, for selected database systems or not at all in the database.

7. Select for selected database systems if the index should only be created for selected database systems.

Click on the arrow behind the radio buttons. A dialog box appears in which you can define up to 4 database systems with the input help. Select Selection list if the index should only be created on the given database systems. Select Exclusion list if the index should not be created on the given database systems. Choose .

8. Choose .

Result

The secondary index is automatically created on the database during activation if the corresponding table has already been created there and index creation was not excluded for the database system.

You can find information about the activation flow in the activation log, which you can call with Utilities ® Activation log. If errors occurred when activating the index, the activation log is automatically displayed.

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

Re: index creation

Former Member — Thu, 12 Apr 2007 12:04:56 GMT

Hi Arun,

1. The standard table is the most appropriate type if you are going to address the individual table entries using the index.

2. Index access is the quickest possible access.

3. You should fill a standard table by appending lines (ABAP APPENDstatement), and read, modify and delete entries by specifying the index (INDEX option with the relevant ABAP command).

4. The access time for a standard table increases in a linear relationship with the number of table entries.

5. If you need key access, standard tables are particularly useful if you can fill and process the table in separate steps.

6. For example, you could fill the table by appending entries, and then sort it. If you use the binary search option (BINARY) with key access, the response time is logarithmically proportional to the number of table entries.

7. Example code:

U can also read an internal table using INDEX.

read table it_tab into wa_tab with index <n>.

where <n> is the integer which specifies the record number in the internal table it_tab.

Regards,

Thasneem

Reward if helpful.

Re: index creation

Former Member — Thu, 12 Apr 2007 12:05:50 GMT

Hi all,

How many secondary indexes can we created?

Thanks

Ravi

Re: index creation

Former Member — Thu, 12 Apr 2007 12:06:57 GMT

Hi Arun,

An Index is created on a table to enable faster search on that table. There are two types of index - Primary and Secondary.

Primary index is automatically created when you create a table. It is created for all primary keys of that table.

Secondary index are created if you want to search a table on fields other than the primary fields. To create a secondary Index goto SE11 -> give tablename -> Indexes -> Specify fields you want to create an index on.

In your program, in the select query on the table, specify the fields on which you have created an Index in the Where clause. Remember to keep the order of fields same as they appear in the Index.

Before creating a secondary index for performance improvement you must try out other ways. Creating a secondary index should be only a last option as it causes an overhead whenever the table is queried (for fields other than those in the Index).

Hope this helps!

Regards,

Saurabh