I recently delivered a service (ABAP OPEN SQL Optimization)
for a customer and the customer asked me: “What’s the upper limit of indexes
per table”. Three possible answers came immediately to my mind:
- 32767; since that was the technical limit on that DB.
- 42; since 42 is always a nice answer when asked for a figure.
- “IT DEPENDS”; since that answer is almost always correct.
I get this question on a regular basis, approximately once a
month. I immediately discarded 1.) since that’s written in the documentation and
a RTFM (Read The Fine Manual) would have been enough, but I thought the
customer wanted to know the practical upper limit not the technical upper
limit. So I was left with 2.) and 3.) and I decided for 2.). There were many
good old (grey haired) ABAP developers in the group so I thought it was save to
give this answer and then come to 3.).
This is what happened:
Customer: “What’s the upper limit of indexes per table”.
Me: “There is only one definite answer to this question: The upper limit of
indexes per table is 42!”
Nobody was laughing. Silence. Everybody was staring at me.
Obviously nobody knew The Hitchhiker's Guide to the Galaxy.
LEARNING: Never use 42 as an answer when people don’t know it.
Now my trouble was two-fold: Firstly I had to explain why I said 42
and next I had to explain that 42 is not the correct answer but “IT
DEPENDS”.
The nice thing about answer 3.) “IT DEPENDS” is that it is
almost always a correct answer. Always? No… of course it depends on the
question if “IT DEPENDS” is the correct answer… 😉 . The bad thing about this
answer is that you are supposed to explain on
what it depends… .
So: The upper limit of indexes per table depends on
- the type of the SQL and DML workload on the table
- whether reading (SQL) or writing (DML) performance
matters in your business processes - the columns in the indexes and the type of changes on
these columns - your hardware, your cache size, your IO subsystem
- and even more things
If you are really interested in indexes in detail I recommend reading
this book. In my book you will find a section on this topic as well.
You might be interested how many indexes per table you have
in your systems. On ORACLE you can find the top scorers with this SQL:
select table_name, count(*) as cnt
from dba_indexes
group by table_name
having count(*) > 3
order by cnt desc
On DB6 you can find the top scoreres with this SQL:
select tbname, count(*) as cnt
from sysibm.sysindexes
group by tbname
having count(*) > 3
order by cnt desc
