Question on index of a database table.

[former_member186584]

Hello Experts,

I was just experimenting on some performance related questions that were popping up in my mind. The following statement was highly performance intensive:

IF GT_E071[ ] IS NOT INITIAL.

     SELECT PGMID OBJECT OBJ_NAME DEVCLASS

                   FROM TADIR

                   INTO TABLE GT_TADIR

                   FOR ALL ENTRIES IN GT_E071

                   WHERE OBJECT = GT_E071-OBJECT_R3TR

                   AND    OBJ_NAME = GT_E071-OBJ_NAME_R3TR+0(40).

ENDIF.

The reason the above statement was highly performance intensive was - the table TADIR did not have an index table with the fields - OBJECT, OBJ_NAME. Therefore, the condition imposed in the WHERE clause does not have index support. So, I created a new index table for TADIR with the fields - OBJECT and OBJ_NAME.

But even after I created this index, when I investigate the SQL Trace of ST05, I find that the index table that I created (with fields - OBJECT and OBJ_NAME) was NOT used. The DB Optimizer used some other index table. Why didn't the DB Optimizer use the index table that matches EXACTLY with the WHERE clause fields? Is there a way to make the DB Optimizer choose the index table that I specifically created to support the above SQL statement?

Note: Of course, TADIR has the primary index with fields - PGMID, OBJECT and OBJ_NAME. So one way to make the above SQL statement efficient, would be to change the WHERE clause of the SELECT query to have PGMID also. But I don't have any specific values to give for PGMID in the WHERE cluase. So is there I way I can make use of the primary index (with fields - PGMID, OBJECT and OBJ_NAME) when I don't have any condition to specify for PGMID in the WHERE clause of my SELECT statement?

Thanks for your time.

[Former Member]

A few thoughts about your question:

What is your database, and what version?
Things like 'DB Optimizer' are largely dependent on what your DB actually is ...

If a new index isn't used, it might help to recreate statistics of all indexes of that table.

If you want to enforce index usage, you may us a hint in your ABAP code.

hope this helps

[former_member186584]

Hi Joe,

Thanks for the reply. My database is: MSSQL Release: 9.00.4035.

I will search for the suitable hint.

May I know you thoughts on how to "reframe" the WHERE clause of my SELECT so that the existing primary index (with fields PGMID, OBJECT and OBJ_NAME) can be used. I don't have any conditions to specify for the PGMID in the WHERE clause. In other words, can I "adapt" the SQL statement to the existing primary index?

Thanks for your time.

[Former Member]

As I wrote, your DB Optimizer depends on your DB. Unfortunately I don't know much about MSSQL optimizer. I don't know how to change its behaviour.
It might be a good approach to try to use the existing index though. There are only a few different entries for PGMID after all.
On the other hand, an additional index shouldn't need that much resources, and the one you tried looks good.
See which one, the existing or the new, will be easier to use. I can't help more, I'm afraid.

regards

[amy_king]

Hi Muthuswamy,

If recreating statistics as Joe suggests doesn't work, you can force a particular index by using a query optimizer hint. Find out the name of your index by looking at the table > indexes > DB index name for the index you're interested in. Then you can use the index name in your query. The syntax may differ in MSSQL so look for documentation specific to your database, but as an example an Oracle hint would look something like...

    SELECT ...
           FROM table
           INTO ...
           WHERE ...
           %_hints oracle 'index("TABLE" "INDEX_NAME")'.

Cheers,

Amy

[Former Member]

Since you are experimenting I just wanted add this - in case you did not already know this. Active Indexes on SAP level do not always mean they exist on the database. A DBA can help you more by checking/creating one for you.

Regards,

Shravan

[former_member186584]

Hi Shravan,

I observed that what you said is correct. In my case, even though I have an active index in DDIC level, that index doesn't seem to be created in database level. I went to the SQL statement Summary view of ST05's SQL trace and then used the "Explain" function. Then, I saw that the index I created doesn't show up in the list of available indexes.

Can you please explain how to create this index in database level also?

Thanks for your time and effort.

[Former Member]

Hi Muthuswamy,

Check with your local Basis/DBA. The process depends on your actual DB. Also, in a productive landscape you will not have access to such things usually.

Regards,

Shravan

[ThomasZloch]

GT_E071 sounds as if it was filled from E071, and E071 contains PGMID, so maybe you can enhance your program to read PGMID as well.

Even if not, you are reading from TADIR, and TADIR has 99,9% entries with PGMID = "R3TR", so you can try using this as fixed value.

Be aware that E071 can contain partial objects (PGMID = LIMU, OBJECT = REPS, for exampele), and TADIR contains only main objects (OBJECT = PROG in this example), so there will be mismatches, if you don't translate between the two.

Creating new indexes should be the last resort only, always try to enhance your program logic first.

As for why your new index is not being used, a few tips were given already.

You could run an ST05 SQL trace and post the explain here for further analysis.

Thomas

[raymond_giuseppi]

As already written use ST05 SQL trace to get an idea while the optimize does not use your index.

You can force the optimizer to use your index via %HINTS (Read Note 133381 - Database-Hints in Open SQL for MS SQL Server and generic Note 129385 - Database hints in Open SQL.)

In your E071 extracted internal table, you could also perform a LOOP to identify the actual TADIR entry (e.g. for LIMU REPS or other partial objects) via FM TR_CHECK_TYPE (E071 -> TADIR)

Regards,

Raymond