RE: indexing

From: Ric Van Dyke <ric.van.dyke_at_hotsos.com>
Date: Tue, 19 Feb 2013 13:23:34 -0600
Message-ID: <C970F08BBE1E164AA8063E01502A71CF0202C9F4_at_WIN02.hotsos.com>

I have a blog post awhile back that has a query to show you which indexes are really being used , how they are used and how often. This might be a good place to start to know which indexes are really useful.

http://ricramblings.blogspot.com/2012/11/is-this-index-being-used.html

Oracle (the optimizer) CAN evaluate all of those indexes, and maybe even use them all in different cases. There isn't a magic number that is "too many", there used to be but that was a long time ago in a galaxy... (you know the rest of that).

Likely the nest is to use the query I wrote here (or some form of it) over time and collect hard numbers that show what is and what is not being used. Once you know the likelihood of use, if your in 11, you can at least start some testing by making some index invisible to see if they are really useful or not.

---

Ric Van Dyke
Education Director
Hotsos Enterprises LTD.

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Zelli, Brian Sent: Tuesday, February 19, 2013 11:09 AM To: oracle-l (oracle-l_at_freelists.org) Subject: indexing

I have developers who are able to create indexes thru their application builder. On one table they have 30 indexes. I've often said to them that they are creating too many but I get overruled by their manager. Are they creating too many? Can oracle handle many, many indexes? Is there a way I can at least minimize any depreciation of performance if they are going to be allowed to do this? ciao,
Brian

This email message may contain legally privileged and/or confidential information. If you are not the intended recipient(s), or the employee or agent responsible for the delivery of this message to the intended recipient(s), you are hereby notified that any disclosure, copying, distribution, or use of this email message is prohibited. If you have received this message in error, please notify the sender immediately by e-mail and delete this email message from your computer. Thank you.
--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Tue Feb 19 2013 - 20:23:34 CET