Sybase Adaptive Server IQ 12.4.2, Administration And Performance Manual

Page: 158 / 536

Creating Adaptive Server IQ indexes
138
• Because indexes are compact, more data can be kept in memory for
subsequent queries, thereby speeding throughput on iterative analysis.
• Tuning is data-dependent, allowing data to be optimized once for any
number of ad hoc queries.
Creating Adaptive Server IQ indexes
You can create a column index explicitly using either the
CREATE INDEX
statement or Sybase Central. These two methods are discussed in the sections
that follow.
The CREATE INDEX statement
To create an Adaptive Server IQ column index, use this syntax:
CREATE [ UNIQUE ] [
index-type ] INDEX index-name
... ON [ owner.]table-name
... (
column-name )
... [ { IN | ON }
dbspace-name ]
... [ NOTIFY integer ]
If you do not specify an index-type, Adaptive Server IQ creates an
HG
index.
Several front-end tools create an
HG
index automatically for this reason.
Examples The first example creates a High_Non_Group (
HNG
) index called
ship_ix
on
the
ship_date
column of the
sales_order_items
table.
CREATE HNG INDEX ship_ix
ON dbo.sales_order_items (ship_date)
The second example creates a Low_Fast index called
sales_order_region
on the
region
column of the
sales_order
table.
CREATE LF INDEX sales_order_region
ON dbo.sales_order (region)
By default, after every 100,000 records are inserted and loaded into indexes,
you receive a progress message. To change the number of records, specify the
NOTIFY
option of
CREATE INDEX
. To prevent these messages, specify
NOTIFY
0
.