Sybase Adaptive Server IQ 12.4.2, Administration And Performance Manual

Page: 170 / 536

Adaptive Server IQ index types
150
AND l_suppkey = s_suppkey
AND c_nationkey = s_nationkey
AND s_nationkey = n_nationkey
AND n_regionkey = r_regionkey
AND r_name = ’ASIA’
AND o_orderdate >= ’1994-01-01’
AND o_orderdate < ’1995-01-01’
GROUP BY n_name
HAVING n_name LIKE "I%"
AND SUM(l_extendedprice*(1-l_discount)) > 0.50
ORDER BY 2 DESC
All columns referenced in this query except l_extendedprice and l_discount
should have an
LF
or
HG
index.
Selecting an index
Here is a quick chart that summarizes how to select an index type.
Criteria to identify Index to select
Note indexes created automatically on all columns. Default index
Note indexes created automatically on columns with
UNIQUE
or
PRIMARY KEY
constraint.
HG
with
UNIQUE
enforced
Identify all columns used in a join predicate and choose
the index type depending on the number of unique values.
HG
or
LF
Identify columns that contain a low number of unique
values and do not already use multiple indexes.
LF
Identify columns that have a high number of unique
values and that are part of a
GROUP BY clause in a select
list in a
SELECT DISTINCT
or
DISTINCT COUNT
.
HG
Identify columns that may be used in the
WHERE
clause
of ad hoc join queries that do not already have
HG
or
LF
indexes.
HG
or
LF
Identify columns that have a high number of unique
values and that will not be used with
GROUP BY
,
SELECT DISTINCT or DISTINCT COUNT .
HNG
Look at any remaining columns and decide on additional
indexes based on the number of unique values, type of
query, and disk space. Also, for all columns, be sure that
the index types you select allow the data type for that
column.