Oracle SQL列转行优化咨询：160万数据性能与0值冗余问题

针对160万条数据量的列转行需求（将id1、id2、id3转为行，同时过滤值为0的多余行），放弃字符串拼接+正则拆分的低效方案，推荐以下两种高性能实现：

方案一：UNION ALL 集合拆分 #

这是最直观的纯集合操作，完全避免字符串处理开销，性能拉满：

-- 假设原表名为your_table，other_col是需要保留的其他关联列
SELECT other_col, id1 AS id
FROM your_table
WHERE id1 != 0
UNION ALL
SELECT other_col, id2 AS id
FROM your_table
WHERE id2 != 0
UNION ALL
SELECT other_col, id3 AS id
FROM your_table
WHERE id3 != 0
-- 按需排序
ORDER BY other_col, id;

优势： #

• 无字符串拼接、正则匹配的额外开销，Oracle优化器可以高效执行
• 直接通过WHERE条件过滤值为0的行，从源头上避免多余数据生成
• 逻辑清晰，易于维护

方案二：CROSS JOIN + 集合表函数 #

用Oracle内置的集合类型实现更简洁的写法，同样是低开销的集合操作：

SELECT t.other_col, col.column_value AS id
FROM your_table t
-- 将三个id列转为数字集合
CROSS JOIN TABLE(SYS.ODCINUMBERLIST(t.id1, t.id2, t.id3)) col
-- 过滤0和可能的NULL值
WHERE col.column_value != 0
  AND col.column_value IS NOT NULL
ORDER BY t.other_col, id;

优势： #

• 仅扫描原表一次，代码更紧凑
• 集合表函数是Oracle原生支持的高效操作，性能优于字符串拆分方案

为什么之前的方案性能极差？ #

字符串拼接（id1||','||id2||','||id3）和regexp_substr拆分属于字符串密集型操作，160万条数据下会产生巨量的内存和CPU开销：

1. 每条数据都要执行字符串拼接，生成新的字符串对象
2. 正则表达式拆分需要逐字符解析字符串，效率远低于集合操作
3. 还要额外处理值为0的行，进一步增加计算量

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