For equality and non-equality comparison of an encrypted column to a constant value, Adaptive Server optimizes the column scan by encrypting the constant value once, rather than decrypting the encrypted column for each row of the table. The same restrictions listed in “Joins on encrypted columns” apply.
For example:
select sum(d.amount) from daily_xacts d where creditcard = '123-456-7890'
Adaptive Server 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, Adaptive Server 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 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.
