Known Performance Issues When Using TDE and Indexes on the Encrypted Columns
(Doc ID 728292.1)
Last updated on JULY 22, 2021
Advanced Networking Option - Version 10.2.0 to 11.1 Information in this document applies to any platform.
This document describes some particular performance problems that can happen with encrypted columns, due to two optimizer bugs we were able to identify and fix.
To understand and identify the issue please consider the following background information on TDE.
With Transparent Data Encryption (TDE) the server creates a key for each table that uses encrypted columns. The table key is stored encrypted with the database master key in the data dictionary. This master key is stored in a PKCS12 wallet, outside the database or, from 11g onwards, optionally into a Hardware Security Module (HSM). For the database to use TDE, a wallet must exist either as file ewallet.p12 or inside the HSM. The procedure below uses a basic generated key. The master encryption key could also be from a PKI pair.
Beware that because of this there are a number of normal restrictions when using indexes on encrypted columns, eg bitmap indexes are not supported. Please check for the complete details on these restrictions.:
<Note.454980.1> Ext/Rev Best Practices for having indexes on encrypted columns using TDE in 10gR2
The conclusion is that, in order to use an index including at least one encrypted column, the predicate should be an equality predicate and the index access should be INDEX UNIQUE SCAN. Other forms of predicate would result in FULL TABLE SCAN, because the index cannot be used with INDEX RANGE SCANs.
The above also means that when resolving queries that involve (indexed) columns that are encrypted, to be of any use when comparing the predicate conditions, they must first be decrypted, the goal of the optimizer is to do as little of these operations as possible. The following scenario's describe problems where the optimizer fails to resolve some queries involving encrypted columns into an optimal execution plan.
Scenario 1 : Encrypted columns on joined tables.
For example doing a join between 2 tables on encrypted columns, the values must be first decrypted in order to apply the join predicate supposing the following scenario:
As seen, the SORT that would have been expected because of the ORDER BY clause is now executed. The optimizer can no longer eliminate the sort by accessing the <index1> index in the ORDER BY requested order. This is because now the index data includes encrypted data and the ORDER BY order no longer corresponds to the <index1> order. The optimizer feature can no longer be applied and the SORT ORDER BY operation becomes mandatory.
To view full details, sign in with your My Oracle Support account.
Don't have a My Oracle Support account? Click to get started!