Search Arguments and Encrypted Columns

For equality and nonequality comparison of an encrypted column to a constant value, SAP ASE optimizes the column scan by encrypting the constant value once, rather than decrypting the encrypted column for each row of the table.

For example:

select sum(d.amount) from daily_xacts d
     where creditcard = '123-456-7890'

SAP ASE cannot use an index to perform a range search on an encrypted column; it must decrypt each row before performing data comparisons. If a query contains other predicates, SAP ASE selects the most efficient join order, which often leaves searches against encrypted columns until last, on the smallest data set.

If your query has more than one range search without a useful index, write the query so that the range search against the encrypted column is last. This example which searches for the Social Security Numbers of taxpayers earning more than $100,000 in Rhode Island positions the zipcode column before the range search of the encrypted adjusted gross income column:

select ss_num from taxpayers
       where zipcode like '02%' and
       agi_enc > 100000

Referential Integrity Searches

Referential integrity probes match at the cipher text level if both the following are true:

The datatypes of the primary key and foreign key match according to the rules described above.
The encryption of the primary and foreign keys meets the key requirements for joining columns.