RE: Encoded Vector Indexes -- RPG400-L

David,
Thanks for your time and your thoughts regarding the EVI's.  I will continue my 
experimenting.  According to some documentation I found on IBM's site...as of 
V4R4M0,
EVI's can also improve joins.

We will see.  Thanks again.

Russell Conerly
Artaban Solutions, Inc.


>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
>+---


+---
| 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
+---