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.