Sybase IQ Index Types

This section explores in depth the reasons you might use each of the column index types.

• The Fast Projection (FP) Default Index Type
  When you create a permanent table in a Sybase IQ database, IQ stores all column values in a default index. This default index, called an FP (Fast Projection) index, optimizes projections and enables certain kinds of search conditions to be evaluated.
• The Low_Fast (LF) Index Type
  This index is ideal for columns that have a very low number of unique values (under 1,000) such as sex, Yes/No, True/False, number of dependents, wage class, and so on. LF is the fastest index in Sybase IQ.
• The High_Group (HG) Index Type
  The High_Group index is commonly used for join columns with integer data types. It is also more commonly used than High_Non_Group because it handles GROUP BY efficiently.
• The High_Non_Group (HNG) Index Type
  Add an HNG index when you need to do range searches.
• The Compare (CMP) Index Type
  A Compare (CMP) index is an index on the relationship between two columns. You may create Compare indexes on any two distinct columns with identical data types, precision, and scale. The CMP index stores the binary comparison (<, >, or =) of its two columns.
• The Containment (WD) Index Type
  The Containment (WD) index allows you to store words from a column string of CHAR, VARCHAR, and LONG VARCHAR data.
• The Date (DATE), Time (TIME), and Datetime (DTTM) Index Types
  Three index types are used to process queries involving date, time, or datetime quantities:
• TEXT Indexes
  To use TEXT indexes, you must be specifically licensed to use the unstructured data analytics functionality.
• Optimizing Performance for Ad Hoc Joins
  Indexes can be created to optimize ad hoc join performance.
• Selecting an Index
  Certain criteria, such as the number of unique values, help you to identify appropriate index types for your data.