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

Printable View

04-30-2003, 09:59 AM
samdba

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.
04-30-2003, 10:31 AM
marist89

Yes, Yes, Yes.

If you want to understand, go to tahiti.oracle.com and search for Index.
04-30-2003, 11:23 AM
samdba

Hi Jeff,

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

Thanks.
04-30-2003, 11:37 AM
slimdave

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.
04-30-2003, 12:40 PM
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
04-30-2003, 02:34 PM
stecal

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

Quote:

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.
05-01-2003, 08:15 AM
abhaysk

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

Quote:

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 ).
05-01-2003, 08:47 AM
pando

Quote:

Originally posted by samdba
Hi Jeff,

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

Thanks.

adjective: lazy
05-01-2003, 09:04 AM
julian

Quote:

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.
05-01-2003, 09:14 AM
DaPi

Quote:

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 :)
05-01-2003, 09:55 AM
julian

Quote:

Originally posted by DaPi
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 :)

Yeah right.. I am planning to turning the computer off and opening a bottle of Jameson.
05-01-2003, 10:33 AM
patel_dil

Quote:

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.

Good Point, Julian.
05-01-2003, 10:39 AM
DaPi

Quote:

Originally posted by julian
opening a bottle of Jameson.

Small stuff! That's only 10**26 molecules :)
05-01-2003, 10:58 AM
grjohnson

There is a limit depending on the tables volume and hardward available. I had a table with 5 indexes being inserted at a rate of 700 records per second and the hardard couldn't keep up (With 4 indexes it could). But the same table with 10 inserts per second would be fine.
05-01-2003, 01:53 PM
tamilselvan

Also remember,

Maximum number of columns allowed in a bitmap index is 30.
05-02-2003, 02:35 AM
DaPi

Quote:

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.

After relection, I now realise that a tighter limit is that of producing unique 30 character names using alphanum (plus _ $ and #) - there are only 10**45 of these ! :)
05-02-2003, 03:03 AM
slimdave

You could use mixed-case naming of course. Never neglect the beneficial use of bad design practices!
05-02-2003, 04:01 AM
DaPi

slimdave, I did think of mixed case after posting - thought that was in the domain of "b*gg*r*ng flies" - OK, with mixed case you get to around 10**54

(BTW if you're into trivia - Archimedes' "Sand Reckoner" is the earliest known discussion of naming big numbers. He estimated the number of grains of sand that would fit in a sphere the size of his "universe" - something like the size of the solar system. The biggest number in his first "period" was 10**80000 and he didn't stop there!)

grjohnson, what is this "hardware" you speak of that so limits human imagination?