Creating a Secondary Index
A secondary index can be created on attributes that are involved in restrictive filtering conditions. Such an index will improve the performance of "point" (very restrictive) SELECT queries, in which the WHERE clause is satisfied by only a small percentage of the total number of records.
Secondary indexes are useful in cases where filtering is done on attributes for which data appears to be random and the vast majority of blocks still need to be read from disk. In such cases, the min-max indexes used by X100 are ineffective. (The min-max indexes are effective for tables where the data is roughly sorted on the attributes on which the filtering is done; for example, sales data is often entered in the order of sale_date and queries often restrict on a range of sale dates.)
When using secondary indexes, performance may be degraded on:
• INSERT and UPDATE queries when indexed attributes are involved
• COMBINE on tables with indexed attributes
• Server start
Also, memory use is increased. Memory use and performance degradation increase linearly with the number of indexes and indexed attributes.
A secondary index is created with the statement:
CREATE INDEX name ON table_name (column_list) WITH STRUCTURE=X100_SI;
Note: Currently, secondary indexes cannot be created on tables with more than 4 billion rows.
Last modified date: 01/30/2023