Skip to Content

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:

  1. 32767; since that was the technical limit on that DB.
  2. 42; since 42 is always a nice answer when asked for a figure.
  3. “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

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply