Oracle 执行计划中的 ACCESS 和 FILTER 详解

Oracle 执行计划中的 ACCESS 和 FILTER 详解

在 Oracle 执行计划中，ACCESS 和 FILTER 是两个关键的操作类型，它们描述了 Oracle 如何检索和处理数据。理解这两个概念对于 SQL 性能调优至关重要。

ACCESS（访问）

ACCESS 表示 Oracle 通过索引或直接访问表的方式来获取数据。

特点

1. 索引访问：通常通过索引快速定位数据
2. 高效检索：直接访问所需数据块，减少I/O
3. 访问路径：包括索引唯一扫描(INDEX UNIQUE SCAN)、索引范围扫描(INDEX RANGE SCAN)等

常见 ACCESS 操作类型

ACCESS 示例

-- 索引唯一扫描示例
EXPLAIN PLAN FOR
SELECT * FROM employees WHERE employee_id = 100;

-- 执行计划中会出现:
-- | Id | Operation                   | Name          |
-- |----|-----------------------------|---------------|
-- |  0 | SELECT STATEMENT            |               |
-- |  1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES     |
-- |  2 |   INDEX UNIQUE SCAN         | EMP_ID_PK     |

FILTER（过滤）

FILTER 表示 Oracle 对已获取的数据应用额外的过滤条件。

特点

1. 后置过滤：在获取数据后应用条件
2. 性能影响：可能导致处理更多数据
3. 常见场景：无法使用索引的条件、函数条件等

常见 FILTER 操作场景

1. 对索引列应用函数：

   WHERE UPPER(last_name) = 'SMITH'
   

2. 使用不等于(!=或<>)操作：

   WHERE department_id != 10
   

3. 使用OR条件：

   WHERE department_id = 10 OR salary > 5000
   

4. 使用LIKE以通配符开头：

   WHERE last_name LIKE '%SMITH%'
   

FILTER 示例

-- 过滤条件示例
EXPLAIN PLAN FOR
SELECT * FROM employees WHERE salary > 5000 AND UPPER(last_name) = 'SMITH';

-- 执行计划中可能出现:
-- | Id | Operation         | Name      |
-- |----|-------------------|-----------|
-- |  0 | SELECT STATEMENT  |           |
-- |* 1 |  TABLE ACCESS FULL| EMPLOYEES |
--
-- Predicate Information:
-- 1 - filter("SALARY">5000 AND UPPER("LAST_NAME")='SMITH')

ACCESS 与 FILTER 对比

性能优化建议

1. 将FILTER转为ACCESS：

   • 为常用查询条件创建合适索引
   • 重写SQL避免对索引列使用函数

2. 复合索引策略：

   -- 创建复合索引支持多列查询
   CREATE INDEX emp_name_salary_idx ON employees(last_name, salary);
   

3. 避免全表扫描：

   • 确保查询能使用索引
   • 使用INDEX提示强制使用索引

4. 函数索引：

   -- 为函数条件创建函数索引
   CREATE INDEX emp_upper_name_idx ON employees(UPPER(last_name));
   

5. 统计信息更新：

   -- 确保统计信息准确
   EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES');
   

实际案例分析

案例1：将FILTER转为ACCESS

问题SQL：

SELECT * FROM orders WHERE TO_CHAR(order_date, 'YYYY-MM') = '2023-01';

执行计划：

TABLE ACCESS FULL ORDERS
  FILTER: TO_CHAR(order_date, 'YYYY-MM') = '2023-01'

优化方案：

-- 方案1: 使用范围查询
SELECT * FROM orders 
WHERE order_date >= TO_DATE('2023-01-01', 'YYYY-MM-DD')
AND order_date < TO_DATE('2023-02-01', 'YYYY-MM-DD');

-- 方案2: 创建函数索引
CREATE INDEX orders_ym_idx ON orders(TO_CHAR(order_date, 'YYYY-MM'));

案例2：复合索引优化

问题SQL：

SELECT * FROM employees 
WHERE department_id = 10 
AND salary > 5000;

执行计划：

TABLE ACCESS FULL EMPLOYEES
  FILTER: department_id = 10 AND salary > 5000

优化方案：

-- 创建复合索引
CREATE INDEX emp_dept_sal_idx ON employees(department_id, salary);

优化后执行计划将显示使用索引范围扫描(INDEX RANGE SCAN)。

总结

1. ACCESS 表示数据检索方式，FILTER 表示数据获取后的过滤
2. 优化目标是将尽可能多的 FILTER 条件转为 ACCESS 条件
3. 通过创建合适索引、重写SQL语句可以减少FILTER操作
4. 使用执行计划工具定期检查SQL性能，识别不必要的FILTER操作