Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> Re: Measuring index usage
On Wed, 27 Sep 2000 01:45:11 GMT, oratune_at_aol.com wrote:
>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.
Jonathan pointed out, correctly, that an application could depend on the records to be retreived in a specific order. My point is that this order could also change as a result of changed statistics (we do a compute every night). Changed statistics could mean that one day Oracle decides to use a different query plan, skipping the index we need for correct ordering.
>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).
Which was my original question. What is the best way to determine if an index is ever used for data retreival?
>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.
I hope I'll never get into that position, but when I do, I might lose the battle, but I'll certainly win the war. I'm sure my management will make them "an offer they can't refuse" ;-)
>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.
I appreciate the imput a lot. Jonathan made a valid point that convinced me to get approval from the supplier before touching the indexes. It never occurred to me that changing or adding a (non-unique) index could lead to application errors. I am usually very carefull (ask our developers ;-), and this is one more bullet on my list of "no I won't do that without approval" items.
-- Jan jantah_at_hot.mail.com ...and you know what to do with "hot.mail", right?Received on Wed Sep 27 2000 - 11:46:07 CDT
![]() |
![]() |