secondary index

[Former Member]

hi every one , I created secondary indexes for a table with help of indexes in se11 but I don't know how to use these indexes in my select query . give me some clues to solve my problem.

[former_member194669]

Hi,

For example if you want to use MARC index PCT ( MATNR WERKS MANDT PRCTR)

then your select statement will be like this way.

select * from marc 
     into table i_marc 
     where matnr eq 'ABCD'
         and werks eq 'N501'
         and mandt eq sy-mandt
         and prctr eq 'ABC'.

In the select statement the where condition field sequence will be same as in secondary index.

aRs

[Former Member]

hi,

An index is defined on fields FIELD1, FIELD2, FIELD3 and FIELD4 of table BSPTAB in this order. This table is accessed with the SELECT statement:

SELECT * FROM BSPTAB WHERE FIELD1 = X1 AND FIELD2 = X2 AND FIELD4= X4.

Since FIELD3 is not specified more exactly, only the index sorting up to FIELD2 is of any use. If the database system accesses the data using this index, it will quickly find all the records for which FIELD1 = X1 and FIELD2 = X2. You then have to select all the records for which FIELD4 = X4 from this set.

The order of the fields in the index is very important for the accessing speed. The first fields should be those which have constant values for a large number of selections. During selection, an index is only of use up to the first unspecified field.

regards,

Ashok Reddy

[Former Member]

Once you have defined the secondary index, the database "engine" (Oracle, DB2, SQL Server or whatever) should automatically use this new index if it determines that is the best access path based on the contents of your select statement. In your ABAP code, you can "encourage" the database to use your index by specifying as many fields of the index in the correct order, e.g. for BKPF index 2 (posting date), you might code:

      select bukrs belnr gjahr budat usnam
        into corresponding fields of table lt_bkpf          
        from bkpf                                             
        where bukrs = p_bukrs                          
        and   bstat = lc_bstat         "not parked, model etc 
        and   budat in s_budat.                               

but even this may not always be enough, but an SQL trace on your program will show you which path the SQL optimiser has headed down. You can also include "hints" in your code (generally not a good habit) or ask your DBA / Basis to ensure the table has been re-analysed, cache refreshed etc if you are not getting the results you expect.

[Former Member]

Thank you very much for all your answers.

Regards,

soma shanthi