What is the maximum no. of indexes per table???
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 18

Thread: What is the maximum no. of indexes per table???

  1. #1
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620

    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

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Yes, Yes, Yes.

    If you want to understand, go to tahiti.oracle.com and search for Index.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620
    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

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    May 2001
    Location
    Maryland, USA
    Posts
    409
    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

  6. #6
    Join Date
    May 2002
    Posts
    2,645

    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.

  7. #7
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434

    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"

  8. #8
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Originally posted by samdba
    Hi Jeff,

    Smart reply. But, I would like other users to contribute in more detail please.

    Thanks.
    adjective: lazy

  9. #9
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    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

  10. #10
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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
  •  


Click Here to Expand Forum to Full Width