OPEN SQL - The proper index is not being used

[luis_rod]

Hi all,

I have created an extension on a Z (date) field for VBAP. In an ABAP program I have a SQL sentence that asks for that field in the WHERE clause.

My problem is that, when checking the SQL execution with ST05, it seems that the new index isn’t being used. I even tried the HINTS clause, to no avail.

Any ideas?

Thanks beforehand,

Luis

[former_member703998]

Hi,

Can you please post your code and also elobrate your issue

[luis_rod]

I have the following SQL statement:

SELECT DISTINCT vbak~vbeln
INTO CORRESPONDING FIELDS OF TABLE itab
FROM vbap 
INNER JOIN vbak ON vbak~vbeln = vbap~vbeln
WHERE vbap~zdate IN s_dates

Obviously this is a *very* long running statement, as VBAP has a lot of records, so I created an extension index, VBAP-ZDA, containing MANDT and ZDATE,

If I do a trace of the SQL statements (ST05) I can see that SAP is doing a full table scan, disregarding the index. I added a HINT clause for the ZDA index, with the same result.

[michael_piesche]

Can you show the technical page of the index where it states whether the index is active and active on the connected database?

SE11 Database VBAP -> GoTo Indexes -> Doubleclick on your Extension Index -> Make and Show Screenshot

• Is it active
• Is there a switch and the switch is turned on
• Is it active on the underlying database
• Is it an index for all databases or just specific ones (excluded or included) or no database index

SE11 Database VBAP -> Utilities -> Database Object -> Check

• Is your index listed and does it say it is consistent on the bottom of the page?

SE11 Database VBAP -> Utilities -> Database Object -> Database Utility -> Indexes... -> Double-Click your index -> Is it active, can you "Activate and adjust database"

• When activating DDIC objects in production, always do this at a time where technical maintenance is allowed.

PS: Why do you include MANDT in your index? This would be only necessary, if you actually have several Clients in your production system. If there is just one, MANDT/CLNT are overhead to the index definition, because they always store the same value and therefore dont break down the search. If you have two or more clients that are actively used, than this is indeed a valuable addition to the index.

[luis_rod]

Michael,

Thanks for your post. I'm going to close this thread as I have just discovered that, although the index does not seem to be being used in our DEV system, it IS working ok in our QA system. Why? I'm sure I don't know. Missing SAP note in DEV? DB problems? beats me, I'm going to drop that hot potato to our infrastructure people and concentrate on my program 🙂

Why did I include MANDT in my system? I don't have a good answer for that. All the prior indexes for our VBAP version (including a couple defined by SAP) included the MANDT column, so I went with the flow and included one myself :-). I think I'm going to follow up on your comment and drop MANDT, as we have only a client in our PRD system.

Thanks again,

Luis

[Sandra_Rossi]

What is your database system?

Could you show what hint(s) you tried?

Could you show the execution plan?

For at least Oracle, you need to refresh the statistics so that the database system can evaluate the usefulness of an index depending on the context...

[luis_rod]

Sandra,

Thanks for your post. As I wrote to Michael, since I posted my original question I have found that, although the index does not appear to be being used in our DEV system, ST05 shows it as used in our QA system (!??). I'm going to close this thread and send the problem to our DB and infrastructure people.

Thanks again,