We have just run across a strange issue with regards to multiple index selection, specifically when used within a FOR EACH query, and a join.
For example, given the below query (sports2000):
for each Customer
where Customer.CustNum ge 10,
each Invoice
where Invoice.CustNum eq Customer.CustNum
and Invoice.InvoiceDate eq today
no-lock
by Invoice.InvoiceNum
It correctly uses the CustNum and InvoiceDate indices from the Invoice table.
c:\temp\sports2000.p 1 SEARCH sports2000.Customer CustNum
c:\temp\sports2000.p 1 SEARCH sports2000.Invoice CustNum
c:\temp\sports2000.p 1 SEARCH sports2000.Invoice InvoiceDate
c:\temp\sports2000.p 1 SORT-ACCESS sports2000.Invoice Invoicenum
If I change the 'each Invoice' to a 'first Invoice', both indexes are still correct selected. However, if I change the each Invoice join to last Invoice, the InvoiceDate index is no longer selected for use:
c:\temp\sports2000.p 1 SEARCH sports2000.Customer CustNum
c:\temp\sports2000.p 1 SEARCH sports2000.Invoice CustNum
c:\temp\sports2000.p 1 SORT-ACCESS sports2000.Invoice Invoicenum
It seems that, for some unusual reason, multiple indexes via FOR LAST are not used, and that a FOR LAST gets implemented like a FIND LAST, but a FOR FIRST is implemented as a FOR EACH. I can't see any reference to this behaviour in the index selection documentation.
Is this expected behaviour?