Oracle数据库中索引的常见执行计划是什么

发布时间：2021-12-02 11:18:10 作者：柒染
来源：亿速云阅读：360

# Oracle数据库中索引的常见执行计划是什么

## 引言

在Oracle数据库性能优化中，索引是提升查询效率的核心手段之一。理解索引的执行计划（Execution Plan）对于数据库开发人员和DBA至关重要。执行计划是Oracle优化器（Optimizer）生成的用于执行SQL语句的详细步骤，它决定了数据访问路径和操作顺序。本文将深入探讨Oracle数据库中与索引相关的常见执行计划，包括其工作原理、适用场景以及如何通过执行计划诊断性能问题。

---

## 一、索引执行计划基础

### 1. 什么是执行计划
执行计划是Oracle优化器根据SQL语句、对象统计信息和系统配置生成的执行路径。它展示了：
- 数据访问方式（全表扫描、索引扫描等）
- 表连接方法（嵌套循环、哈希连接、排序合并等）
- 操作顺序和成本估算

### 2. 索引在执行计划中的作用
索引通过减少I/O操作来加速查询，常见的索引类型包括：
- B树索引（默认）
- 位图索引
- 函数索引
- 反向键索引等

---

## 二、索引相关的常见执行计划

### 1. INDEX UNIQUE SCAN
**场景**：通过唯一索引（如主键）精确查找单行数据  
**特点**：
- 效率最高（O(1)时间复杂度）
- 仅返回0或1行记录
```sql
-- 示例：主键查询
EXPLN PLAN FOR SELECT * FROM employees WHERE employee_id = 100;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- 执行计划输出示例：
-- -------------------------------------------------------------------
-- | Id | Operation         | Name       | Rows | Bytes | Cost |
-- |  0 | SELECT STATEMENT  |            |    1 |    50 |    1 |
-- |* 1 |  INDEX UNIQUE SCAN| EMP_ID_PK  |    1 |    50 |    1 |
-- -------------------------------------------------------------------

2. INDEX RANGE SCAN

场景：非唯一索引的等值查询或范围查询
特点： - 可能返回多行数据 - 索引列需满足最左前缀原则

-- 示例：范围查询
EXPLN PLAN FOR 
SELECT * FROM orders WHERE order_date BETWEEN TO_DATE('2023-01-01','YYYY-MM-DD') 
                                      AND TO_DATE('2023-12-31','YYYY-MM-DD');
                                      
-- 执行计划输出可能显示：
-- ---------------------------------------------------------------
-- | Id | Operation        | Name          | Rows | Cost |
-- |  0 | SELECT STATEMENT |               | 1000 |   50 |
-- |* 1 |  INDEX RANGE SCAN| IDX_ORDER_DATE| 1000 |   50 |
-- ---------------------------------------------------------------

3. INDEX FULL SCAN

场景：需要读取整个索引的所有条目
典型用例： - 查询列全部包含在索引中（覆盖索引） - ORDER BY子句与索引顺序一致

-- 示例：覆盖索引查询
EXPLN PLAN FOR 
SELECT employee_id, last_name FROM employees ORDER BY employee_id;

-- 执行计划可能显示：
-- ---------------------------------------------------
-- | Id | Operation       | Name       | Cost |
-- |  0 | SELECT STATEMENT|            |   10 |
-- |  1 |  INDEX FULL SCAN| EMP_ID_NAME|   10 |
-- ---------------------------------------------------

4. INDEX FAST FULL SCAN

场景：类似全索引扫描，但使用多块读取（Multiblock Read）
特点： - 无序读取（不保证顺序） - 通常比INDEX FULL SCAN更快但不可用于排序

-- 示例：统计查询
EXPLN PLAN FOR 
SELECT COUNT(*) FROM employees;

-- 若存在合适的索引，可能显示：
-- ------------------------------------------------------
-- | Id | Operation          | Name       | Cost |
-- |  0 | SELECT STATEMENT   |            |    5 |
-- |  1 |  INDEX FAST FULL SCAN | EMP_ID_PK |    5 |
-- ------------------------------------------------------

5. INDEX SKIP SCAN

场景：复合索引的非前导列查询
原理： - Oracle将查询拆分为多个子查询（对前导列的不同值） - 9i版本后引入的特性

-- 示例：复合索引(gender, email)中查询email
EXPLN PLAN FOR 
SELECT * FROM customers WHERE email = 'user@example.com';

-- 执行计划可能显示：
-- -------------------------------------------------------
-- | Id | Operation        | Name          | Cost |
-- |  0 | SELECT STATEMENT |               |   20 |
-- |  1 |  INDEX SKIP SCAN | IDX_GEN_EML |   20 |
-- -------------------------------------------------------

三、特殊索引的执行计划

1. 位图索引执行计划

BITMAP INDEX SINGLE VALUE / RANGE SCAN
适用场景： - 低基数列（如性别、状态字段） - 数据仓库环境

-- 示例：位图索引查询
EXPLN PLAN FOR 
SELECT * FROM sales WHERE channel_id = 'WEB';

-- 输出可能包含：
-- -------------------------------------------------------
-- | Id | Operation              | Name          | Cost |
-- |  0 | SELECT STATEMENT       |               |   15 |
-- |  1 |  BITMAP CONVERSION TO ROWID|             |   15 |
-- |* 2 |   BITMAP INDEX SINGLE VALUE| IDX_CHANNEL |    1 |
-- -------------------------------------------------------

2. 函数索引执行计划

FUNCTION-BASED INDEX SCAN
当查询条件包含函数时：

-- 创建函数索引
CREATE INDEX idx_upper_name ON employees(UPPER(last_name));

-- 查询使用函数索引
EXPLN PLAN FOR 
SELECT * FROM employees WHERE UPPER(last_name) = 'SMITH';

-- 执行计划将显示函数索引扫描

四、执行计划诊断与优化

1. 何时索引未被使用

常见原因包括： - 统计信息过时（需执行ANALYZE TABLE或DBMS_STATS） - 索引列被函数修改（如WHERE UPPER(name) = 'ABC'但无函数索引） - 使用了!=、NOT IN等操作符

2. 强制使用索引提示

-- 使用INDEX提示
SELECT /*+ INDEX(emp emp_name_idx) */ * FROM employees emp
WHERE department_id = 10;

3. 关键视图

EXPLN PLAN：生成执行计划
V$SQL_PLAN：查看缓存中的执行计划
AWR/DBA_HIST_SQL_PLAN：历史执行计划分析

五、总结

Oracle索引的执行计划反映了优化器对数据访问路径的选择。理解不同索引扫描方式的特性和适用场景，可以帮助我们： 1. 设计更高效的索引策略 2. 快速定位性能瓶颈 3. 编写优化器友好的SQL语句

实际工作中应结合DBMS_XPLAN、SQL Trace等工具进行综合分析，并定期维护统计信息以保证执行计划的准确性。

最佳实践建议：
- 为高频查询的关键列创建合适的索引
- 监控执行计划的变化（特别是Oracle版本升级后）
- 避免过度索引导致DML性能下降 “`

注：本文约1950字，包含代码示例和执行计划示意图。实际应用时需根据具体数据库环境和数据特征进行调整。