RE: EVI (encoded vector index) question

[Michael_Schutte@xxxxxxxxxxxx]

Most of the key columns I know the maximum number of distinct values but
there are a few in which I will not. I'll do some testing and we'll see
where I stand.


Michael Schutte
Admin Professional
Bob Evans Farms, Inc.
"The Secret's the Sauce! Enjoy our new Bob-B-Q Pulled Pork Knife & Fork
Sandwich!"



                                                                           
             "DeLong, Eric"                                                
             <EDeLong@Sallybea                                             
             uty.com>                                                   To 
             Sent by:                  "Midrange Systems Technical         
             midrange-l-bounce         Discussion"                         
             s@xxxxxxxxxxxx            <midrange-l@xxxxxxxxxxxx>           
                                                                        cc 
                                                                           
             01/11/2008 05:25                                      Subject 
             PM                        RE: EVI (encoded vector index)      
                                       question                            
                                                                           
             Please respond to                                             
             Midrange Systems                                              
                 Technical                                                 
                Discussion                                                 
             <midrange-l@midra                                             
                 nge.com>                                                  
                                                                           
                                                                           




Hi Michael,

I have played with EVIs a bit, not an expert by any means...

Regarding your proposed use of EVI, I would expect that a full rebuild of
the EVI after adding records would be more efficient than updating the
existing EVI per update.  The problem with dynamically updating EVIs
relates to its implementation.

In an EVI, an index is created for each value found in the key field.  Its
important to have low cardinality of the key values.  Along with each index
value, DB2 builds a row-bitmap that is long enough to account for every row
in the database.  If you have 10 million rows in your table, then you need
10 million bits in the bitmap.

If you add records to the table, the bitmap field in the index must be
expanded for EVERY index value.  The system must resize this bitmap for
every new row, which can be very costly.  IBM therefore recommends that the
EVI be removed before inserts, then rebuild the index afterward.

The real value of EVI comes when selecting rows in a query.  IBM can obtain
the bitmap that describes the rows that contain the selected value, then
using boolean operations, "and" and "or" the bitmaps to construct the rows
that satisfy the query.  Its very slick and efficient when implemented
correctly....

Eric

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of
Michael_Schutte@xxxxxxxxxxxx
Sent: Friday, January 11, 2008 3:44 PM
To: midrange-l@xxxxxxxxxxxx
Subject: EVI (encoded vector index) question



Anybody experienced in creating EVIs (encoded vector index)?  I'm reading
the IBM help site but I have a couple of questions.

http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp?topic=/rzajq/whatareevi.htm


I have a new table that's going to have records added to it monthly.

The IBM site says to create EVIs on Read-only tables or tables with minimum
of INSERT, UPDATE, DELETE activity.

Once a month activity (at night) I wouldn't think would be an excessive
amount of activity.   What do you think?

If you agree with me, the site also states When Loading Data...
1. Drop EVIs, load data, create EVIs.


Is that true?  It sounds like to me that before the monthly updating...
I'll need to drop the EVIs, then add my new records... then recreate the
EVIs.

That kinda sounds right, but also it doesn't.  It sounds right so that the
information can be regathered. but then again, shouldn't that happen on
it's own?

If I have to drop the EVIs and recreate then I'm not going to be able to do
this.  All though this table is only going to add new monthly records every
month, it's going to contain a lot of records.  I'm not sure if we can
afford to have the recreation to run every month.

Any help will be appreciated.

Michael Schutte
Admin Professional
Bob Evans Farms, Inc.
"The Secret's the Sauce! Enjoy our new Bob-B-Q Pulled Pork Knife & Fork
Sandwich!"

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



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