Re: Index Advisor Question

Hi Gary

Thanks for the response.

Indexes are expensive but only "one time" after that you pay incrementally
so the cost is minmal, Conversely, if you don't have them they are
expensive every time.

At least at one point in time multiple column indexes provided an advantage
to other indexes based on the same columns if the largest (i.e most
columns) index was built first. I did some rebuilding of indexes where the
compound indexes were built first and other columsn already in the index
built very quickly. Implicit access path sharing I believe was the
terminology, though I don;t know if this is till true.




On Thu, Sep 19, 2013 at 10:20 AM, Gary Thompson <gthompson@xxxxxxxxxxx>wrote:

Evan,
Yes, just the question I had for myself and the reason I posted the way I
did.

Old habits based on old experience maybe, but my thinking was/is:
1) indexes are expensive
2) multiple column indexes are relatively
less useful because fewer queries benefit
3) diminishing returns as added columns have
fewer and fewer "candidate rows"


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:
midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Evan Harris
Sent: Wednesday, September 18, 2013 4:11 PM
To: Midrange Systems Technical Discussion
Subject: Re: Index Advisor Question

I'm curious as to why those who have offered alternatives would not take
the index advisor's advice.

Just looking to understand how a human expert discounts the advice of a
code-driven tool and hoping to gains some insight into how to use index
advisor better.

Why a 10 column index would be a red flag would be a good start - why is
that specifically bad ? (Wanting to understand why that is so, not question
the statement per se)


On Thu, Sep 19, 2013 at 8:54 AM, William P Hunter <wphunter@xxxxxxxxxxx

wrote:

I would start debug and run the sql statement via strsql and see what
shows up in the jobs messages.

From: dieter.bender@xxxxxxxxxxxx
To: midrange-l@xxxxxxxxxxxx
Subject: Re: Index Advisor Question
Date: Wed, 18 Sep 2013 22:45:44 +0200

Hi Gary,

first of all, I would collect some data with database Monitor
(STRDBMON)

to have a look to the real cost of those queries, then trying your
suggestion and have a look again to the cost in real life. If the cost
(= response time) is fine, best decision is: just do nothing.

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

--

Regards
Evan Harris
--
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.