Re: SQL on V4: Index Optimization (Hobbyist question)

Hi Patrik,

You are correct that a search string cannot start with a wildcard when
using an index. The answer to this is the same for both CQE and SQE
because the underlying index structures are the same. Db2 for i uses a
radix index, for which the root of each key is the first character/byte of
the data. As a result, searching the keys in the index requires at least
one charater/byte of the search string in order to begin the process of
searching the index. I suppose the query engine could theoretically scan
*all* of the keys in the index looking for a match, but this is unlikely
to be much better (and might be much worse) than doing a table scan.

Thank you,

Tim Clark
DB2 for IBM i / SQL Optimizer

"MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> wrote on 03/24/2021
06:35:33 AM:

From: Patrik Schindler <poc@xxxxxxxxxx>
To: Midrange Systems Technical Discussion

<midrange-l@xxxxxxxxxxxxxxxxxx>,

Date: 03/24/2021 06:36 AM
Subject: [EXTERNAL] SQL on V4: Index Optimization (Hobbyist question)
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxxxxxxxx>

Hello,

maybe some folks can remember 20 years ago when V4 was still common…
So certainly NOT SQE.

I have a PF created with DDS. The important field is a 20 character
field. This field is also defined as key field (with a K line in the

DDS).

The database contains 484,507 records with all uppercase english
words. Goal is to have decent performance to retrieve a list of
words according to LIKE pattern matches.

Example: 'METAL__R_I_GS%' => METALWORKINGS (only one match).
Another: 'FL_W____TING%' => FLOWCHARTING, FLOWCHARTINGS.
But also: '______C_U%' => Many matches.
But never: '%SOMESTRING'

Tests within STRSQL showed that the index in the physical file
itself wasn't suitable, so a temporary index is built. Obviously
this is very slow.

After creating an additional index in SQL like this:

CREATE UNIQUE INDEX ucwrdlst ON ucwrdlstpf (ucword)

the first examples match in a blink of the eye on my slow 150, but
for '______C_U%', I see records being counted in the message line
for dozens of seconds.

I tinkered a bit with visual explain in the old Operations
Navigator. Reason stated was "OPTIMIZER CHOSE TABLE SCAN OVER
AVAILABLE INDEXES".

Based on that, I found this page:

INVALID URI REMOVED

u=https-3A__use-2Dthe-2Dindex-2Dluke.com_sql_where-2Dclause_searching-2Dfor-2Dranges_like-2Dperformance-2Dtuning&d=DwIGaQ&c=jf_iaSHvJObTbx-

siA1ZOg&r=oCmVbiwufH6yM8U29GriOcB5LKDoEG8y1HZD-01RVtc&m=y4yuKxGeGVPzgc9Ry6R_LX9V6GCcJoyrWnBIZ60Qfbk&s=W19D-

nO4S9ue5rV_Z59qsyxB8tDX4EgUdAYu3GLaF9c&e=

and the summary is: Wildcards in front of a search string force a
table scan (in DB2). Apparently, I can't work around that. Correct?
Do I maybe overlook something?

Side question: Is it possible to create an index "within" the PF
satisfying the at least the base LIKE needs? If yes, how?

Reminder, I'm using V4R5 on my 150 for hobbyist purposes.

Thanks a lot!

:wq! PoC