两个JDBC对于Oracle语句性能的差距是如此之大

Question

这是我的代码，使用 prepareStatement 动态发送rq时速度非常慢

for (int i = 0; i < 1; i++) {
String sql = "select KHDM from T_KHZHYKQK where khdm= '10797684' and zjzhlxdm = '0' and rq >= '20191230' and rq <= '20210104' order by rq";
String sql2 = "select KHDM from T_KHZHYKQK where khdm = '10797684' and zjzhlxdm = '0' and rq >= ? and rq <= ? order by rq";
Statement statement = connection.createStatement();
for (int j = 0; j < 5; j++) {
long start = System.currentTimeMillis();
statement.executeQuery(sql);
long end = System.currentTimeMillis();
System.out.println("time1:" + (end - start));
}
System.out.println();
preStmt = connection.prepareStatement(sql2);
for (int j = 0; j < 5; j++) {
long start1 = System.currentTimeMillis();
preStmt.setString(1, "20191230");
preStmt.setString(2, "20210104");
preStmt.executeQuery();
long end1= System.currentTimeMillis();
System.out.println("time2:" + (end1 - start1));
}
System.out.println();
}

这就是结果，两个语句的差距表现如此之大。

time1:206
time1:108
time1:110
time1:109
time1:111
time2:7752
time2:7372
time2:7029
time2:6961
time2:7105

rq在数据库中RQ NUMBER(8) default 0 not null。我试过setInt()，setLong，setBigDecimal()，结果没有什么不同。 数据库版本为 12c。我想知道原因。 我的甲骨文版本：

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

我的 JDBC 版本：

Manifest-Version: 1.0
Ant-Version: Apache Ant 1.7.1
Implementation-Title: JDBC
Implementation-Version: 12.2.0.1.0
sealed: true
Repository-Id: JAVAVM_12.2.0.1.0_LINUX.X64_161213
Specification-Vendor: Sun Microsystems Inc.
Specification-Title: JDBC
Created-By: 25.91-b14 (Oracle Corporation)
Specification-Version: 4.0
Implementation-Vendor: Oracle Corporation
Main-Class: oracle.jdbc.OracleDriver
Name: oracle/sql/
Sealed: false
Name: oracle/sql/converter/
Sealed: false
Name: oracle/jdbc/logging/annotations/
Sealed: false
Name: oracle/sql/converter_xcharset/
Sealed: false

Answer 1

您的两个查询都可能受益于索引。 采用第一个版本，它不需要准备好的语句：

SELECT KHDM
FROM T_KHZHYKQK
WHERE khdm = '10797684' AND zjzhlxdm = '0' AND rq BETWEEN '20191230' AND '20210104'
ORDER BY rq;

以下索引可能有助于此查询：

CREATE INDEX idx ON T_KHZHYKQK (rq, zjzhlxdm, khdm);

该索引完全涵盖了WHERE、ORDER BY和SELECT条款。 您可以尝试更改索引中三列的顺序，尽管我的建议是我可能会首先尝试的。