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
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).
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.