使用PreparedStatement查询Oracle时出现索引依赖的异常结果

问题描述 #

测试环境升级应用版本并引入新功能后，发现Oracle 21c执行特定PreparedStatement查询时返回异常结果：查询条件明确指定id<>10，但仍返回了id=10的记录，该问题与主键索引PK_TEST_BUG强相关。

最小复现环境 #

• 使用Docker启动Oracle 21c实例：

  docker run --restart always -d -p 1521:1521 -e ORACLE_PASSWORD=system --name oracle-21c-01 gvenzl/oracle-xe:21-slim
  

• 以system用户创建test用户并授权：

  CREATE USER test PROFILE DEFAULT IDENTIFIED BY test ACCOUNT UNLOCK;
  GRANT CONNECT TO test;
  GRANT UNLIMITED TABLESPACE TO test;
  GRANT CREATE TABLE TO test;
  

• 以test用户创建表、插入数据、添加约束并重建主键索引：

  CREATE TABLE TEST_BUG
  (
      ID         NUMBER(10)    NOT NULL
          CONSTRAINT PK_TEST_BUG PRIMARY KEY,
      TENANT     NUMBER(10)    NOT NULL,
      IDENTIFIER VARCHAR2(255) NOT NULL,
      NAME       VARCHAR2(255) NOT NULL
  );
  
  INSERT INTO TEST_BUG
  VALUES (10, 1, 'IDENTIFIER', 'TESTBUG');
  
  ALTER TABLE TEST_BUG
      ADD CONSTRAINT UK_NAME_TENANT UNIQUE (NAME, TENANT);
  ALTER TABLE TEST_BUG
      ADD CONSTRAINT UK_IDENTIFIER_TENANT UNIQUE (IDENTIFIER, TENANT);
  
  ALTER INDEX PK_TEST_BUG REBUILD;
  

异常复现代码（Java） #

public class Main {

    public static void main(String[] args) throws Exception {
        Class.forName("oracle.jdbc.driver.OracleDriver");
        try (Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE", "test", "test")) {
            try (PreparedStatement preparedStatement = connection.prepareStatement(
                    "select id from test_bug where tenant = ? and name=? and id<>?"
            )) {
                preparedStatement.setInt(1, 1);
                preparedStatement.setString(2, "TESTBUG");
                preparedStatement.setInt(3, 10);
                ResultSet resultSet = preparedStatement.executeQuery();
                System.out.println(resultSet.next());
                System.out.println(resultSet.getInt(1));
            }
        }
    }
}

异常输出 #

true
10

查询条件id<>10，但返回了id=10的记录。

补充现象 #

• 仅使用PreparedStatement时触发问题，SQL Developer中执行相同逻辑的SQL正常；
• 将id<>10硬编码到查询语句中时，执行结果正常；
• 禁用PK_TEST_BUG索引后查询正常，重建索引后问题复现；
• 更换不同版本ojdbc11驱动（如21.9.0.0），问题无差异。

解决思路 #

1. 强制指定执行计划：在查询语句中添加提示，强制使用唯一索引或全表扫描，跳过可能出错的主键索引：

   -- 强制使用UK_NAME_TENANT索引
   select /*+ INDEX(test_bug UK_NAME_TENANT) */ id from test_bug where tenant = ? and name=? and id<>?
   -- 强制全表扫描
   select /*+ FULL(test_bug) */ id from test_bug where tenant = ? and name=? and id<>?
   

2. 确保参数类型完全匹配：数据库中ID字段为NUMBER(10)，尝试改用setObject指定参数类型，避免隐式类型转换可能带来的问题：

   preparedStatement.setObject(3, 10, Types.NUMERIC);
   

3. 更新表与索引统计信息：索引统计信息过时可能导致执行计划错误，执行以下语句更新统计信息：

   ANALYZE TABLE TEST_BUG COMPUTE STATISTICS;
   ANALYZE INDEX PK_TEST_BUG COMPUTE STATISTICS;
   

4. 检查Oracle补丁：该问题可能是Oracle 21c的已知bug，查看Oracle官方支持文档，安装对应补丁或升级到最新补丁版本。
5. 重建主键索引：尝试删除并重新创建主键索引，而非使用REBUILD命令，可能修复索引内部的异常：

   ALTER TABLE TEST_BUG DROP CONSTRAINT PK_TEST_BUG;
   ALTER TABLE TEST_BUG ADD CONSTRAINT PK_TEST_BUG PRIMARY KEY (ID);
   

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