Multi-Attribute Secondary Index
A secondary index can be built on more than one attribute.
For example: A company producing cars has the following table:
TABLE cars (
car_SN <- Primary Key
model <- SortKey (Clustered Index)
year_produced
body_type
color
price
)
Suppose a report is needed of the serial numbers of all blue Model-F Sedans the company produced after 2007.
There are thousands of cars with the same model (Model-F), thousands with the same body_type (Sedan), thousands of the same color (blue) and thousands produced after 2007. Having single-column indexes on some or all of these attributes would not help much because each of them would return thousands of record identifiers that would then need to be intersected.
In comparison, a multi-attribute index on all these attributes would be extremely effective:
CREATE INDEX cars_sidx ON cars(model_id, body_type, color, year_produced) WITH STRUCTURE=VECTORWISE_SI;
Note: Maintaining a secondary index introduces overhead on update operations and may not be justified if such queries are infrequent.