If we create a 32 secondary indexes on table what might be the performance

General
Enthusiast

If we create a 32 secondary indexes on table what might be the performance

Hi Everyone,

If we create a 32 secondary indexes on a table whether in decreases performance or not?

Regards,

Purushotham..

2 REPLIES
Supporter

Re: If we create a 32 secondary indexes on table what might be the performance

Decreases performance of what?


It will definitely add space requirements and also any insert/update and delete on this table will take longer as all 32 indices might need to be maintained as well.


You should only add a secondary index in case you can proof that it supports queries accessing the table and the negative impact on table size and maintenance is justifying this (e.g. if this query runs only once a quarter it might not be worth to spend this extract money on space and maintenance, where it might be if this is a very critical query)...


But life is complicated and colorful and you will have always difficulties to divide it into black and white.

Enthusiast

Re: If we create a 32 secondary indexes on table what might be the performance

Creating a secondary index causes the system to build a subtable to contain its index rows, thus adding another set of rows that requires updating each time a table row is inserted, deleted, or updated.

Why dont you take a huge table  with one SI and one with 32 SI and run explain on any dml operation.