Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

Problem on secondary Index

Former Member
0 Kudos

i am fetching data from NAST table.

SELECT MAX( DISTINCT ERDAT ) FROM NAST
                             INTO VAR_ERDAT
                             WHERE OBJKY EQ '4500016332'.

When i chkd in SM50 Process analysis,It is reading NAST table Sequentially,Which is taking long time.

When i checked in SQL tracer,It is not using the secondary index that it shud use for NAST table,What i know is...ABAP optimizer itself decides on Secondary Index which is to be used,What we can do is use the Secondary Index fields in Sequence as defined in Secondary INdex.

ACCORDING TO MY UNDERSTANDING :

SECONDARY INDEX THAT SHOULD BE USED according to select Statement that i wrote above FOR NAST TABLE:

<b>INDEX ID : ADQ

SHORT DESC : ADABAS-Specific Index</b>

Please correct me,if i am wrong somewhere.Also tell me if there is any way of explicitly forcing it to use the proper SECONDARY INDEX.

THANKS IN ADVANCE,

VIJAY RAHEJA

6 REPLIES 6

FredericGirod
Active Contributor
0 Kudos

Hi,

From version 4.5 and up, you can do it by using the keyword %_HINTS after the WHERE clause. A more detailed explanation can be found in http://www.intelligenterp.com/columns/archive/abap4.shtml and in OSS 129385.

source : http://sap.ittoolbox.com/documents/popular-q-and-a/specifying-the-index-to-be-used-2462

For the index you are looking for ... the problem is that, the client number is not specified. So SAP don't want to use this specific index.

Try to modify the index and add the mandt (no, it's a joke ).

Rgd

Frédéric

Message was edited by: Frédéric Girod

Former Member
0 Kudos

Hi Vijay,

Your are right that ABAP optimizer itself decides on Secondary Index which is to be used.

If you go to Index ADQ of table NAST, you will see that it is intended to work for selected database systems and not for all database systems.

What I suggest you to check if the database you are using is specified in the selected database systems. If not, create your own Secondary Index.

Hope it helps.

Regards,

Neeraj Gupta

Former Member
0 Kudos

HI

FOR NAST TABLE THERE ARE SECONDARY INDXES EXISTING.

To display the existing secondary indexes on any database table, follow this procedure:

1 Display the table.

2 Press the Indexes. . . button on the Application toolbar. If you do not see this button, your window might be too narrow. Widen it or choose the menu path Goto->Indexes. . ..

3 If secondary indexes exist, a list of them will be displayed in a dialog box. If none exist, a message will be displayed.

4 To display one of them, double-click on it. The index is displayed in the ABAP/4 Dictionary: Table: Display Index screen.

now you can use any one of these secondary index in the where addition.

IF IT FINDS USEFUL PLEASE REWARD POINTS.

REGARDS,

ANOOP

Message was edited by: ANOOP R.S

Former Member
0 Kudos

HI vijay,

1. since in where clause u are using

just OBJKY field,

(and since this is a part of primary key)

secondary index won't come into picture.

regards,

amit m.

Former Member
0 Kudos

Since the index is specific to ADABAS, it won't exist in your system if you aren't using ADABAS. So even using hints won't help. You can either extend the index to you database (which may cause more problems) or try to determine KAPPL and pass that to the select.

Rob

0 Kudos

Hi All,

We also created a secondary index on an ODS which is being looked up another ODS. But we still sequential read in SM50, and I don't think the index is being used even though it is in our 'where' clause. Please let me know what else need to be done, this is a PROD issue.

Thanks,

BW_US