Monday, July 11, 2016

Why you need Secondary Index for a table?


Index is nothing but  a copy of the database table containing only limited fields. Primary index consist of the key fields with index to non-key fields. When a table is created, the primary index is automatically created. The intention of the index is to help in faster search as well as unique identification of the record. Additional indexes can be used to speed up the purpose of the record selection. This is particularly handy in case of large number of records in a table. ‘0’ is reserved for the primary index and any other number could be used for the secondary index. Secondary index is creaed for non-key fields. Care must be taken to ensure the fields put across in secondary index are extensively used.

Creating a secondary index.

  1. Goto SE11 and provide the table name. 
  2. Click on the indexes button. 

 3.Provide the three letter ID for the secondary index.The ID needs to start with Z or Y unless it is something SAP requests

4.Provide the short description and the fields based on which secondary index needs to be created.

What is the use of unique and non-unique index?
Simple.Unique index will not allow multiple entries. The entries needs to be unique with respect to the fields mentioned. Primary index is one example fo unique index. Unique index does not mean that the search for the records would be fast, it just means the unique set of records needs to be maintained.

Few more points on secondary index:
  • SAP normally chooses the index while executing the SELECT statement.However,we can force it to choose our index by using the following statement

 %_HINTS ORACLE 'INDEX("ZNAM" )

  • We can create upto 9 secondary indexes for a table.
  • Primary index would be more suited for SORT  statements than secondary indexes.


No comments:

Post a Comment