如何查找Oracle表中可用于创建唯一索引的最少必要列？

现有思路的局限性 #

你当前的方案在大表场景下存在两个明显的缺陷：

• 针对100万到10亿行级的表，逐列执行GROUP BY统计基数的IO、CPU消耗极高，单列统计可能就要消耗数分钟到数小时，多列组合验证的成本还会指数级上升
• 仅按单列基数排序后依次组合，不一定能得到列数最少的最优解：两个高基数列可能存在强关联，组合后基数提升极小，反而可能不如「高基数+低基数但完全独立」的组合列数更少

成熟算法及优化方案 #

业内针对最小候选键（即你需要的最小唯一列集合）挖掘已经有非常成熟的落地方案，核心思路是通过剪枝和采样大幅降低计算量：

• 核心剪枝逻辑利用唯一键的反单调性：如果某列集合是唯一键，那么所有包含这个集合的更大列组合也一定是唯一键。因此你只需要按照「单列→两列组合→三列组合」的顺序验证，找到的第一个符合要求的组合就是列数最少的最优解，不需要验证更长的组合
• 采样前置校验：验证任意列组合时，先采样10%的行做校验，如果采样范围内已经存在重复，直接淘汰该组合，不需要跑全表验证，仅采样无重复的组合才做全表校验，能降低90%以上的计算消耗

Oracle专属实用技巧 #

1. 秒级获取单列基数，无需手动跑GROUP BY #

只要表的统计信息不是严重过时，直接查Oracle内置数据字典就能拿到所有列的基数、NULL值占比，不需要自己写统计语句：

SELECT COLUMN_NAME, NUM_DISTINCT, NUM_NULLS
FROM USER_TAB_COLUMNS
WHERE TABLE_NAME = 'YOUR_TABLE_NAME'
ORDER BY NUM_DISTINCT DESC;

如果统计信息太久没更新，可以跑快速采样统计，性能比逐列GROUP BY高几个数量级：

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'YOUR_USERNAME', TABNAME=>'YOUR_TABLE_NAME', ESTIMATE_PERCENT=>1);

1%的采样精度足够用来做列基数排序，误差可忽略。

2. 列组合唯一性验证优化 #

不要用全表GROUP BY统计所有分组的数量，用下面的语句，Oracle只要找到第一条重复记录就会终止扫描，不需要跑完整个表：

-- 返回结果大于0说明组合存在重复，不符合唯一性要求
SELECT 1 
FROM YOUR_TABLE_NAME
WHERE COLUMN1 IS NOT NULL AND COLUMN2 IS NOT NULL -- 唯一索引列默认不允许NULL，可按需调整过滤条件
GROUP BY COLUMN1, COLUMN2
HAVING COUNT(1) > 1
FETCH FIRST 1 ROW ONLY;

3. 前置业务校验 #

如果是业务系统的表，优先核对业务规则，通常订单号、用户ID+流水号这类组合已经在业务逻辑上保证唯一，不需要做全表扫描挖掘，能节省大量时间。

落地执行步骤 #

1. 查数据字典拿到所有列的基数、NULL占比，过滤掉NULL占比超过20%、基数<100的列，缩小候选列范围
2. 先验证所有单列是否满足唯一性，有符合的直接就是最优解
3. 单列都不满足的情况下，按单列基数从高到低生成两列组合，先采样校验再做全表验证
4. 找到第一个符合唯一性要求的列组合，就是你需要的最小唯一索引列集合

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