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 JDBC and optimizer query hints - are they recognised? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "JDBC and optimizer query hints - are they recognised?" Watch "JDBC and optimizer query hints - are they recognised?" New topic

JDBC and optimizer query hints - are they recognised?

Mike Hayes

Joined: Aug 26, 2008
Posts: 4
Hi folks,

An Oracle hotshot at a customer site has identified a problem query that can be improved by adding optimizer hints of the form "select /*+ index(.... etc".
The query is executed using a regular JDBC PreparedStatement. The database is oracle 9i and we're using the (not sure of exact revision) for the JDBC driver.
It's a legacy app and the code is relatively old stuff that I've inherited and that I usually don't have to go near very often.

I don't have the necessary Oracle skills to properly interpret EXPLAIN output yet (working on it) so I'm clueless as to whether the hints make any difference to the query. That and the development database is nowhere near as big as the customers, so there's no observable difference between the results of the query with or without the hints.

Does the oracle driver do anything to optimizer hints? I think a prepared statement parses the query and I'm wondering if the driver ignores or discards the optimizer hints in the process. Does anyone have any experience of this?

Paul Sturrock

Joined: Apr 14, 2004
Posts: 10336

The JDBC driver should just pass the hints to the database.

Its your customer's call (and they'll have much better visibility of the problem than I have), but optimiser hints are usually the last thing to try. If they are using the cost based optimiser there is loads that can be done before reaching the using hints stage. If they are still using the (legacy) rule based optimiser - which they may we'll be since this is Oracle 9 - you might suggest trying CBO first. Its very useful because then your Oracle hotshot can tune your queries to his/her hearts content without changing any code. Just an idea.

JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Mike Hayes

Joined: Aug 26, 2008
Posts: 4
Hi Paul,

Thanks for the reply. There is a performance problem there, and the query in question doesn't seem to be the best in the world. I suspect the oracle analyst did his thing and found a dodgy query. But my expereicen is that tweaking one query meerly shifts the problem somewhere else.
Thanks again for the help, I'll see if I can persuade them to looking into using a different optimizer.

I agree. Here's the link:
subject: JDBC and optimizer query hints - are they recognised?
jQuery in Action, 3rd edition