Re: Measuring index usage

In article <lk72tso3dfi7rmb2tcn8kro109hvufebp8_at_4ax.com>,   Jantah <jantah_at_hot.mail.com> wrote:
> On Tue, 26 Sep 2000 09:05:06 +0100, Jonathan Lewis wrote:
>
> >Two problems with that:
> >
> >First - the supplier will probably say you must not
> >change the indexing.
>
> I still can't see why there should be a problem here. The supplier is
> not the enemy, we share the same goals. Plus the supplier is not a
> devine authority with infinite wisdom (and neither am I). We should
> both appreciate the fact that we can learn things from each other. At
> least, that's the way we've always done business with them.
>
> >Second - the supplier MIGHT let you drop an index,
> >and not be aware that one of their developers has
> >written a piece of SQL that (illegally) assumes
> >that the data will return in a given order that happens
> >to be dependent on a specific index.
>
> That's a good argument for not changing any indexes on my own (a bit
> far-fetched, but you never know...).
> It's _not_ a good argument for not bothering at all. You have to draw
> the line somewhere. If the supplier says it's ok to change an index
> and my own investigations show the same, I won't hesitate for one
> second to implement the change. Following your logic, it won't be
> possible to implement any change, no matter what the source is.
> Following your logic, it won't even be possible to _use_ the system.
> Changing statistics on tables could lead to different query plans,
> which could lead to different ordering of queries, which could lead to
> the problem you describe.
>
> --
> Jan
>
> jantah_at_hot.mail.com
> ...and you know what to do with "hot.mail", right?
>

Statistics are different than indexes; statistics on tables will, most likely, tend to utilize the indexes at hand. Usually suppliers have no objection to setting more optimal conditions for the query optimizer, and there are times when the supplier asks that statistics be computed to "improve performance". Computing or estimating statistics on tables does not change the index structure whereas dropping indexes or creating new indexes certainly changes the overall database design from the supplier. And, remember, each additional index on a table is another object that needs maintenance, and another DML transaction for the table involved(inserts and deletes for primary keys, inserts, updates and deletes for other indexes). Don't be fooled; suppliers are notorious for being lovable little puppies one minute and snarling dogs the next, especially when physical changes to their database are concerned. Believe me, I've tried to fight this battle more than once and, more than once, I've been shot down even though I had the "proof" in my hands, in black and white, to prove my case.

Developers are prone to assumption, and the case Jonathan Lewis stated is true more often than you might care to think. Don't think we are against your desire to improve the performance of the application, we're not. But, most of us have been through this more than once and are speaking from experience. I urge you to think long and hard about your desired course of action. Again I admonish you to not change anything until you get WRITTEN approval from the supplier or vendor. You'll want documentation to back up your actions should someone from the supplier question the changes or threaten to cut short your warranty and/or support agreement.

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com http://www.deja.com/
Before you buy.