aspose file tools*
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
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Some indexes not used in JDBC call" Watch "Some indexes not used in JDBC call" New topic
Author

Some indexes not used in JDBC call

Jefferson Parke
Greenhorn

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 8.1.7.4.0) 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?

Thanks,
Jeff
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30512
    
150

Jeff,
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
Greenhorn

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.

Thanks!
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Some indexes not used in JDBC call