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

If you don't want to have rows with blanks in a specific position, just add
WHERE SUBSTRING(NAME,1,1) > ' '
to the CREATE INDEX statement.

One more thing: I don't know if an EVI would provide better performance (if
that feature is available on your version).

On Sat, Mar 27, 2021 at 5:07 AM Patrik Schindler <poc@xxxxxxxxxx> wrote:

Hello,

Am 24.03.2021 um 22:40 schrieb x y <xy6581@xxxxxxxxx>:

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.

The optimizer is smarter than I thought. He seems to use all these indexes
as needed so

SELECT ucword FROM ucwrdlstpf WHERE ucword LIKE 'Q__AS%'

gives a result in around three seconds. :-O This is *a lot* less than
before, with some dozen seconds before. (Three seconds might seem much, but
remember, we're talking about a 150 here.)

Problem solved. Thanks a lot for great your help and valuable input!

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.

I did that with DDS (SST statement) according to a private message. I
didn't know that partial indexes were possible at all.

You could add a WHERE on CREATE INDEX to drop rows with blanks.

May I ask you to elaborate?

: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