Adaptive Server optimizes the joining of two encrypted columns by performing ciphertext comparisons if:
The joining columns have the same datatype. For ciphertext comparisons, char and varchar are considered to be the same datatypes, as are binary and varbinary.
For int and float types, the columns have the same length. For numeric and decimal types, the columns have the same precision and scale.
The joining columns are encrypted with the same key.
The joining columns are not part of an expression. For example, you cannot perform a ciphertext join on a join where t.encr_col1 = s.encr_col1 +1.
The encryption key was created with init_vector and pad set to NULL.
The join operator is ‘=’ or ‘<>’.
The data has the default sort order.
For example, this sets a schema to join on ciphertext:
create encryption key new_cc_key for AES with init_vector NULL create table customer (custid int, creditcard char(16) encrypt with new_cc_key) create table daily_xacts (cust_id int, creditcard char(16) encrypt with new_cc_key, amount money........)
You can also set up indexes on the joining columns:
create index cust_cc on customer(creditcard)create index daily_cc on daily_xacts(creditcard)
Adaptive Server executes the following select statement to total a customer’s daily charges on a given credit card without decrypting the creditcard column in either the customer or the daily_xacts table.
select sum(d.amount) from daily_xacts d, customer c where d.creditcard = c.creditcard and
c.custid = 17936
