Oracle数据库索引技术深度解析

### Oracle数据库索引技术深度解析 #### 1. 仅索引部分行 函数式索引除了能辅助使用内置函数（如UPPER、LOWER等）的查询外，还可用于选择性地对表中的部分行进行索引。B*Tree索引不会为全NULL的键创建索引项。例如，创建索引语句 `Create index I on t(a,b);` ，若某行中A和B都为NULL，索引结构中不会有该行的索引项。 以一个大表为例，表中有一个非空列 `PROCESSED_FLAG` ，其值可以为Y或N，默认值为N。新插入的行值为N表示未处理，处理后更新为Y表示已处理。若想快速检索值为N的记录，使用常规的B*Tree索引会有问题。因为表中有数百万行，几乎所有行的值都是Y，创建的索引会很大，且从N更新为Y时维护索引的成本也很高。同时，由于这是一个事务性系统，很多人会同时插入 `PROCESSED_FLAG` 为N的记录，位图索引不适合并发修改，所以也不适用。 下面是具体操作步骤： 1. 使用标准的 `BIG_TABLE` 脚本更新 `TEMPORARY` 列，将Y和N的值互换： ```sql $ sqlplus eoda/foo@PDB1 SQL> update big_table set temporary = decode(temporary,'N','Y','N'); ``` 2. 查看Y和N的比例： ```sql SQL> select temporary, cnt, round( (ratio_to_report(cnt) over ()) * 100, 2 ) rtr from (select temporary, count(*) cnt from big_table group by temporary); ``` 结果如下： | T | CNT | RTR | | - | ---------- | ---------- | | Y | 998728 | 99.87 | | N | 1272 | .13 | 3. 创建常规索引并分析： ```sql SQL> create index processed_flag_idx on big_table(temporary); SQL> analyze index processed_flag_idx validate structure; SQL> select name, btree_space, lf_rows, height from index_stats; ``` 结果显示索引有1,000,000个条目，占用近14MB空间，高度为3。 4. 使用函数式索引只索引值为N的记录： ```sql SQL> drop index processed_flag_idx; SQL> create index processed_flag_idx on big_table( case temporary when 'N' then 'N' end ); SQL> analyze index processed_flag_idx validate structure; SQL> select name, btree_space, lf_rows, height from index_stats; ``` 新索引仅32KB，高度为2，使用该索引进行检索时I/O操作会减少。 #### 2. 实现选择性唯一性 函数式索引还可用于实施某些复杂的约束。例如，有一个包含版本信息的表，如项目表，项目有两种状态：ACTIVE或INACTIVE。需要实施规则“活跃项目的名称必须唯一，非活跃项目则不需要”。 开发者通常的做法是运行查询查看是否有活跃的同名项目，若没有则创建。但在多用户环境中，这种简单实现方式无法工作。若两人同时尝试创建新的活跃项目X，都会成功。可以使用函数式索引让数据库来处理这个问题。 操作步骤如下： ```sql Create unique index active_projects_must_be_unique On projects ( case when status = 'ACTIVE' then name end ); ``` 当状态列为ACTIVE时，NAME列将被唯一索引，任何创建同名活跃项目的尝试都会被检测到，且不会影响表的并发访问。 #### 3. 关于ORA - 01743的注意事项 在创建基于 `TO_DATE` 内置函数的函数式索引时，有时会失败。例如： ```sql $ sqlplus eoda/foo@PDB1 SQL> create table t ( year varchar2(4) ); SQL> create index t_idx on t( to_date(year,'YYYY') ); ``` 会报错 `ORA - 01743: only pure functions can be indexed` 。但有时使用 `TO_DATE` 创建函数式索引是可行的，如： ```sql SQL ```