- They must be updated every time a row is updated, deleted, or added to a table.
This is only a consideration for indexes other than the primary index in the Vantage environment. The more indexes you have defined for a table, the bigger the potential update downside becomes.
Because of this, secondary, join, and hash indexes are rarely appropriate for OLTP situations.
- All secondary indexes are stored in subtables, and join and hash indexes are stored in separate tables, exerting a burden on system storage space.
- When FALLBACK is defined for a table, a further storage space burden is created because secondary index subtables are always duplicated whenever FALLBACK is defined for a table. An additional burden on system storage space is exerted when FALLBACK is defined for join indexes or hash indexes or both.
For this reason, it is extremely important to use the EXPLAIN modifier to determine optimum data manipulation statement syntax and index usage before putting statements and indexes to work in a production environment.