The default index structure (created by CREATE INDEX) generates a clustered structure causing the table data to be stored in an ordered fashion. This type of index can be used to effect an ordering of data where there is no natural ordering as described in Ordered Data.

A secondary index (created by CREATE SECONDARY INDEX) is an in-memory structure that has no disk footprint and is rebuilt from scratch when VectorH starts. A secondary index can speed up retrieval of small amounts of data from a “medium” sized table. If you have dimension tables with a few million rows and where queries select only a few rows, then a secondary index can speed up retrieval.

Use of these structures is not generally required and, unless they can be proven to improve performance, are generally discouraged.