Encryption in the index

Database
Enthusiast

Encryption in the index

Can a column that will be encrypted be used as part of an index? 

Tags (3)
4 REPLIES
Enthusiast

Re: Encryption in the index

I am not sure. Will it hash?????. But I don't think it is advisable , since when use in  where clause, it is nightmare.

Teradata Employee

Re: Encryption in the index

Yes it can. But then use of the index will only be made if you write the query to provide the encrypted value for comparison rather than the clear value.

WHERE index_field=ENCRYPT_Funct(<value>)

rather than

WHERE index_field=<value>

If you do the latter, it will not be able to use the index or at best will have to scan the whole index, decrypting every value to do the matching.

Re: Encryption in the index

which feature may be used to enable data encryption ?

Roles, Profiles, column-level security,  user defined functions (UDF),  X views in the Data Dictionary

Enthusiast

Re: Encryption in the index

Depending on the version of TD you are using, you may be able to collect stats on the expression for the decryption of the column which could help with performance.

--Searching on decrypted index (expression stats)
WHERE DECRYPT_function(index)=<value>
William Miteff