Secondary index not getting picked

[Former Member]

Hello All,

I am seeing stange behaviour of picking of secondary indexes.

Example:

Index - I1 is having two fields and the same two fields in giving in where clause of the select and this fields are unque and not used in any other secondary index.

Result in trace(ST05) -- Index I1 is not picked and it extraction went for 'Full table scan'.

But in other system for the same inputs index I1 is picked and it can be seen trace(ST05).

Before posting this query, i have gone through many posts related secondary index and not found helpful.

Any inputs will be appreciated.

Thanks.

Adnan.

[Former Member]

Hi Adnan,

Is it a custom secondry index created on table?

Regards,

Sudeesh Soni

[Former Member]

Hi Sudeesh,

Thanks for your quick response.

It's a standard index and to add more on this issue, this is not the only one table one index issue...

I have noticed for other tables with other indexes as well.

Regards

Adnan

[Former Member]

Ask Basis team to update runtime stats.

[Former Member]

Hi,

In the select query have you Called the Secondary Index in the WHERE clause.

Please try with this option it will surely work.

More Information About Index:

Inclusions Index: 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.

Exclusions Index: 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.

Thanks & Regards,

Saravanan Sambandam

[Mohamed_Mukhtar]

Hello Adnan,

Kindly ask your basis team to check the index quality and ask them to update statistics and index.

Please paste your select query and also let us know the order of fields in your secondary index.

Thanks

[former_member129652]

Hi,

  The database determines which index to use and how to use index.  If the underlying database is Oracle and you want to force the database to use index, please use hints.

For example,

%_HINTS ORACLE 'INDEX("SPFLI" "SPFLI~001")'

[former_member212002]

- Is the "Where" condition according to the fields of Secondary index?

- Is the secondary index active at database level ?

- Check for inconstancy of the table between DDIC and Database in SE14.

- Try using "Adjust and Active table" for starters in DB utility.

- Check the secondary index for more information in DB20.

If it is a custom report, you can explicitly provide the index to a select query. For example -

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

Thanks, Abhinab

[jayanthi_jayaraman]

Hi,

Use the addition %_HINTS ORACLE to force the index in select statement as suggested to overcome the problem.