I'm very curious on how does TDE is implemented in practice. My goal is to get understanding on the usability constraint and possibility performance impact of applying such technique.
Thus I'm trying to understand how does the database perform search under such encryption scheme, since, in my understanding, performing encryption such as AES encryption requires the use of random IV, rendering query with where clause to be impossible unless a full database scan is done.
For example, such query:
select * from some_tbl where some_column = "some_plain_text"
would fail since the database no longer contains "some_plain_text" assuming some_column is encrypted. I have heard some techniques solve this issue by saving the hash on the database as well, and search is done based on the hash instead.
But it should only solve queries of exact match, but how about queries like this:
select * from some_tbl where some_column < 5
or queries that use the 'like' operator, and how about indexing?