Oracle 索引与 Filter（类 Nested Loop）优化：生产案例 + 实操 Demo

一、核心概念：FILTER vs Nested Loop

二、生产案例：客户冻结状态查询优化（FILTER + 索引）

1. 业务场景

select a.clientno,
       a.customername,
       case
         when exists (select 1
                 from cdk_freezesdeductsmsg b
                where a.clientno = b.customerno
                  and b.freezestatus = '1') then
          '是'
         else
          '否'
       end as frozen_state
  from cr_client a
 where a.comcode = '1001';  -- 筛选1001机构的客户，约5000行

2. 问题现象

• 执行耗时：8~10 秒（生产环境）；
• 执行计划：
  关键问题：内层 cdk_freezesdeductsmsg全表扫描（约 100 万行），外层 5000 行 → 总扫描量 = 5000×100 万 = 50 亿行，性能极差。

• -----------------------------------------------------------------------------------
  | Id  | Operation          | Name                  | Rows  | Bytes | Cost (%CPU)|
  -----------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT   |                       |  5000 |   195K|  8900   (1)|
  |   1 |  FILTER            |                       |       |       |            |
  |*  2 |   TABLE ACCESS FULL| CR_CLIENT             |  5000 |   195K|   100   (1)|
  |*  3 |   TABLE ACCESS FULL| CDK_FREEZESDEDUCTSMSG |     1 |    20 |  1780   (1)|
  ----------------------------------------------------------------------------------

3. 优化思路

4. 优化效果

• 执行耗时：0.1 秒（性能提升 80 倍）；
• 执行计划：内层从全表扫描变为索引范围扫描。

三、实操 Demo：完整复现 + 验证

步骤 1：创建测试表（模拟生产数据）

-- 1. 客户表（外层表，模拟5000行数据）
CREATE TABLE cr_client (
  clientno VARCHAR2(20) PRIMARY KEY,  -- 客户编号
  customername VARCHAR2(50),          -- 客户名称
  comcode VARCHAR2(4)                 -- 机构编码
  ) TABLESPACE USERS;
  -- 插入测试数据（1001机构5000行）
  INSERT INTO cr_client 
  SELECT 
  'C'||LPAD(LEVEL, 10, '0'),  -- clientno: C0000000001~C000005000
  '客户'||LEVEL,
  '1001'FROM DUALCONNECT BY LEVEL <= 5000;
  COMMIT;
  -- 2. 冻结记录表（内层表，模拟100万行数据）
  CREATE TABLE cdk_freezesdeductsmsg (
  id NUMBER PRIMARY KEY,
  customerno VARCHAR2(20),    -- 关联客户表clientno
  freezestatus VARCHAR2(1),   -- 冻结状态：1=冻结，0=解冻
  freezetime DATE             -- 冻结时间
  ) TABLESPACE USERS;
  -- 插入测试数据（100万行，其中1000行是1001机构客户的冻结记录）
  INSERT INTO cdk_freezesdeductsmsg
  SELECT 
  LEVEL,
  'C'||LPAD(MOD(LEVEL, 5000)+1, 10, '0'),  -- 关联客户表
  CASE WHEN LEVEL <= 1000 THEN '1' ELSE '0' END,  -- 前1000行冻结
  SYSDATE - LEVEL/10000FROM DUALCONNECT BY LEVEL <= 1000000;
  COMMIT;
  -- 收集统计信息（让优化器识别数据分布）
  EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'CR_CLIENT', CASCADE=>TRUE);
  EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'CDK_FREEZESDEDUCTSMSG', CASCADE=>TRUE);

步骤 2：执行原 SQL，查看执行计划（慢查询）

-- 开启执行计划显示
SET AUTOTRACE ON EXPLAIN;
-- 原SQL（无索引）
SELECT 
  a.clientno, a.customername,
  CASE WHEN EXISTS (
    SELECT 1 FROM cdk_freezesdeductsmsg b 
    WHERE a.clientno = b.customerno AND b.freezestatus = '1'
  ) THEN '是' ELSE '否' END AS frozen_state FROM cr_client aWHERE a.comcode = '1001';
-- 关闭执行计划
SET AUTOTRACE OFF;

原执行计划（关键部分）：

-----------------------------------------------------------------------------------
| Id  | Operation          | Name                  | Rows  | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                       |  5000 |   195K|  8900   (1)|
|   1 |  FILTER            |                       |       |       |            |
|*  2 |   TABLE ACCESS FULL| CR_CLIENT             |  5000 |   195K|   100   (1)|
|*  3 |   TABLE ACCESS FULL| CDK_FREEZESDEDUCTSMSG |     1 |    20 |  1780   (1)|
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("A"."COMCODE"='1001')
   3 - filter("B"."CUSTOMERNO"=:B1 AND "B"."FREEZESTATUS"='1')

• FILTER操作：外层 CR_CLIENT全表扫描（5000 行），内层 CDK_FREEZESDEDUCTSMSG全表扫描（100 万行）；
• 总 Cost：8900，耗时约 8 秒（视服务器性能）。

步骤 3：创建索引优化（核心）

-- 优化：创建内层过滤字段的复合索引（选择性高的字段在前）
CREATE INDEX IDX_CDK_FREEZES_CUST_STATUS 
ON cdk_freezesdeductsmsg(CUSTOMERNO, FREEZESTATUS)TABLESPACE USERS;
-- 重新收集统计信息EXEC 
DBMS_STATS.GATHER_INDEX_STATS(USER, 'IDX_CDK_FREEZES_CUST_STATUS');

步骤 4：执行优化后 SQL，验证效果

SET AUTOTRACE ON EXPLAIN;
-- 同一条SQL（索引自动生效）
SELECT 
  a.clientno, a.customername,
  CASE WHEN EXISTS (
    SELECT 1 FROM cdk_freezesdeductsmsg b 
    WHERE a.clientno = b.customerno AND b.freezestatus = '1'
  ) THEN '是' ELSE '否' END AS frozen_state FROM cr_client a WHERE a.comcode = '1001';
SET AUTOTRACE OFF;

优化后执行计划：

-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                          | Rows  | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                               |  5000 |   195K|   150   (1)|
|   1 |  FILTER                      |                               |       |       |            |
|*  2 |   TABLE ACCESS FULL          | CR_CLIENT                     |  5000 |   195K|   100   (1)|
|*  3 |   INDEX RANGE SCAN           | IDX_CDK_FREEZES_CUST_STATUS   |     1 |    20 |     1   (0)|
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("A"."COMCODE"='1001')
   3 - access("B"."CUSTOMERNO"=:B1 AND "B"."FREEZESTATUS"='1')

• 核心变化：内层从 TABLE ACCESS FULL变为 INDEX RANGE SCAN（Cost 从 1780→1）；
• 总 Cost：150，耗时约 0.1 秒（性能提升 80 倍）；
• 关键： access（索引命中）代替 filter（全表过滤），仅扫描匹配的索引行。

四、Nested Loop + 索引优化案例（类 FILTER 场景）

1. 业务场景

SELECT a.clientno, b.orderid, b.amount
FROM cr_client a JOIN aml.orders b ON a.clientno = b.clientno
WHERE a.comcode = '1001' AND b.orderstatus = '01';  -- 01=已支付

2. 优化前问题

• 执行计划：Nested Loop + 内层订单表全表扫描；
• 耗时：5 秒（订单表 100 万行）。

3. 索引优化

CREATE INDEX IDX_ORDERS_CLIENT_STATUS ON aml.orders(CLIENTNO, ORDERSTATUS);

4. 优化后效果

• 执行计划：Nested Loop + 内层索引范围扫描；
• 耗时：0.05 秒（Cost 从 5000→50）。

五、生产环境关键优化原则（FILTER/Nested Loop + 索引）

1. 索引设计原则

• 内层优先：FILTER/Nested Loop 的性能瓶颈在内层，优先给内层的过滤 / 关联字段建索引；
• 覆盖索引：内层若只需 “存在性验证”（如 EXISTS），建 覆盖索引（包含所有过滤字段），避免回表；
• 字段顺序：复合索引中， 选择性高的字段在前（如 freezestatus（2 个值）< customerno（5000 个值），所以 customerno在前）。

2. 结果集控制

• 外层结果集必须小（<1000 行）：若外层> 1 万行，FILTER/Nested Loop 会比 Hash Join 慢，需先通过索引缩小外层结果集；
• 示例：给外层 cr_client.comcode建索引，缩小外层扫描范围：

• CREATE INDEX IDX_CR_CLIENT_COMCODE ON cr_client(COMCODE)

3. 避免的坑

• 内层索引失效：若内层过滤条件含函数（如 INSTR4(customerno, 'C')=1），需建函数索引：

• CREATE INDEX IDX_CDK_FREEZES_CUST_FUNC ON cdk_freezesdeductsmsg(INSTR4(CUSTOMERNO, 'C'), FREEZESTATUS)

• 聚簇因子过高：若内层索引回表（如需要 freezetime字段），需确保索引聚簇因子低（参考之前聚簇因子优化）。

六、生产问题排查：FILTER/Nested Loop 性能差

1. 定位慢查询

-- 查看正在执行的FILTER/Nested Loop慢查询SELECT 
  sql_id,
  sql_text,
  elapsed_time/1000000 AS elapsed_sec,
  operation,
  optionsFROM v$sql_planJOIN v$sql ON v$sql_plan.sql_id = v$sql.sql_idWHERE operation IN ('FILTER', 'NESTED LOOPS')
  AND elapsed_time/1000000 > 5;  -- 耗时>5秒

2. 验证索引是否生效

-- 查看索引使用情况
SELECT 
  index_name,
  table_name,
  usage_count,
  last_usedFROM v$object_usageWHERE table_name = 'CDK_FREEZESDEDUCTSMSG';

七、总结

1. 核心逻辑：FILTER/Nested Loop 的性能依赖 “外层小结果集 + 内层索引高效扫描”，索引是优化的核心；
2. Demo 验证：给内层过滤字段建覆盖索引，可将 FILTER 操作的耗时从 8 秒降至 0.1 秒（80 倍提升）；
3. 生产原则：内层优先建覆盖索引、控制外层结果集大小、避免索引失效（函数 / 隐式转换）。