Question

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

Environment

Local Data Processing

Answer

This depends on the database.

Also note this depends on the version of HVR. Verify in our User Guides for any changes for more information.

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.