We are on 126.96.36.199.0/ Win2K using Oracle Apps. We have done lot of customization in Apps resulting performance problems because in custom queries, developers/designers have used Substr, upper and other functions in the Where clause due to which, indexes are not being used on most of the queries and this leads to FTS. I have created function based indexes whereever felt required.
But, I have following questions/doubts.
1. Can I just go on creating indexes where table is being queried without using indexes?
2. Will it not create any performance issues?
3. Is there a maximum limit on having no. of indexes per table?
Please help me understand the above.
Thanks in Adv.
To handle yourself, use your head. To handle others, use your heart
Well considering that you don't seem to understand the implications of putting indexes on tables, you sure were keen on doing so, eh?
Adding more indexes slows up delete, insert , and update (on the indexed column). Rule of thumb says that if an insert takes 1 unit of time with no indexes on table, then for each (non bitmap) index added the insert will require an extra 3 units of time. Maybe a little more for function based indexes.
Also, you can easily invalidate your support contract on a 3rd party app by adding/modifying/deleting indexes.
Maximum no. of indexes per table can be found in documentation -- look in the Reference manual, under database limits.
Re: What is the maximum no. of indexes per table???
Originally posted by samdba Please help me understand the above.
Although you may think Jeff's answer was "smart," it wasn't. If you had written something like, "I've read the Concepts manual, but I still don't understand how creating a million indexes on one table is bad," I'm willing to bet you would have received an answer more to your liking. You have to at least try a little bit on your own first, and Jeff's answer to you reflects the fact you made no effort. Telling you to read a particular manual is, in fact, helping you understand. Whether you get that message or not is another matter.