- How frequently you must submit ALTER TABLE TO CURRENT requests to refresh join index rows.
- The type of DATE, CURRENT_DATE, or CURRENT_TIMESTAMP condition that is specified in the join index definition.
If the join index rarely requires refreshing and its DATE, CURRENT_DATE, or CURRENT_TIMESTAMP condition is such that Vantage must remove and insert a large number of rows, dropping and recreating the join index is typically more efficient.
When the primary index column of a join index is updated because of a new DATE, CURRENT_DATE, or CURRENT_TIMESTAMP value, ALTER TABLE TO CURRENT deletes all rows from the join index and rebuilds the join index with the new DATE, CURRENT_DATE, or CURRENT_TIMESTAMP value.
When DELETE ALL followed by an index rebuild is more efficient, but ALTER TABLE TO CURRENT does not use the join index, consider dropping and recreating the join index. Vantage drops the join index and the existing privileges and statistics on the join index, so factor the time required to grant the dropped privileges and to recollect statistics into deciding whether to use this approach.
When you must issue ALTER TABLE TO CURRENT requests for the base table and the join indexes defined on that table, consider submitting the ALTER TABLE TO CURRENT requests only on those join indexes that specify a lower bound DATE, CURRENT_DATE, or CURRENT_TIMESTAMP condition first. A lower bound DATE, CURRENT_DATE, or CURRENT_TIMESTAMP condition is one that causes rows from the join index to be deleted when the DATE, CURRENT_DATE, or CURRENT_TIMESTAMP is refreshed to the latest date or timestamp value, for example using a condition such as j > CURRENT_DATE. You may have to do join index maintenance on the base table, but not on the rows that ALTER TABLE TO CURRENT deleted.