Creating a secondary index

There are two types of indexes: Primary index and secondary index. Primary index is automatically created using the primary keys defined. 

Secondary index could be created as per the user requirement. This article discusses about creating a secondary index. 

Go to transaction SE11. 

 

For our demo purpose, we have considered the table ZAUTHOR. 

To know if there are any secondary indexes available, click on Goto à Indexes

 

Following popup appears:

 

From the above screenshot, it is evident that there are no secondary indexes already created. 

Click on Create à Create Index 

 

Enter the name of the index. 

 

Fill in the details – Short description and the fields in the index. 

 

Save and activate. 

Now you can observe the index created above in the list now:

  

Maximum number of secondary indexes we can have are 9.

How to make SELECT statement to make use of any particular secondary index? 

Consider the following example:
SELECT * FROM SPFLI
  %_HINTS ORACLE 'INDEX("SPFLI" "SPFLI~001")'
.......
ENDSELECT.

In the above example, 001 is the secondary index of the table SPFLI. It's a well-known fact that the efficient way of retrieving data from the database tables is by using secondary indexes. Many database vendors provide the optimizer hints for the same. From SAP® v4.5, optimizer hints can be provided by the %_HINTS parameter. This is dependent on the database systems that support optimizer hints. The point to be noted here is these optimizer hints are not standardized by the SQL standards. Each database vendor is free to provide the optimizer hints.
Now to know which index to use for our table:
1. Go to SE11 and there specify the table name
2. Now from the menu, goto --> indexes
3. select the required index.

Now suppose that the identifier 001 represents a non-unique secondary index comprising of the columns CITYFROM and CITYTO. The index name should be defined as:
 <tablename>~<Index Identifier>
like SPFLI~001 in the above example.
The sequence of fields in the WHERE condition is of no relevance in using this optimizers index. If you specify hints incorrectly, ABAP^TM ignores them but doesn't return a syntax error or runtime error.
The code was written in R/3 4.6C.

Code

Consider the following example:REPORT Suresh_test.

TABLES: spfli.

DATA : t_spfli LIKE spfli OCCURS 0 WITH HEADER LINE.

SELECT * FROM spfli
  INTO TABLE t_spfli
  %_HINTS ORACLE 'INDEX("SPFLI" "SPFLI~001")'.

LOOP AT t_spfli.
  WRITE :/ t_spfli.
ENDLOOP.