Oracle官方文档翻译《Database Concepts 23ai》第5章-索引和索引组织表-函数索引概述

5 Indexes and Index-Organized Tables

Overview of Function-Based Indexes

函数索引概述

函数索引计算涉及一个或多个列的函数或表达式的值，并将其存储在索引中。函数索引可以是B树索引或位图索引。

被索引的函数可以是算术表达式或包含SQL函数、用户定义的PL/SQL函数、包函数或C调用的表达式。例如，一个函数可以将两列中的值相加。

• 函数索引的用途
  函数索引对于在WHERE子句中包含函数计算的语句非常高效。数据库仅在该函数被包含在查询中时才使用函数索引。当数据库处理INSERT和UPDATE语句时，仍然需要评估函数以处理该语句。

• 优化函数索引
  对于在WHERE子句中包含表达式的查询，优化器可以在一个函数索引上使用索引范围扫描。

另请参阅：

• Oracle Database Administrator’s Guide 获取如何创建函数索引的信息
• Oracle Database Development Guide 了解有关使用函数索引的更多信息
• Oracle Database SQL Language Referencefor 了解函数索引的限制和使用说明

Uses of Function-Based Indexes

函数索引的用途

函数索引对于在WHERE子句中包含函数计算的语句非常高效。数据库仅在该函数被包含在查询中时才使用函数索引。当数据库处理INSERT和UPDATE语句时，仍然需要评估函数以处理该语句。

示例 5-7：基于算术表达式的索引

例如，假设您创建了以下函数索引：

CREATE INDEX emp_total_sal_idx
ON employees (12 * salary * commission_pct, salary, commission_pct);

数据库可以在处理类似以下查询（包含部分示例输出）时使用上述索引：

SELECT employee_id, last_name, first_name,
       12 * salary * commission_pct AS "ANNUAL SAL"
FROM   employees
WHERE  (12 * salary * commission_pct) < 30000
ORDER BY "ANNUAL SAL" DESC;

| EMPLOYEE_ID | LAST_NAME | FIRST_NAME | ANNUAL SAL |
|------------ | --------- | ---------- | -----------|
|159          | Smith     | Lindsey    | 28800      |
|151          | Bernstein | David      | 28500      |
|152          | Hall      | Peter      | 27000      |
|160          | Doran     | Louise     | 27000      |
|175          | Hutton    | Alyssa     | 26400      |
|149          | Zlotkey   | Eleni      | 25200      |
|169          | Bloom     | Harrison   | 24000      |

示例 5-8：基于UPPER函数的索引

定义在SQL函数UPPER(column_name)或LOWER(column_name)的函数索引，有助于进行大小写不敏感的搜索。例如，假设employees表中的first_name列包含混合的大小写字符。您可以在hr.employees表上创建以下函数索引：

CREATE INDEX emp_fname_uppercase_idx
ON employees ( UPPER(first_name) );

emp_fname_uppercase_idx索引可以有助于以下查询：

SELECT *
FROM   employees
WHERE  UPPER(first_name) = 'AUDREY';

示例 5-9：对表中特定行进行索引

函数索引对于只对表中特定的行进行索引也是有用的。例如，sh.customers表中的cust_valid列具有值I或A。只对值为A的行进行索引，您可以编写一个函数，对于不为A的行返回空值。您可以按以下方式创建索引：

CREATE INDEX cust_valid_idx
ON customers ( CASE cust_valid WHEN 'A' THEN 'A' END );

另请参阅：

• Oracle Database Globalization Support Guide 了解有关语言索引的信息
• Oracle Database SQL Language Reference 获取有关SQL函数的更多信息

Optimization with Function-Based Indexes

优化函数索引

对于在WHERE子句中包含表达式的查询，优化器可以在一个函数索引上使用索引范围扫描。

当谓词具有高度选择性时，即选择相对较少行时，对于索引范围扫描的访问路径尤其有益。在示例5-7中，如果在表达式12 * salary * commission_pct上构建索引，那么优化器可以使用索引范围扫描。

虚拟列对于快速访问由表达式生成的数据也是有用的。例如，您可以将虚拟列annual_sal定义为12 * salary * commission_pct，并在annual_sal上创建一个函数索引。

优化器通过解析SQL语句中的表达式，然后比较语句表达式树和函数索引来执行表达式的匹配。该比较不区分大小写，并且忽略空格。

另请参阅：

• “Overview of the Optimizer”
• Oracle Database SQL Tuning Guide 获取更多关于收集统计信息的信息
• Oracle Database Administrator’s Guide 获取如何向表添加虚拟列的信息