Adaptive Server optimizes the joining of two encrypted columns by performing cipher text comparisons if:
The joining columns have the same datatype. For cipher text 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 must 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 cipher text 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 uses the default sort order.
This example sets a schema to join on cipher text:
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 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
