openedgeprogress-4gl

Multiple indexes found in XREF for one table - PROGRESS 4GL


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

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

QUERY

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

916 SEARCH edb.tdf opvdor

916 SEARCH edb.tdf opsellord

Solution

  • Using two indexes should be a benefit, not a problem. I don’t understand why you would want to force the query to be less efficient. The WHERE clause that you are showing has equality matches on all of the fields in both indexes which ought to be ideal.

    This old thread on Progress Communities gets into how multiple indexes work: https://community-archive.progress.com/forums/00019/61573.html

    If you are unsure, run some test cases and track the response time. Or, if you want to get really fancy, use the -zqil startup parameter to report on runtime behaviour. (More info on -zqil: https://community.progress.com/s/article/21216)