Re: SQL on V4: Index Optimization (Hobbyist question) -- MIDRANGE-L

Looks like EVI index's were available @ v4r5...

You might try creating one and see if that helps. While not ideal, it's
perhaps possible the symbol table would be quicker to scan than the radix
index or the full table; though I guess that depends on what else is in the
table (ir. how big the rows are).

"database of words"

That rings my "Omnifind Text Search Engine" bell... and while Omnifind
wasn't available at v4r5, the prior product,5722-DE1 DB2 Text Extender, may
have been..
https://wiki.midrange.com/index.php/Advance_Text_Searching

HTH,
Charles

On Wed, Mar 24, 2021 at 5:35 AM Patrik Schindler <poc@xxxxxxxxxx> wrote:

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:

https://use-the-index-luke.com/sql/where-clause/searching-for-ranges/like-performance-tuning

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

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com