The 'Revalidate originally validated constraints' option was not selected but during the post-switch validation it still runs and the SQL command 'ALTER TABLE xxx ENABLE VALIDATE PRIMARY KEY' took 1 hour to complete on table A but took only 1 second on table B. Table A and B has around the same number of rows but the size of table A is about 5 times the size of table B. What are the factors that may affect the time taken for ENABLE VALIDATE and is the table also locked during the validation?
By default, 'Revalidate originally validated constraints' is unchecked for performance reasons. However, the "Posting Key" (usually the Primary Key or the 'best' Unique Constraint) is still revalidated. This is so because if the same table is reorganized again there is certainty that the data is consistent. While validating a constraint that is already enabled does not require any Data Manipulation Language (DML) locks during validation. So the ENABLE VALIDATE will not prevent concurrent access to the table.
Factors that may affect time taken for ENABLE VALIDATE
1. Complexity of the constraints
If the primary key column is NOT NULL, in order to verify the primary key, Oracle would accomplish this by only reading the index and looking out for duplicates. However if the primary key column is NULL and in order to verify the primary key, Oracle would not be able to use the index and would have to do a full table scan looking for NULL values, in addition to verifying that there are no duplicates.
2. Activity on the table while Oracle is verifying the rows and the number of rows in the table
3. Number of blocks Oracle would have to read if a row does not fit in a block
