Indexes Temporarily Disabled During Refresh

Question

Which indexes/constraints are temporarily dropped/disabled during HVR refresh?

Environment

HVR 5

Answer

This depends on:

• the database you use
• version of HVR that you are using

Ingres & Vector

• Referential constraints TO a table are dropped/created
• Referential constraints FROM a table are dropped/created
• Storage structure (bulk refresh)
• Volatile secondary indexes (bulk refresh)
• Persistent secondary indexes (bulk refresh)
• Primary key constraints (bulk refresh)
• Journaling (bulk refresh)

Oracle

• Triggers disabled/reenabled
• Referential constraints TO a table are dropped/created
• Referential constraints FROM a table are dropped/created
• Domain indexes on LOB columns (bulk refresh)
• Check constraints (bulk refresh Oracle 9)

SQL Server

• Triggers are enabled/disabled
• Referential constraints TO a table are enabled/disabled (row-wise refresh)
• Referential constraints TO a table are dropped/created (bulk refresh)

All SQL statements that are needed to re-enable/re-create the constraints/triggers/storage structure is stored in the refresh recovery table: hvr_strr_ in the target database.

For lots of MPP targets, constraints are declaration only so there should not be any issues loading into tables due to referential integrity constraints.