Re: index advisor

There's a bad habit here where programmers are forgetting to select on a
field that is always blank (currently not being used but could be in the
future). All of logicals have this field in the index and if you don't
select on it, it would cause the adviser to suggest a new index. In short,
I usually look at the SQL statements and see where improvements can be
made. If so, then I ask for a project to get the select fix.

If not, then I start with the most advised queries.

For example, all files have store and suffix. We currently do not use
suffix however, suffix is in the key. If you were to select from orders
where storer = 1 and order = 12345, you are going to get a suggestion of
storer and order. Even though we have one by Storer, Suffix, Order it's
going to suggest that new index. FYI, it may still use the Storer,
Suffix, Order index but it'll suggest the new one.

Even if suffix is used in the query, I often still get suggestions for a
new key where suffix, storer, and order is the key. I typically do not
create these because we already have an index where the first two are flip
flopped. It's suggesting that key because the distinct possibilities for
suffix is less than storer and order.



On Mon, Mar 4, 2013 at 5:26 PM, CRPence <CRPbottle@xxxxxxxxx> wrote:

On 04 Mar 2013 13:43, Stone, Joel wrote:

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"?

Too long since I have seen that stuff; given my amount of usage and
familiarity with the feature. Sorry, but I pretty much just know
intuitively what to do in most cases, so I tended not to [have to] use
the tooling. There should be some presentations available on the web. I
think mostly they start with getting the most-advised out of the way
first, and then to concentrate on specific queries later.

--
Regards, Chuck
--
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.