WHERE col1 IS NULL查询执行缓慢问题求助

这问题挺反常的——全表扫2500万条记录秒出，加个col1 IS NULL的过滤就耗时超6分钟，同逻辑在5000万条的表上却正常，重建表也没解决。结合你提供的执行计划和表信息，我整理几个核心排查方向：

1. 统计信息严重失真，导致优化器判断失误 #

你贴的执行计划里预估返回1行，但实际场景中col1的NULL值数量可能远不止这些？如果Oracle的统计信息没有准确记录col1的NULL值分布，优化器可能会生成看似高效但实际完全不匹配的执行路径。

• 先查col1的统计数据：

  SELECT num_nulls, density, num_distinct 
  FROM user_tab_columns 
  WHERE table_name = 'YOUR_TABLE_NAME' 
    AND column_name = 'COL1';
  

  对比num_nulls和实际业务中的NULL数量，如果差距极大，赶紧重新收集统计信息：

  EXEC DBMS_STATS.GATHER_TABLE_STATS(
      OWNNAME => 'YOUR_SCHEMA', 
      TABNAME => 'YOUR_TABLE_NAME', 
      ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE, 
      METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO', 
      CASCADE => TRUE
  );
  

2. VARCHAR2 NULL值的存储特性引发IO瓶颈 #

Oracle里VARCHAR2的NULL值不占用实际存储，但如果col1的NULL值集中在冷数据块（比如很久没被访问过，被置换到磁盘深层），而全表扫描时数据库会用多块预读（multiblock read）高效加载热数据，过滤NULL时却要逐个校验冷数据块里的每行，导致大量物理IO拖慢速度。

• 可以打开统计信息对比两次查询的IO差异：

  SET AUTOTRACE ON STATISTICS;
  -- 先跑全表扫
  SELECT * FROM YOUR_TABLE_NAME;
  -- 再跑慢查询
  SELECT * FROM YOUR_TABLE_NAME WHERE col1 IS NULL;
  

  重点看physical reads和consistent gets的数值，如果慢查询的物理读远高于全表扫，基本就是冷数据块的锅。

3. 隐藏的索引/约束在暗中拖后腿 #

虽然你说没给col1建索引，但有可能存在禁用但未删除的函数索引、虚拟列索引，或者延迟生效的约束，导致优化器尝试走低效路径，或者过滤时额外做了不必要的校验。

• 查该表的所有索引：

  SELECT index_name, index_type, status 
  FROM user_indexes 
  WHERE table_name = 'YOUR_TABLE_NAME';
  

• 查所有约束：

  SELECT constraint_name, constraint_type, status 
  FROM user_constraints 
  WHERE table_name = 'YOUR_TABLE_NAME';
  

  如果发现状态异常的索引/约束，尝试删除或重新启用后再测试。

4. 表重建不彻底，遗留存储碎片化问题 #

如果你是用CREATE TABLE ... AS SELECT * FROM old_table的方式重建表，可能继承了原表的存储碎片化；或者表所在的表空间本身IO性能拉胯（比如和那个5000万的表不在同一种存储介质上，慢表在机械盘，快表在SSD）。

• 检查表的存储碎片化：

  SELECT segment_name, blocks, empty_blocks, freelist_groups 
  FROM user_segments 
  WHERE segment_name = 'YOUR_TABLE_NAME';
  

  如果empty_blocks占比过高，可以尝试用ALTER TABLE YOUR_TABLE_NAME MOVE TABLESPACE YOUR_TBS;重新整理表段，再重建索引。

5. 混合列存储（HCS）的特殊处理逻辑 #

执行计划里的TABLE ACCESS STORAGE FULL是Oracle针对混合列存储表的操作，如果你的表用了HCS，那查询NULL值时可能需要扫描整个列的存储单元，而全表扫描是走行存储模式，两者的效率差异会被放大。

• 检查表的存储类型：

  SELECT segment_name, compression_type 
  FROM user_segments 
  WHERE segment_name = 'YOUR_TABLE_NAME';
  

  如果是QUERY HIGH或其他混合列压缩类型，可以尝试调整压缩策略，或者临时转换成行存储测试。

内容的提问来源于stack exchange，提问作者Maharajaparaman