Oracle的索引快速全扫描 INDEX FFS的介绍和使用场景，SQL优化技巧，告别全表扫描的臃肿代价！

1. 问题描述

当所需要的数据都在索引结构中获得，并且比全表扫描(多块读)、索引范围扫(单块读)的COST要低的时候，则数据库CBO优化器则会选择索引快速全扫描;

可能发生索引快速全扫描的场景有哪些呢？

1. 基础场景：COUNT查询

1. 简单的COUNT统计
-- 场景：索引大小 < 表大小，且索引列非空
SELECT COUNT(*) FROM employees;

-- 如果employees表有主键索引或非空索引
-- 优化器会选择索引快速全扫描而不是全表扫描

2. COUNT(索引列)
-- 使用索引列进行COUNT
SELECT COUNT(employee_id) FROM employees;
SELECT COUNT(department_id) FROM employees;

-- 前提：索引列有NOT NULL约束或实际数据中无NULL值

2. 覆盖索引场景（Covering Index）

1. 查询只包含索引列
-- 复合索引 (department_id, salary)
SELECT department_id, salary FROM employees;

-- 单列索引场景
SELECT employee_id FROM employees;
SELECT email FROM employees;  -- 假设email有唯一索引

2. 包含WHERE条件但所有列都在索引中
-- 复合索引 (department_id, hire_date, salary)
SELECT department_id, hire_date 
FROM employees 
WHERE department_id = 50;

-- 即使WHERE条件不是前导列，也可能走INDEX FFS
SELECT department_id, salary 
FROM employees 
WHERE salary > 5000;

3. 聚合函数场景

1. 使用聚合函数且所有列在索引中
-- 求和、平均值等
SELECT department_id, SUM(salary) 
FROM employees 
GROUP BY department_id;

SELECT AVG(salary), MAX(hire_date) 
FROM employees 
WHERE department_id IN (10, 20, 30);

2. 分组统计查询
-- 索引包含分组列和聚合列
SELECT department_id, job_id, COUNT(*), AVG(salary)
FROM employees
GROUP BY department_id, job_id;

-- 对应的理想索引：CREATE INDEX idx_emp_dept_job_sal ON employees(department_id, job_id, salary);

4. 连接查询优化场景

1. 星型查询中的维度表访问
-- 数据仓库场景，只访问维度表的索引列
SELECT d.department_name, COUNT(*)
FROM employees e, departments d
WHERE e.department_id = d.department_id
GROUP BY d.department_name;

-- 如果departments表有(department_id, department_name)索引
-- 对departments的访问可能走INDEX FFS

2. 索引连接(Index Join)的基础
-- 多个索引包含查询所需的所有列
SELECT employee_id, department_name
FROM employees e
WHERE e.department_id IN (
    SELECT department_id FROM departments WHERE location_id = 1700
);

-- 如果两个表都有合适的覆盖索引，可能使用INDEX FFS + 哈希连接

5. 特定函数和表达式场景

1. 函数索引的快速扫描
-- 基于函数的索引
CREATE INDEX idx_emp_upper_name ON employees(UPPER(last_name));

SELECT UPPER(last_name) FROM employees;

-- 即使有WHERE条件也可以使用
SELECT UPPER(last_name) 
FROM employees 
WHERE UPPER(last_name) LIKE 'S%';

2. 虚拟列索引
-- Oracle 11g+ 虚拟列
ALTER TABLE employees ADD income AS (salary + NVL(commission_pct, 0) * salary);
CREATE INDEX idx_emp_income ON employees(income);

SELECT income FROM employees WHERE income > 10000;

6. 数据仓库和报表查询场景

1. 物化视图查询
-- 查询物化视图的基表索引
SELECT calendar_year, calendar_month, SUM(sales_amount)
FROM sales s, times t
WHERE s.time_id = t.time_id
GROUP BY calendar_year, calendar_month;

-- 如果times表有合适的索引，可能使用INDEX FFS

2. 分区表局部索引
-- 分区表的局部索引快速扫描
SELECT partition_key, COUNT(*)
FROM partitioned_table
GROUP BY partition_key;

-- 对每个分区的局部索引进行快速全扫描

7. 强制使用INDEX FFS的提示(Hint)

使用INDEX_FFS提示
-- 强制使用索引快速全扫描
SELECT /*+ INDEX_FFS(e idx_emp_dept_sal) */
       department_id, AVG(salary)
FROM employees e;

8. 优化器选择INDEX FFS的条件

根据《Oracle 11.2 SQL Tuning Guide》文档，优化器选择INDEX FFS的条件：

-- 必要条件：
索引包含所有查询列（覆盖索引）
至少一个索引列有NOT NULL约束（防止全NULL行问题）
成本低于全表扫描（基于统计信息计算）

--成本考量因素：
索引大小 vs 表大小
多块读效率（DB_FILE_MULTIBLOCK_READ_COUNT）
系统I/O能力
缓存命中率
性能特征：
 1) 使用多块读，类似全表扫描
 2) 可以并行执行
 3) 结果不保证有序
 4) 不能消除排序操作