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!"
midrange.com
