RE: index advisor

Thanks Chuck;

Can you comment on how to determine which indexes I should create based on the Index Advisor report?

Which columns are most important ie when large values are reported, these would make a wise index choice?

- "Times advised for query use"?

- "Most expensive query estimate"?

Thanks



-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of CRPence
Sent: Monday, March 04, 2013 3:19 PM
To: midrange-l@xxxxxxxxxxxx
Subject: Re: index advisor

On 04 Mar 2013 11:55, Stone, Joel wrote:

If one row advises to add (FieldA , FieldB), and a second row
advises FieldA for the same file, would adding a logical file
with keys FieldA & FieldB do the trick for both advising rows?

In most cases, the one Access Path with both columns as keys is
probably the best. There are likely some cases where the /baggage/ of
the extra column FieldB in the Keyed Access Path could discourage the
choice of that AccPth by the optimizer for query implementation.

Does creating a LF benefit the same as adding an index?

A Keyed DDS LF can have the same benefit as a SQL DDL CREATE INDEX
when creating the /same/ Keyed Access Path. For just keyed columns, the
typical keyed DDS LF will be created with PAGESIZE(8) by default, per
the default specification of PAGESIZE(*KEYLEN), for which the optimizer
would tend *not* to choose that AccPth for implementation except perhaps
in limited memory environments; i.e. the larger page sizes [64+] are
preferred by the query engines. The default page size for SQL [i.e.
PAGESIZE 64] is what is the preference for the query engines as the minimum.