RE: Encoded Vector Indexes -- RPG400-L

Russell,

I've tinkered a little bit with them.  As you probably already know, EVI's
are helpful only for record selection, not record ordering, so specifying
the EVI on a SELECT is pointless (I haven't tried it, it may not even be
allowed).  They're also only really useful if they're built over fields with
a finite number of possible values, for example something like an inventory
classification code.  If the number of values isn't AT LEAST an order of
magnitude smaller than the number of records in the based-on table, an EVI
over the field will not be helpful.  They work best if you only specify ONE
key field in each EVI - if you have several fields you want to use in your
WHERE clause, create a separate EVI for each one.  The query engine will
figure out which one(s) to use when it builds the access plan.

This is from memory and may be wrong, but I think it does tell you when it
uses one, just like it does for a regular index.  If it doesn't use one, it
will also provide the code for why not, also just like a regular index.

In my case, I couldn't justify the EVI's I tested, mainly because the
third-party application that we run doesn't use SQL and rarely uses OPNQRYF.
EVI's are useless for native RPG I/O, as I'm sure you're aware.  The
selections our odd mix of queries and reports make most often are for date
ranges, which EVI's don't work well for (too many possible values).

I hope that helps a little.  If you have any specific questions, holler - I
might have an idea about how to find the answer, at least.

Dave Shaw
Spartan International, Inc.
Spartanburg, SC
---
If you would like to subscribe to the MAPICS-L mailing list send email to
MAPICS-L-SUB@midrange.com or go to www.midrange.com and follow the
instructions.

-----Original Message-----
From: Russell Conerly [mailto:rconerly@netdoor.com]

I have been doing some studying on Encoded Vector Indexes with SQL.  I've
been
looking at improving performance of an SQLRPGLE program running on V4R4 
that is using some rather
large files.  The documentation I have found has been limited IMO.

Have any of you experimented with/used EVI's?  And if so, what are the 
pros/cons to using
them?  Does the optimizer inform you that it is using the EVI?  Do you use 
the index
in your select statement or is the index implied?  Please advise.

Thank you in advance.

Regards,
Russell Conerly
Artaban Solutions, Inc.
+---
| This is the RPG/400 Mailing List!
| To submit a new message, send your mail to RPG400-L@midrange.com.
| To subscribe to this list send email to RPG400-L-SUB@midrange.com.
| To unsubscribe from this list send email to RPG400-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---