1. 统计信息收集

统计信息是 CBO（基于代价的优化器）进行基数估算的基础。优化器依赖统计信息估算行数、数据分布，并据此计算不同执行路径的代价。如果统计信息过时或缺失，会导致估算行数失真，优化器可能选错执行计划。

1.1 核心命令

-- 收集表统计信息
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'USER',TABNAME=>'EMP');
 
-- 收集物化视图统计
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'USER',TABNAME=>'MV_EMP_DEPT');

有两张表，一张部门表 DEPT 和一张员工表 EMP：

-- 查询表，表中有10万条数据
SELECT COUNT(*) FROM "USER".EMP;

-- 查询数据部门分布情况
SELECT DEPTID, COUNT(*) FROM "USER".EMP GROUP BY DEPTID ORDER BY DEPTID;

1.2 统计信息过期问题

--查看预估行数
EXPLAIN SELECT * FROM "USER".EMP WHERE DEPTID = 70;

SELECT COUNT(*) FROM "USER".EMP WHERE DEPTID=70;

--插入10000条数据
INSERT INTO "USER".EMP (EMPID, EMPNAME, SALARY, DEPTID, EMAIL)
SELECT 200000 + LEVEL,
       'emp_new_' || LEVEL,
       5000 + MOD(LEVEL, 20000),
       70,
       'emp' || LEVEL || '@test.com'
FROM DUAL CONNECT BY LEVEL <= 10000;
COMMIT;

--查询数据行数 有20100条数据
SELECT COUNT(*) FROM "USER".EMP WHERE DEPTID=70;

--查看执行计划
EXPLAIN SELECT * FROM "USER".EMP WHERE DEPTID = 70;

增加数据前执行计划统计的是 9974，增加数据后执行计划统计的是 10961。真实行数变大了，但 EXPLAIN 里预估行数还是之前旧的值，说明统计信息已经过期了,并没有更新。

--重新收集统计信息，再执行 EXPLAIN：
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'USER',TABNAME=>'EMP');
EXPLAIN SELECT * FROM "USER".EMP WHERE DEPTID = 70;

执行计划统计的是 19844，接近 20100。

1.3 原理与结论

a. CBO 优化器依靠系统字典里保存的统计信息做基数估算，不是实时扫描表去统计行数。
b. 当表发生大量 DML（INSERT/DELETE/UPDATE），如果不主动收集统计，字典里保存的数据分布信息不会自动更新，导致统计信息过期。
c. 统计过期的现象：EXPLAIN 预估行数和 SQL 查询得到的真实行数差距明显。
优化器拿着旧的分布数据，错误估算返回行数，进而错误评估索引、全表扫描、表连接的代价，选错执行计划。
d. 执行 DBMS_STATS.GATHER_TABLE_STATS 会重新扫描表，刷新表、列、直方图等统计信息存入系统字典；刷新后，优化器基数估算回归准确，预估行数贴近真实行数。
结论：大量新增数据后，统计信息不会自动更新，造成 CBO 低估行数（统计过期）；执行收集统计存储过程，刷新字典中的数据分布信息，基数估算恢复准确，优化器才能生成正确的执行计划。

2.索引优化

2.1 索引不一定被使用

--查看 DEPTID=70 的真实行数
SELECT COUNT(*) FROM "USER".EMP WHERE DEPTID=70;

--当前：DEPTID无索引，执行计划为CSCN全表扫描
EXPLAIN SELECT * FROM "USER".EMP WHERE DEPTID = 70;

算子为 CSCN CLUSTER SCAN，整张表全部扫描再过滤。创建索引：

CREATE INDEX IDX_EMP_DEPT ON "USER".EMP(DEPTID);

执行计划

EXPLAIN SELECT * FROM "USER".EMP WHERE DEPTID = 70;

执行计划依然是 CSCN 全表扫描，不会走索引。

EXPLAIN SELECT * FROM "USER".EMP WHERE DEPTID = 70;

EXPLAIN SELECT * FROM "USER".EMP WHERE DEPTID = 30;

DEPTID=30 行数少，优化器选择 INDEX SCAN 索引扫描 + 回表。
结论：索引不是建了就一定会走。优化器会比较代价，只有索引扫描代价更低时才选用索引。

2.2 函数包裹导致索引失效

EXPLAIN SELECT * FROM "USER".EMP WHERE MOD (DEPTID,30) = 0;
CREATE INDEX IDX_EMP_MOD_DEPT ON "USER".EMP(MOD(DEPTID,30));
EXPLAIN SELECT * FROM "USER".EMP WHERE MOD (DEPTID,30) = 0;

索引列直接等值查询：SSEK 索引查找 + BLKUP 回表，高效。
索引列包裹函数：B 树普通索引失效，变成 CSCN 全表扫描。

2.3 复合索引与最左前缀

CREATE INDEX IDX_EMP_DEPT_SAL ON "USER".EMP(DEPTID, SALARY);
--命中最左前缀 DEPTID：
EXPLAIN SELECT * FROM "USER".EMP WHERE DEPTID=30;
--跳过 DEPTID，只用 SALARY：
EXPLAIN SELECT * FROM "USER".EMP WHERE DEPTID=30 AND SALARY>6000;

2.4 索引优化小结

a. 单列索引：等值查询可以走索引；索引列上做函数、运算，普通 B 树索引失效，触发全表扫描。
b. SELECT * 会产生书签回表，大量回表会拉高代价，优化器可能放弃索引。
c. 复合索引最左原则：遵循最左前缀，从索引定义首列开始连续使用字段，索引才能生效；跳过左边字段，索引失效。
d.生效：从索引最左侧字段连续使用，如 DEPTID=30、DEPTID=30 AND SALARY>6000。
e.失效：跳过左侧首列，直接使用后面字段，仅 SALARY>6000 无法使用该复合索引。
f.注意：SQL 书写条件顺序不影响，优化器内部自动调整，由索引定义顺序决定；范围条件会截断后面字段索引有序性。