Sybase Adaptive Server IQ 12.4.2, Administration And Performance Manual

Page: 187 / 536

CHAPTER 4 Adaptive Server IQ Indexes
167
SELECT sales_rep FROM sales_order
WHERE sales_rep = 299
the results show 20 rows with 299 in the sales_rep column.
However, if you enter:
SELECT emp_id FROM employee
WHERE emp_id = 299
the results show only one row with 299 in the
emp_id
column.
Note
Query optimizations for star joins rely on the underlying primary key-
foreign key relationships. Because Adaptive Server IQ does not enforce
foreign keys, in order for your query results to be exactly as expected, your
application needs to ensure that data inserted into or deleted from the database
does not violate the primary key-foreign key relationship.
To declare a foreign key, see “Creating primary and foreign keys” on page 125.
For other information on foreign keys, see “Declaring entity and referential
integrity” on page 281.
Modifying tables included in a join index
Once you have created a join index, you are restricted in the types of changes
you can make to the join index and its underlying tables and indexes.
You cannot drop any table that participates in a join index. Likewise, you
cannot use
ALTER TABLE
to add, drop, or modify a column that participates in
a join index. In both cases, you must first drop the join index. Then you can
either drop the table, or modify any columns that participate in the join index.
You can add columns to the tables that participate in a join index. However,
there are restrictions on inserting data into these columns, as described in the
next section.
You can drop indexes on columns not involved in the join relationship, and you
can add, drop or modify nonjoined columns of tables in a join index. However,
you cannot drop either the indexes on a join column or the join column itself.
You need at least one index on a column involved in a predefined join
relationship. It is highly desirable to have either an
HG
or
LF
index on all
columns that are part of a join index.