Re: Measuring index usage

>I still can't see why there should be a problem here. The supplier is
>not the enemy, we share the same goals.

It's not a question of the supplier being antagonistic, simply a question of playing safe. Unless the supplier can hand you the document which says:

    Index X exists to satisfy the requirements of module Y     because ...., then it is very hard to PROVE that any     index can be safely dropped.

Conversely, if the supplier is so up to scratch that they can produce this document, then they will already have a document which says -

    if you are not using module A, then index B (and     tables P Q and R) can be dropped.

Your comment about statistics is valid - it hadn't crossed my mind before, but some developers could be careless enough to assume that their code works, when in fact it is purely a side-effect of an index they do not know about. In this case an unlucky change of stats could result in real errors occurring.

I was thinking more of the 'rownum = 1' traps that get into production code, specifically things like:

        select /*+ index(t1, ind1) */
        from t1
        where colA = 'asdf'
        and colB = 'asdfa'
        and rownum = 1

(or the more prevalent 'index_desc' hint to get the maximum value without doing a
sort group by.

Such code depends on the write index
existing with the right name.

The critical trap is the ROWNUM = 1 in the SQL, but even if it doesn't appear in the SQL then it could still be hidden in the application code.

--

Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Jantah wrote in message ...


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