Filter
Top Products
Using join indexes
168
Adaptive Server IQ automatically applies the changes to the join index at the
same time as it changes the base table. You do not need to synchronize the join
index after any
ALTER TABLE on nonjoined columns.
Other restrictions on
ALTER TABLE for join indexes include the following:
• You cannot rename a column into or out of a
NATURAL join condition.
• You cannot add a column that would participate in a previously specified
NATURAL join.
• You cannot drop a
PRIMARY KEY/FOREIGN KEY relationship if it matches
a join condition that is in use in a join index.
• You cannot drop a
NOT NULL constraint from a column that participates in
a join condition.
• You cannot modify the data type of a column that participates in a join
condition.
Inserting or deleting from tables in a join index
You always insert or load into, or delete from, the underlying tables, not the
join index itself. When you first create the join index, Adaptive Server IQ
synchronizes the joined tables automatically, whether or not you have
previously loaded data into the tables.
If you insert into or delete from a table that participates in an existing join
index, you must synchronize the join index explicitly, unless you are updating
the top table in the join hierarchy. If you insert rows and then delete them
before the synchronization takes place, Adaptive Server IQ optimizes
synchronization to omit the insertions.
You cannot perform partial-width inserts to tables that participate in a join
index. If you need to add columns to a table in a join index, you must do one
of the following:
• Drop the join index, do the partial-width insert, and then recreate the join
index.
• Load or insert into all columns of the table.