Meaningless Drivel is fun!*
The moose likes JDBC 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 Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
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
internet detective

Joined: May 26, 2003
Posts: 30136

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

[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, 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
Similar Threads
URGENT --Help needed --- Delay in executing SQL Query..
pre-compile and prepared statement
RazorSQL 5.2 SQL Editor and Database Query Tool Released
ORA-00933 SQL Command not properly ended
Strange issue with JDBC execute performance