-
What is the maximum no. of indexes per table???
Hi all,
We are on 8.1.7.3.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.
Sam
------------------------
To handle yourself, use your head. To handle others, use your heart
-
Yes, Yes, Yes.
If you want to understand, go to tahiti.oracle.com and search for Index.
Jeff Hunter
-
Hi Jeff,
Smart reply. But, I would like other users to contribute in more detail please.
Thanks.
Sam
------------------------
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.
-
Adding on further to Slimdave's comments, There is no limit to the maximum number of Indexes you can have on a Table.
Thanks
-- Dilip
-
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.
-
Re: What is the maximum no. of indexes per table???
Originally posted by samdba
Hi all,
1. Can I just go on creating indexes where table is being queried without using indexes?
Thanks in Adv.
Not a good idea....rather try optimising the query which fetches same result set...with join to indexed cols ( if u can, hopefully u cud use cols of PK's ).
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
Originally posted by samdba
Hi Jeff,
Smart reply. But, I would like other users to contribute in more detail please.
Thanks.
adjective: lazy
-
Originally posted by patel_dil
Adding on further to Slimdave's comments, There is no limit to the maximum number of Indexes you can have on a Table.
Thanks
The limitation comes from the maximum columns in one table: 1000; and maximum number of columns in a concatinated index: 32.
Oracle Certified Master
Oracle Certified Professional 6i,8i,9i,10g,11g,12c
email: ocp_9i@yahoo.com
-
Originally posted by julian
The limitation comes from the maximum columns in one table: 1000; and maximum number of columns in a concatinated index: 32.
Which gives a limit of the order of 10**100 (that's a Google isn't it?). Julian, nice to see you are planning ahead for some REALLY BIG databases
"The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|