Optimization with Function-Based Indexes (201)

You must gather statistics about function-based indexes for the optimizer. Otherwise,
the indexes cannot be used to process SQL statements.

The optimizer can use an index range scan on a function-based index for queries with
expressions in WHERE clause. For example, in this query:
SELECT * FROM t WHERE a + b < 10;

the optimizer can use index range scan if an index is built on a+b. The range scan
access path is especially beneficial when the predicate (WHERE clause) has low
selectivity. In addition, the optimizer can estimate the selectivity of predicates
involving expressions more accurately if the expressions are materialized in a
function-based index.

The optimizer performs expression matching by parsing the expression in a SQL
statement and then comparing the expression trees of the statement and the
function-based index. This comparison is case-insensitive and ignores blank spaces.

函式索引的最佳化
1. 需要為最佳化器收集函式索引的統計資訊
2. 當一個查詢的 WHERE 子句中含有表示式時，最佳化器可以對函式索引進行索引範圍掃描 .
如果謂詞產生的選擇性較低，則對區間掃描極為有利。如果表示式的結果物化在函式索引內，
則更能精確的估計表示式謂詞的選擇性。
3. 最佳化器能夠將 SQL 語句及函式索引中的表示式解析為表示式樹並進行比較，從而實現表示式匹配。
這個比較過程是大小寫無關的 ，並將忽略空格

[@more@]