Internal_Function with Encryption in SQL PLAN

Sometimes,the columns are decrypted as a result and decrypt functions (appears as INTERNAL_FUNCTION in the execution plan) are applied on them, which can lead to poor approximations of column selectivity, leading to improper plans. This happens mostly when the encrypted columns are using SALT to encrypt the data, but it can happen for other reasons as well, including bugs. Bug:7147087 AFTER ENABLING TDE, EXECUTION PLAN CHANGES FOR THE WORSE and it can be recognized from the following symptoms: 1. both tables participating in a join have encrypted columns. 2. there is at least a join condition with encrypted columns at both ends. 3. the second table has an index on the join column(s). 4. the INTERNAL_FUNCTION is applied to the encrypted columns in the join in the second table and the execution plan that used to be an INDEX UNIQUE SCAN on the unenecrypted columns turns into an INDEX RANGE SCAN or FULL TABLE SCAN. Scenario 2: Pushed Predicates The second known TDE performance bug is the one when the queries are using pushed predicates on encrypted columns inside explicit or implicit views and the encrypted column values are decrypted to filter out the values instead of encrypting the pushed predicates. This situation is met when: 1. external predicates are pushed into views 2. the execution plan presents predicate of the form INTERNAL_FUNCTION(column) =  ; On the other hand, INTERNAL_FUNCTION may consume more memory and cpu than normal

本文转自maclean_007 51CTO博客，原文链接:
http://blog.51cto.com/maclean/1277485