Skip to Content
0
Aug 21, 2012 at 01:49 PM

Question on index of a database table.

149 Views

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.