D
danielb
Guest
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?
Continue reading...
Continue reading...