[Stackoverflow] [Progress OpenEdge ABL] Multiple indexes found in XREF for one table - PROGRESS 4GL

David

Guest

Apr 27, 2024

From below query, a table tdfmstr uses two indexes and conditions added (All conditions are mandatory). After the compilation, I checked XREF of the query. I could see compiler chose 2 indexes opvdor and opsellord.

Could you please tell me is the compiler using both indexes or the 2nd index(opsellord) only? What if I say to compiler only use 1st index by mentioning use-index opvdor in my query.. I understand forcing the compiler is not a good choice but in this case I am not sure how this query will work with larger records and not causing any performance issues.

INDEXES

Code:

**opvdor**                     3 + tdfcust     
                                  + tdfnum  
                                  + tdfseq
                                                    
**opsellord**                 3 + tdfcust     
                                  + tdfpart    
                                  + tdfvend

QUERY

Code:

for each tdf_mstr
  where tdfmstr.tdfcust = "SALES"
  and   tdfmstr.tdfnum  = "1"
  and   tdfmstr.tdfseq  = 455
  and   tdfmstr.tdfpart = "TEST"
  and   tdfmstr.tdfglob = ""
  and   tdfmstr.tdfvend = "TOYOTA"
  and   tdfmstr.tdfeed  = "X"
no-lock
use-index opvdor: /*Forcibly given to use 1st Index*/

end.

XREF RESULTS

Code:

916 SEARCH edb.tdf opvdor

916 SEARCH edb.tdf opsellord