If you use a case insensitive sort order, Adaptive Server is unable to make use of 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.
Using a case-insensitive comparison, the string abc matches
all strings in the following range: abc, Abc, ABc, ABC, AbC, aBC, aBc and abC.
When Adaptive Server makes a case-insensitive search for a column
value matching abc, it must
compare abc against this
range of values. By contrast, a case-sensitive comparison of the
string abc to the column
data will match only identical column values, for example, 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.
For non-encrypted columns an index on a character column orders
the data according to the defined sort order. For encrypted columns
the index orders the data according to the ciphertext values. The
ordering of ciphertext values bears no relationship to the ordering
of plaintext values. For this reason an index on an encrypted column
is useful only for equality and non-equality matching and not for
searching a range of values. The strings abc and Abc encrypt
to different ciphertext values and are not stored adjacently in
the index.
When Adaptive Server uses an index on an encrypted column
it is comparing the column data in its ciphertext form. For case
sensitive data, you do not want abc to
match Abc, and the ciphertext
join or search based on equality matching works well. Adaptive Server
can join columns based on ciphertext values and can efficiently
match where clause values.
For example, assume in the following example that 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, Adaptive Server will encrypt McCarthy and
perform a ciphertext search of maidenname.
If there is an index on maidenname, the search
will make use of the index.
However, for a case insensitive ordering, this strategy of
encrypting the constant is not useful because Adaptive Server must
look for a range of values such as mccarthy, MCCARTHY,
and so on, where the ciphertext values are not ordered according
to the server’s character set. Adaptive Server must decrypt every
row in the name column before doing a case insensitive comparison
with McCarthy.
