Sybase Adaptive Server IQ 12.4.2, Administration And Performance Manual

Page: 165 / 536

CHAPTER 4 Adaptive Server IQ Indexes
145
Default column index
For any column that has no index defined, or whenever it is the most effective,
query results are produced using the default index. This structure is fastest for
projections, but generally is slower than any of the three column index types
you define for anything other than a projection. Performance is still faster than
most RDBMSs since one column of data is fetched, while other RDBMSs need
to fetch all columns which results in more disk I/O operations.
Projections on few rows
If a column is used only in projections, even if some of the queries return a
small number of rows, Low_Fast and High_Non_Group indexes are redundant
because the default structure is equally as fast for projecting a small number of
rows.
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 Adaptive Server IQ.
When you test for equality, just one lookup quickly gives the result set. To test
for inequality, you may need to examine a few more lookups. Calculations such
as
SUM
,
AVG
, and
COUNT
are also very fast with this index.
As the number of unique values in a column increases, performance starts to
degrade and memory and disk requirements start to increase for insertions and
some queries. When doing equality tests, though, it is still the fastest index,
even for columns with many unique values.
Recommended use
Use an
LF
index when:
• A column has fewer than 1,000 unique values.
• A column has fewer than 1,000 unique values and is used in a join
predicate.
Never use an
LF
index for a column with 10,000 or more unique values.