If you use a case-insensitive sort order, Adaptive Server cannot use an index on an encrypted char or varchar column when performing a join with another column or a search based on a constant value. This is also true of an accent-insensitive sort order.
For example, For example, in a case-insensitive search, the
string abc matches all
strings in the following range: abc, Abc, ABc, ABC, AbC, aBC, aBc, abC. Adaptive
Server must compare abc against
this range of values. By contrast, a case-sensitive comparison of
the string abc to the column
data matches only identical column values, that is, columns containing abc.
The main difference between case-insensitive and case-sensitive
column lookups is that case-insensitive matching requires Adaptive
Server to perform a range search whereas case-sensitive matching
requires an equality search.
An index on a nonencrypted character column orders the data
according to the defined sort order. For encrypted columns, the
index orders the data according to the cipher text values, which
bears no relationship to the ordering of plain text values. Therefore,
an index on an encrypted column is useful only for equality and
non-equality matching and not for searching a range of values. abc and Abc encrypt
to different cipher text values and are not stored adjacently in an
index.
When Adaptive Server uses an index on an encrypted column,
it compares column data in cipher text form. For case sensitive
data, you do not want abc to
match Abc, and the cipher
text join or search based on equality matching works well. Adaptive
Server can join columns based on cipher text values and can efficiently
match where clause values. In this example, the maidenname column
is encrypted:
select account_id from customer where cname = 'Peter Jones' and maidenname = 'McCarthy'
Providing that maidenname has been encrypted
without use of an initialization vector or random padding, Adaptive
Server encrypts McCarthy and
performs a cipher text search of maidenname.
If there is an index on maidenname, the search uses of
the index.
