Oracle数据库索引的使用与常见问题解析

### Oracle数据库索引的使用与常见问题解析 #### 同一列组合上的多个索引 在Oracle Database 12c之前，不能在一个表上为完全相同的列组合定义多个索引。例如： ```sql EODA@ORA11GR2> create table t(x int); Table created. EODA@ORA11GR2> create index ti on t(x); Index created. EODA@ORA11GR2> create bitmap index tb on t(x) invisible; ERROR at line 1: ORA-01408: such column list already indexed ``` 从12c开始，可以在同一组列上定义多个索引，但前提是这些索引在物理上不同，例如一个是B*Tree索引，另一个是位图索引。并且，对于表中相同的列组合，只能有一个可见索引。在Oracle 12c数据库中，之前的`CREATE INDEX`语句可以正常工作： ```sql EODA@ORA12CR1> create table t(x int); Table created. EODA@ORA12CR1> create index ti on t(x); Index created EODA@ORA12CR1> create bitmap index tb on t(x) invisible; Index created ``` 为什么要在同一组列上定义两个索引呢？假设最初为数据仓库星型模式的事实表外键列创建了所有B*Tree索引，后来通过测试发现位图索引对于应用于星型模式的查询类型性能更好。因此，希望尽可能无缝地转换为位图索引。可以先将位图索引创建为不可见的，准备好后，删除B*Tree索引，然后将位图索引改为可见。 #### 扩展列的索引 随着Oracle 12c的推出，`VARCHAR2`、`NVARCHAR2`和`RAW`数据类型现在可以配置为存储多达32,767字节的信息（之前，`VARCHAR2`和`NVARCHAR2`的限制是4,000字节，`RAW`的限制是2000字节）。 先创建一个包含扩展列的表，然后尝试在该列上创建常规的B*Tree索引： ```sql EODA@O12CE> create table t(x varchar2(32767)); Table created. EODA@O12CE> create index ti on t(x); create index ti on t(x) * ERROR at line 1: ORA-01450: maximum key length (6398) exceeded ``` 出现这个错误是因为Oracle对索引键施加了最大长度限制，大约是块大小的四分之三（此示例中数据库的块大小为8K）。即使该索引中还没有任何条目，Oracle知道对于一个可以包含多达32,767字节的列，索引键可能会大于6,398字节，因此不允许在这种情况下创建索引。 但这并不意味着不能对扩展列进行索引，而是需要使用将索引键长度限制为小于6,398字节的技术。有以下几种选择： - 创建基于`SUBSTR`或`STANDARD_HASH`函数的虚拟列，然后在虚拟列上创建索引。 - 使用`SUBSTR`或`STANDARD_HASH`函数创建基于函数的索引。 - 创建基于更大块大小的表空间，例如16K块大小将允许索引键大小约为12,000字节。但如果需要12,000字节的索引键，可能操作有误，需要重新考虑，这种方法暂不探讨。 ##### 虚拟列解决方案 思路是先在扩展列上应用一个SQL函数创建一个虚拟列，该函数返回一个小于6,398字节的值。然后可以对该虚拟列进行索引，这为对扩展列发出查询时提供了更好的性能机制。 操作步骤如下： 1. 创建一个包含扩展列的表： ```sql EODA@O12CE> create table t(x varchar2(32767)); Table created. ``` 2. 向表中插入一些测试数据： ```sql EODA@O12CE> insert into t select to_char(level)|| rpad('abc',10000,'xyz') 2 from dual connect by level < 1001 3 union 4 select to_char(level) 5 from dual connect by level < 1001; 2000 rows created. ``` 3. 创建一个基于扩展列子字符串的虚拟列： ```sql EODA@O12CE> alter table t add (xv as (substr(x,1,10))); Table altered. ``` 4. 在虚拟列上创建索引并收集统计信息： ```sql EODA@O12CE> create index te on t(xv); Index created. EODA@O12CE> exec dbms_stats.gather_table_stats(user,'T'); PL/SQL procedure successfully completed. ``` 5. 查询虚拟列时，优化器可以在`WHERE`子句的相等和范围谓词中利用该索引： ```sql EODA@O12CE> set autotrace traceonly explain EODA@O12CE> select count(*) from t where x = '800'; --------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 5011 | 2 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 5011 | | | |* 2 | TABLE ACCESS BY INDEX ROWID BATCHED| T | 1 | 5011 | 2 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | TE | 1 | | 1 (0)| 00:00:01 | --------------------------------------------------------------------------------------------- ``` 6. 优化器也可以在范围类型的搜索中使用这种类型的索引： ```sql EODA@O12CE> select count(*) from t where x >'800' and x<'900'; --------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 5011 | 4 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 5011 | | | |* 2 | TABLE ACCESS BY INDEX ROWID BATCHED| T | 239 | 1169K| 4 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | TE | 241 | | 2 (0)| 00:00:01 | --------------------------------------------------------------------------------------------- ``` 也可以基于`STANDARD_HASH`函数创建虚拟列。`STANDARD_HASH`函数可以应用于长字符串，并返回一个远小于6,398字节的相当唯一的`RAW`值。 操作步骤如下： 1. 假设使用与之前`SUBSTR`示例相同的表和种子数据，添加一个基于`STANDARD_HASH`的虚拟列： ```sql EODA@O12CE> alter table t add (xv as (standard_hash(x))); Table altered. ``` 2. 创建索引并生成统计信息： ```sql EODA@O12CE> create index te on t(xv); Index created. EODA@O12CE> exec dbms_stats.gather_table_stats(user,'T'); PL/SQL procedure successfully completed. ``` 3. `STANDARD_HASH`在`WHERE`子句中使用相等谓词时效果很好： ```sql EODA@O12CE> set autotrace traceonly explain EODA@O12CE> select count(*) from t where x='300'; ```