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

something like this:

where (char1 = case when :char1var > '%' then char1 else char1 end
--% means always include
or char1 = case when :char1var > ' ' then :charvar1 else char1 end)
--non-blank selects same character
and ...

...for all 19 single-character fields.

Index:
CREATE INDEX QTEMP/MASTER01 ON CUSTMAST (substring(NAME,1,1))
CREATE INDEX QTEMP/MASTER02 ON CUSTMAST (substring(NAME,2,1))

...and so on, 18 more times. You could add a WHERE on CREATE INDEX to drop
rows with blanks.


Wild cards might be problematic
On Wed, Mar 24, 2021 at 11:08 AM Patrik Schindler <poc@xxxxxxxxxx> wrote:

Hello,

Am 24.03.2021 um 18:53 schrieb x y <xy6581@xxxxxxxxx>:

Another option: make 20 indexes over each character of the 20-byte field
and use CASE in your WHERE. While not a best practice, I'll bet this
approach would scream.

Thank you. This also has been suggested to me in a private message. I ask
the same question here: How to create indexes as you suggest? I haven't
found something helpful in the older "DB2 Universal Database for iSeries
SQL Reference Version 5" PDF.

And, may I ask for a (simple) example for the CASE in a WHERE for this
kind of index?

: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