案例模拟Oracle数据一致性问题模拟案例存在重复数据的主键表

步骤1：创建表并插入数据

我们首先创建一个简单的员工表，并插入一些数据，其中包含重复的“主键”值。

-- 1. 创建表，ID列将作为主键，但先不创建约束
CREATE TABLE employee (
    id   NUMBER(10),
    name VARCHAR2(50),
    dept VARCHAR2(50)
);
-- 2. 插入数据，其中id=1的数据故意插入两次（模拟历史重复数据）
INSERT INTO employee VALUES (1, '张三', '技术部');
INSERT INTO employee VALUES (2, '李四', '市场部');
INSERT INTO employee VALUES (1, '王五', '销售部'); -- 重复主键值
INSERT INTO employee VALUES (3, '赵六', '财务部');
COMMIT;

步骤2：以“异常”方式创建主键（模拟历史原因）

这里的关键是： 主键约束与索引可以分离。我们创建一个主键约束，但它依赖于一个普通（非唯一）索引，或者先创建约束但不验证现有数据。

-- 方式A：创建一个普通（非唯一）索引，然后基于它添加主键
CREATE INDEX idx_emp_id ON employee(id); -- 注意：这不是唯一索引！
ALTER TABLE employee ADD CONSTRAINT pk_employee PRIMARY KEY (id) USING INDEX idx_emp_id;
-- 此时主键约束创建，但底层索引是非唯一的，不阻止重复值
-- 方式B（更常见）：先以NOVALIDATE方式添加主键，不检查已有数据，然后索引可能损坏或失效
-- ALTER TABLE employee ADD CONSTRAINT pk_employee PRIMARY KEY (id) ENABLE NOVALIDATE;

步骤3：重现你的查询现象

现在我们来执行你提到的两种查询：

-- 查询1：走主键索引查询（返回一条记录）
SELECT * FROM employee WHERE id = 1;
-- 结果可能只显示：1张三技术部
-- 查询2：强制全表扫描查询（返回两条记录）
SELECT /*+ FULL(employee) */ * FROM employee WHERE id = 1;
-- 结果会显示两条：
-- 1张三技术部
-- 1王五销售部

根本原因解析

这种现象的核心是： Oracle的主键约束（CONSTRAINT）和主键索引（INDEX）是两个不同的对象，它们的状态可能不一致。

主键约束：约束在逻辑上存在，允许重复数据已存在，但阻止新的重复数据插入。 主键索引： 索引结构可能缺失唯一性，导致通过索引访问时无法定位到所有重复行。

为什么查询结果不同？

走索引查询：当执行  WHERE id = 1 时，如果优化器选择了可能“损坏”或“非唯一”的  idx_emp_id 索引，Oracle可能会根据索引的 唯一性假设，在找到第一条匹配记录后就停止查找，导致漏掉后续重复行。

1. 强制全表扫描： /*+ FULL */ 提示强制数据库逐行扫描表数据。它会忠实地检查每一行，因此能发现所有  id=1 的记录。

-- 找出所有重复的ID值
SELECT id, COUNT(*)
FROM employee
GROUP BY id
HAVING COUNT(*) > 1;

-- 假设有create_date字段，保留最新的
DELETE FROM employee
WHERE ROWID NOT IN (
    SELECT MAX(ROWID)
    FROM employee
    GROUP BY id
    HAVING COUNT(*) > 1
);
-- 或者更安全的方式：将需要删除的记录先备份到另一张表

-- 1. 删除有问题的原主键约束和索引
ALTER TABLE employee DROP CONSTRAINT pk_employee DROP INDEX;
-- 2. 创建唯一的、有效的索引
CREATE UNIQUE INDEX idx_emp_id_unique ON employee(id);
-- 3. 基于唯一索引添加主键约束
ALTER TABLE employee ADD CONSTRAINT pk_employee PRIMARY KEY (id) USING INDEX idx_emp_id_unique;
-- 4. 验证约束状态
SELECT constraint_name, status, validated, index_name
FROM user_constraints
WHERE table_name = 'EMPLOYEE';
-- 正确状态应为：STATUS='ENABLED', VALIDATED='VALIDATED'