Re: EVI recommendation

There's no performance difference, AFAIK, between a separate index and a
embedded one really. Assuming you create both with the same page size.
DDS defaults to 8K, DDL to 64K (IIRC).

You certainly wouldn't want an EVI over a unique key (or PK ) of a table.
I'm surprised index adviser would ask for it. Are you up to date on PTFs?
Are you sure you're not misreading it? For instance, an EVI on some
transaction file that references the table in question, ie. has a foreign
key reference to the 1.3M record file, might be reasonable.

Personally, I'd be interested to know what IA would say when the table has
an actual PK defined instead of the DDS unique key.

Charles


On Mon, Nov 25, 2013 at 1:27 PM, Gary Thompson <gthompson@xxxxxxxxxxx>wrote:

I am working on top Index Advisor recommendations and
have a recommend to build an Encoded Vector Index on
a field which is declared as the single unique key to a file
used daily for several applications.

This file was created some years ago using DDS and has
about 1.3 million rows.

My questions:

1: To meet the Index Advisor recommendation, would it be a
good idea to create an SQL index on the recommended
field/column, or better to re-create the DDS file with DDL ?

Lazy programmer wants to just add an index, but diligent DB
admin thinks "pure" solutions give better performance ?

2: An MC Press article by Kevin Forsythe January 25, 2004 seems
to say EVI's are better "where there are few distinct values",
but this file has over a million distinct values, so lazy programmer
thinks this is a deal-breaker ?

Lazy programmer thinks this may be supported by October 31,
2013 developerWorks article: "Accelerated analytics - faster
aggregations using the IBM DB2 for i encoded vector index (EVI)
technology, but lazy programmer also thinks Index Advisor is
his friend ?
Note: the second paragraph of the Overview seems to support
Kevin Forsythe's advice.

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