File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes Some indexes not used in JDBC call Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of REST with Spring (video course) this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Some indexes not used in JDBC call" Watch "Some indexes not used in JDBC call" New topic

Some indexes not used in JDBC call

Jefferson Parke

Joined: May 03, 2004
Posts: 5
Hello everyone,

I'm having a problem where a JDBC PreparedStatement without bind parameters can take more than a minute to execute a query that takes less than a second to execute in SQL*Plus. The query is identical, the database instance is the same, neither query is cached, and the query returns only 18 records with 11 columns all of which are either VARCHAR2 or NUMBER. I'm using Oracle's JDBC 2.0 drivers (classes12.jar) and Oracle 8i (Release database. Oracle DB is set to use the cost-based optimizer.

I did an explain plan in SQL*Plus and via JDBC. It turns out that some of the unique indexes that are used when executing the query in SQL*Plus are not used when executing via JDBC.

Does anyone know why this would happen?

Jeanne Boyarsky
author & internet detective

Joined: May 26, 2003
Posts: 32818

Strange. Is it safe to assume that the query is being run with the same user id through JDBC and SQLPlus?

[OCA 8 book] [Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Other Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, TOGAF part 1 and part 2
Jefferson Parke

Joined: May 03, 2004
Posts: 5
It's actually not safe to assume that, and yes the performance difference is attributable to different users. It's still the same database instance and same table being read (no synonyms or anything). I'm now looking into Oracle table policies. This is no longer a JDBC issue.

I agree. Here's the link:
subject: Some indexes not used in JDBC call
It's not a secret anymore!