9. Using Data at Rest Encryption : Indexing Encrypted Columns
Share this page                  
Indexing Encrypted Columns
Because encryption randomizes data in a way that destroys alphabetical order, an index built on an encrypted column is limited to “exact hit” lookups. The index is built with a tuple ID pointer (tidp) that records the row in the base table that contains the encrypted value.
The following restrictions apply to indexes on encrypted columns:
Indexed encrypted columns must not use SALT.
Indexes on tables that contain encrypted columns must be defined with HASH storage structure.
Because such an index only supports exact indexed value lookup, the cost of doing a range lookup or string pattern match on encrypted columns is much higher than a similar operation on an unencrypted column. In general, such queries should be avoided for performance reasons.