Oracle单表中如何针对BLOB列使用Scalar Subquery实现记录去重？

报错根因 #

直接在查询中带file_data（BLOB类型）使用DISTINCT会触发ORA-00932: inconsistent datatypes报错，因为Oracle不支持对BLOB、CLOB这类大对象类型直接做哈希比较、分组聚合类的去重操作，这也是原DISTINCT方案无法落地的核心原因。

实现思路 #

利用非BLOB列的可分组特性搭建去重维度，将原本和普通列一起查询的BLOB列拆分到标量子查询中单独取值：

• 先对所有非大对象列（即原本想通过DISTINCT判断重复的字段）做GROUP BY分组，拿到所有不重复的记录维度，同时记录每一组对应行的物理定位标识
• 再通过标量子查询，根据匹配到的维度关联值、行定位标识，取出对应行的BLOB值，全程不对BLOB值做任何比较运算，完全绕开大对象的使用限制

参考实现SQL #

SELECT 
  base_res.attachsysfilename,
  base_res.file_seq,
  base_res.version,
  base_res.file_size,
  base_res.lastupddttm,
  base_res.lastupdoprid,
  -- 标量子查询匹配对应行的BLOB值
  (
    SELECT src.file_data
    FROM PS_LP_EX_FILEATTCH src
    WHERE src.attachsysfilename = base_res.attachsysfilename
      AND src.file_seq = base_res.file_seq
      AND src.version = base_res.version
      AND src.file_size = base_res.file_size
      AND src.lastupddttm = base_res.lastupddttm
      AND src.lastupdoprid = base_res.lastupdoprid
      AND src.ORA_ROWSCN = base_res.row_loc_scn
      AND ROWNUM = 1
  ) AS file_data
FROM (
  -- 先对非BLOB列做分组去重，获取每一组的行定位值
  SELECT 
    attachsysfilename,
    file_seq,
    version,
    file_size,
    lastupddttm,
    lastupdoprid,
    MIN(ORA_ROWSCN) AS row_loc_scn
  FROM PS_LP_EX_FILEATTCH
  GROUP BY
    attachsysfilename,
    file_seq,
    version,
    file_size,
    lastupddttm,
    lastupdoprid
) base_res

实现注意事项 #

• 如果表本身存在唯一主键（比如附件ID类标识字段），建议直接用主键替代ORA_ROWSCN做行定位，性能和稳定性会更好。ORA_ROWSCN是基于数据块的伪列，高并发写入场景下可能出现同块多行共用同一SCN的小概率问题，配合普通业务维度匹配加ROWNUM=1的兜底逻辑即可规避。
• 去重维度可以灵活调整：如果不需要所有非BLOB列完全一致才判定为重复，只需要修改内层子查询GROUP BY后的字段列表，匹配实际的去重规则即可。
• 该方案完全实现了原本DISTINCT的去重效果，且全程没有让BLOB列参与分组、去重的比较运算，不会触发类型不兼容的报错。

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