Masking a Column With an Index (KBA7295)

Error while masking table - Drop Indexes = Off 

If the Index on the column enforces the Unique Constraint and the masked data is not unique, the masking job will fail with a Unique Constraint Violation error:

Example - Oracle:

[JOB_ID_yy_xx] - Update.0 - ORA-00001: unique constraint (...) violated

Error after masking finished - Drop Indexes = On

If 'Drop Indexes' is selected, or the Indexes are created manually or in a Post-Script, and there are duplicated values in the masked data, the following error (or similar) will be shown:

Example - Oracle:

[JOB_ID_yy_xx] - Execute_Create_Index.0 - ERROR: An error occurred, processing will be stopped:
[JOB_ID_yy_xx] - Execute_Create_Index.0 - Couldn't execute SQL: CREATE UNIQUE INDEX ...
...
[JOB_ID_yy_xx] - Execute_Create_Index.0 - ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

Resolution 

In order to mask a column with a Unique Constraint, the Algorithm has to have Referential Integrity and 1:1 Mapping. The algorithm will, therefore, need to be one of the following: 

• Character Mapping Algorithm (replaces Segment Mapping)
• Mapping Algorithm
• Custom Algorithm developed specifically for a Key (Primary or Foreign) column

Note: On these indexes, it is recommended to start executing the job with Drop Indexes = Off. The reason for this is that the job will fail as soon as there is an error and it will indicate the violation. When the job works, the Drop Indexes can be set to On for extra performance gain. 

Error while masking table - Drop Indexes = Off 

If the Index on the column enforces the Unique Constraint and the masked data is not unique, the masking job will fail with a Unique Constraint Violation error:

Example - Oracle:

[JOB_ID_yy_xx] - Update.0 - ORA-00001: unique constraint (...) violated

Error after masking finished - Drop Indexes = On

If Drop Indexes is selected, or the Indexes are created manually or in a Post-Script, and there are duplicated values in the masked data, the following error (or similar) will be shown:

Example - Oracle:

[JOB_ID_yy_xx] - Execute_Create_Index.0 - ERROR: An error occurred, processing will be stopped:
[JOB_ID_yy_xx] - Execute_Create_Index.0 - Couldn't execute SQL: CREATE UNIQUE INDEX ...
...
[JOB_ID_yy_xx] - Execute_Create_Index.0 - ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

Resolution 

The resolution depends on the function used in the Index. It might be that the masked data needs to have specific characteristics or be unique (see above for algorithms). 

• Check the index on the database. 
• Manually drop the index on the masked column.
• Mask.  
• Manually recreate the index on the masked column. 

Note: In this case too, it is recommended to start executing the job with the Drop Indexes = Off. The reason for this is that the job will fail as soon as there is an error and it will indicate the violation. When the job works, the Drop Indexes can be set to On for extra performance gain. 

The configuration 

The masking job is configured as follows:

• Table Columns:
  • 'ID' (logical key)
  • 'Mask' (masked) (with Clustered Index ASC)
• Algorithm: 'Mapping Algorithm' (with 8 values (1 to 8))
• Commit Size: 4

Explanation 

What happens here?

• The ORDER happens after each UPDATE.
• This has an impact on performance. 

Events (refer to the tables below):

• UPDATE 1: A to 6  > Same order as 6 is still first.
• UPDATE 2: B to 4  > 4 is now first.
• UPDATE 3: C to 7  
• UPDATE 4: D to 1  
• COMMIT (*)
• UPDATE 5: E to 5  
• UPDATE 6: F to 8  
• UPDATE 7: G to 3  
• UPDATE 8: H to 2  > The table now has a completely new order. 
• COMMIT (*)

# Original table   # Masked table     # Masked table       # Masked table   # before masking   # UPDATE 1         # UPDATE 2           # UPDATE 8 ----------------   ----------------   ---------------- ... ---------------- SELECT *           SELECT *           ...                  ... FROM Msk;          FROM Msk; +----+------+      +----+------+      +----+------+        +----+------+ | ID | Mask |      | ID | Mask |      | ID | Mask |        | ID | Mask | +----+------+      +----+------+      +----+------+        +----+------+ | 1  | A    |      | 1  | 6    |      | 2  | 4    |        | 4  | 1    | | 2  | B    |      | 2  | B    |      | 1  | 6    |        | 8  | 2    | | 3  | C    |      | 3  | C    |      | 3  | C    |        | 7  | 3    | | 4* | D    |      | 4* | D    |      | 4* | D    |        | 2  | 4    | +----+------+      +----+------+      +----+------+        +----+------+ | 5  | E    |      | 5  | E    |      | 5  | E    |        | 5  | 5    | | 6  | F    |      | 6  | F    |      | 6  | F    |        | 1  | 6    | | 7  | G    |      | 7  | G    |      | 7  | G    |        | 3  | 7    | | 8* | H    |      | 8* | H    |      | 8* | H    |        | 6  | 8    | +----+------+      +----+------+      +----+------+        +----+------+

Resolving this issue

For most databases, the solution here is to, on the Job Configuration pop-up, check Drop Indexes.

 

For databases without this feature, or if needed to be executed manually:

• Manually drop the Clustered Index on the masked column.
• Mask.  
• Manually recreate the Clustered Index on the masked column. 

 

Note: Some Clustered Indexes might be added as Constraints - these might not be automatically Disabled and has to be manually removed using ALTER TABLE. 

 

 

 

 

---