Re: Embedded RPG and the WHERE LIKE

I'd run some tests on this and checked Visual Explain:

- With no index in place, Visual Explain showed a Table Scan in use.
- With an index in place, and no function like UPPER involved, an Index
Probe was used in every case, regardless if the LIKE search was in the
beginning, the middle, or the end of the column.
- With an index in place, and function UPPER in use, and Index Scan was
used.

Probing or scanning an index instead of a table scan makes sense to me,
simply because the index is smaller in size than the data portion of a
table.

...as tested on V7R1 without messing with the sort sequence.

Whenever the LIKE search is in the middle or end, or if a function like
UPPER is used, I wouldn't expect those situations to be stellar performers,
unless the table has a small number of rows.

Mike

message: 6
date: Mon, 16 Nov 2015 15:15:37 -0600
from: Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx>
subject: Re: Embedded RPG and the WHERE LIKE

I think the index has to have the same sort sequence in order for it to
be used.

And so far as I know, for the LIKE predicate, only those with trailing
wildcard can take advantage of the index. If a leading wildcard, it is
almost certainly going to use a table scan.

On 11/16/2015 12:56 PM, Mike Jones wrote:

Hi Booth,

Between Birgitta and Peter, they've got your answers covered.

To get reasonable performance, you'll definitely want an index present

over

columns searched over using LIKE, to ensure an Index Probe takes place
instead of a full Table Scan.

Mike