Consultor Eletrônico

Using CONTAINS effectively on V7 word indexed field with OR

While BEGINS "" returns all the records, CONTAINS "" returns no
records. Getting all the records with a word index is not the best way
to get all the records. That is why we do not support it.

If you need all the records, do not use a word index. In a word index,
there is an entry for each word, as opposed to a regular index, which
will represent a key only once.

It is also important to note that

The WORD INDEX has a priority over other indexes. A query with
CONTAINS will ALWAYS be resolved by the word index and not by any
other index. All the records that match the CONTAINS are
retrieved and matched against the second criteria.

In an extreme example, if you have a query such as:
FOR EACH articles WHERE language = 5 AND description CONTAINS "A*":
DISPLAY articles.
END.
performance could be very slow,
even with a non-unique index on the "language" field,
and a WORD-INDEX on the "description" field. If there are NO records
with language = 5, PROGRESS, using the description index, would still
return all the records whose description begins with "A", and then
test for the value of the language field.

To increase the effectiveness of any CONTAINS search, provide
as long a string as possible to allow PROGRESS to narrow the record
selection to as few records as possible to support the query:
'WHERE description CONTAINS "machin*"' will be faster than "machi*".

Another issue is that when a wild card character is used (*), PROGRESS
will only use one index, the word index. PROGRESS can only use
multiple indexes where there are ONLY EQUALITIES (single value for
each index). The following query would use 3 indexes:

FOR EACH article WHERE description CONTAINS "machine" AND
language = 1 AND product = 87:

Remember that using operators for ranges, LT, LE, GT, GE, is not an
equality.

MULTIPLE INDEXES VS. MULTIPLE BRACKETS
There are two important features of V7 query resolution, namely
multiple brackets and multiple indexes.

This means that if we have:

FOR EACH customer WHERE cust-num GT 77 OR cust-name BEGINS "A":

(and providing there are indexes on cust-num and another on
cust-name) then PROGRESS will use both indexes, and merge
the result (intelligently so as to prevent any record fulfilling
both criteria and appearing twice) and forward the result
to the client.
The above means that the result will not be in any specific
sort order and this has to be specified with a BY.

As far as multiple brackets on a single index is concerned,
you could have:

FOR EACH customer WHERE cust-num lt 20 OR
(cust-num GT 45 AND cust-num LT 534):

thus establishing two brackets on the one index. In this case,
the sort order should be in the natural order of the brackets
in the index.

Progress Software Technical Support Note # 12353