topic Index in data base table in Application Development and Automation Discussions

Index in data base table

Former Member — Sat, 02 Feb 2008 08:37:39 GMT

Hi all.

I need to create a Ztable ,it has 2 key fields and 2 index fields.

what is the use of index fields ? how is it diff from key fields?

how can i create index for my Ztable?

To be reward all helpfull answer.

JNJ

Re: Index in data base table

Former Member — Sat, 02 Feb 2008 09:19:34 GMT

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,

Re: Index in data base table

Former Member — Sat, 02 Feb 2008 09:22:08 GMT

Creating the index for Ztable:

Goto SE11-> enter u r data base table name

At application tool bar it have pushbutton ‘INDEXES’ click it.

Then a popup will come

Enter indexname of 3 characters and descrioption.

Then press ‘CREATE’ button then other popup will comes with dbtable name and index ID

Enter id: <XX>.

Then other screen will comes then enter the fields on which u need to create secondary index.

REward if useful.

Re: Index in data base table

Former Member — Mon, 04 Feb 2008 06:50:55 GMT

if we have huge amount of data and for select statement if we don't have key fields to pass to where condition then we go for secondary index for where condition.ie if primary index is not suite for our condition then we go for secondary index.

in the tool bar idex button is available by by clicking on then we will get subscreen.

in that subscreen create is there then give index name.

a new window will open then give fields,desc of index etc

save and activate.

Re: Index in data base table

Former Member — Mon, 04 Feb 2008 08:24:02 GMT

Hi JNJ ,

First think why you required index.

Index : Create in a z table or SAP table when there is much data in the table and its taking so much time to retrive data from table on the basic of primery fields.

To resolve such type of problem SAP recommand to create Index.

Although you can add primery key in your z table also if possible.

About the definatation and how to create index previous mail are quite enough for you.

Regards

Swati...........

Re: Index in data base table

Former Member — Mon, 04 Feb 2008 09:10:06 GMT

Hi,

Proceed as follows to create a secondary index on a table:

In the field maintenance screen for the table, choose Goto --> Indexes.

1. If you went to the field maintenance screen of the table in display mode, only correct the index (and not the table).

If indexes already exist on the table, a list of these indexes is displayed. Choose Create. A dialog box appears in which you must enter the three-place index identifier. If there are no indexes, go directly to the dialog box.

2. Enter the index identifier and choose Continue.

You will go to the maintenance screen for indexes.

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

4. Choose TabFields.

A list of all the fields of the table is displayed.

5.Select the fields which you want to copy to the index.

6.Choose Copy.

The selected fields are copied to the index.

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

A unique index is automatically created on the database during activation because a unique index also has a functional meaning (prevents double entries of the index fields).

8. If it is not a unique index, leave Non-unique index selected. In this case you can use the corresponding radio buttons to define whether the index should be created automatically on the database for all database systems, for selected database systems or for no database system.

9. If you chose For selected database systems, you must specify these systems.

You have two possibilities here:

List of inclusions: The index is only created automatically during activation for the database systems specified in the list. The index is not created on the database for the other database systems.

List of exclusions: The index is not created automatically on the database during activation for the specified database systems. The index is automatically created on the database for the other database systems.

Click on the arrow symbol behind the radio buttons. A dialog box appears in which you can define up to 4 database systems. Use the corresponding radio buttons to decide whether this list should be treated as a list of inclusions or exclusions.

Activate the index with Index ® Activate. The activation log tells you about the flow of the activation. Call it with Utilities ® Act.log. If an error occurred when activating the secondary index, you will automatically go to this log.

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.

If possible, check whether the database uses the index you created for selection. For more information see Checking whether an Index is Used.

Best Regards,

Rajesh.

Please reward points if found helpful.

Re: Index in data base table

Former Member — Mon, 04 Feb 2008 20:41:24 GMT

Structure of an IndexAn 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:

 An index can only be used up to the last specified field in the selection!

The fields that 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 should not have

too many indexes.

 Make sure that the indexes on a table are as disjunct as possible.

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.

Indexes of a table have a three-place 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, for example, 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.