Indexes Temporarily Disabled During Refresh

Question

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

Environment

HVR 6

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/re-enabled
• 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.

The trigger-based capture method (Capture_Method=DB_TRIGGER) has been deprecated since 6.2.0/0.