Oracle 11g单字段存储多值场景下如何优化查询性能

先直接回答你最关心的问题：给REGEXP_SUBSTR建函数索引的方案完全不可行。
原因很简单：REGEXP_SUBSTR拆分拼接串时必须指定要提取第N个位置的值，你没法预判目标值会出现在第几个位置，就算你建10个函数索引分别提取前10个位置的值，也覆盖不了变长多值的场景，维护成本极高，没有实用价值。

Oracle 11g环境下针对单字段多值存储的查询场景，按照推荐优先级从高到低给你落地方案：

1. 首选：范式改造拆关联表（性能、可维护性最优） #

单字段存拼接多值本身违反数据库第一范式，长期来看不管是查询性能、数据校验还是后续扩展都会有很多问题，优先建议改造：

• 保留原主表结构，新增一张关联子表，字段为主表主键ID、单值内容
• 把原column1字段里用||拼接的所有值拆成单独行存入关联子表
• 给关联子表的单值内容字段建普通B树索引

查询时直接通过主表主键关联子表做等值匹配，走索引查询性能是所有方案里最高的，同时支持多值统计、范围查询、关联其他表等复杂需求，没有任何兼容坑。

2. 不拆表兼容方案：Oracle Text 文本索引 #

如果因为业务硬约束没法改表结构，不要用LIKE全模糊匹配走全表扫，用Oracle 11g原生自带的Oracle Text组件建CONTEXT索引，性能可以达到普通B树索引的级别：

1. 先配置分词规则，让索引把||识别成分隔符，把值里的-识别成值的一部分不拆词：

BEGIN
  ctx_ddl.create_preference('multi_value_lexer', 'BASIC_LEXER');
  ctx_ddl.set_attribute('multi_value_lexer', 'printjoins', '-');
  ctx_ddl.set_attribute('multi_value_lexer', 'numjoins', '-');
END;
/

2. 建CONTEXT索引，配置提交即同步，避免新写入数据查不到：

CREATE INDEX idx_tab_column1 ON your_table(column1)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS('LEXER multi_value_lexer SYNC (ON COMMIT)');

3. 查询时用CONTAINS语法，直接走索引：

SELECT * FROM your_table WHERE CONTAINS(column1, '310-G01-000-000-000') > 0;

这个方案会自动按||拆分完整值做索引，不会出现子串误匹配的问题。

3. 对标PG数组的原生方案：嵌套表类型 #

Oracle 11g本身支持集合类型，和PostgreSQL的数组能力对应，如果你可以修改字段类型，可以用嵌套表实现：

1. 先定义字符串集合类型：

CREATE OR REPLACE TYPE t_col1_arr AS TABLE OF VARCHAR2(100);
/

2. 修改原表字段为嵌套表类型，创建嵌套表存储段：

ALTER TABLE your_table MODIFY column1 t_col1_arr
  NESTED TABLE column1 STORE AS nt_col1_storage RETURN AS VALUE;

3. 给嵌套表的值列建索引：

CREATE INDEX idx_nt_col1_val ON nt_col1_storage(column_value);

4. 查询时用MEMBER OF语法，直接走索引：

SELECT * FROM your_table 
WHERE '310-G01-000-000-000' MEMBER OF column1;

这个方案不需要额外拆关联子表，但是需要修改现有字段的写入逻辑，不能再直接传拼接好的字符串，需要传入集合类型参数。

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