Optimization with Function-Based Indexes (201)

最新推荐文章于 2019-10-21 22:28:56 发布

转载最新推荐文章于 2019-10-21 22:28:56 发布 · 123 阅读

本文介绍了如何通过收集函数索引的统计信息来提高优化器性能。对于含有表达式的查询，优化器可以使用函数索引进行索引范围扫描，特别是在谓词选择性较低的情况下更为有效。此外，文章还讨论了如何通过解析表达式树来实现表达式的精确匹配。

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@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10599713/viewspace-982482/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10599713/viewspace-982482/