Using MATCHES in Where Clause

Posted by Jeff Ledbetter on 15-May-2017 13:46

Hi.

The help for the MATCHES statement states:

"MATCHES does not use index information when performing a comparison; it always scans the entire data table."

Does this mean that if a MATCHES operator is present, all bracketing that may occur from other entries in the WHERE clause is ignored and a table-scan is performed?

For example, will this MATCHES cause the bracketing on SalesRep to be ignored?

FOR EACH Customer NO-LOCK
  WHERE Customer.SalesRep = "bob"
    AND Customer.Name MATCHES c:

END.

OpenEdge Development - Forum

All Replies

Posted by Peter Judge on 15-May-2017 13:52

The ‘Salesrep’ field will be used for index-selection purposes.
 
The ‘Name’ field will not.
 

Posted by Garry Hall on 15-May-2017 13:57

The bracketing for the SalesRep index is not ignored. Take a look with XREF:

test1.p test1.p 1 SEARCH sports2000.Customer SalesRep

The search criteria has an equality match on Customer.SalesRep, so the compiler will use that. The MATCHES will never be used to provide a bracket for the compiler from which to choose the "best" index. If the only thing you have in your where clause is the MATCHES, then you are doing a WHOLE-INDEX search of the primary index, and comparing the MATCHES function for each record.

Posted by Jeff Ledbetter on 15-May-2017 13:58

Thanks. That's what we thought.  We tried to read too much into the sentence from the help I think. :)

This thread is closed