Slow query in Java by JDBC but not in other systems (TOAD)

Question

Hello i have a query to an Oracle System which involves a view which joins other tables by apliying an TO_NUMBER() to the tables primary key.

If i do the query using TOAD the query is very fast (1 sec for 800 regs). If i do the same query in a java program by JDBC with a String literal (not a parametrized query), the time is good too.

But if i use a parametriced query by an PreparedStatement the query takes 1 min to fetch the same registers. I know that using constant values produce distinct execution plan than using parameters... but if i remove the TO_NUMBER funcions in the joins of the view, the parametriced query is fast too.

• Is the parameters / TO_NUMBER() union preventing to use the PK index of the joined tables?
• is there a workaround to solve this (i need the parameters on the query and also the TO_NUMBER function)?

P.D. sry for my bad english

Answer 1

without additional information, we can only assume that an index isn't being used with the to_number() function is applied to the column. As shown in this SO question, a type conversion can prevent the optimizer from using an index.

In general:

• when you add a function to a column (i-e: to_number(id)) the optimizer won't be able to use the regular indexes on that column,
• if it is possible, you should use the column raw. For exemple: instead of WHERE trunc(col) = DATE '2009-08-27' you should use: WHERE col >= DATE '2009-08-27' AND col < DATE '2009-08-28'
• if you really have to apply a function to a column, you can use a function-based index

Answer 2

Check that the data type of the Java variable passed in the parameter is compatible with the Oracle data type. I have seen symptoms similar to yours when passing Java TIMESTAMP's through a bind variable that was being compared to Oracle DATE columns - literal string query OK, test case in PL/SQL with (date) bind OK, Java code w/ mismatch not OK.

[Edit] I think you've provided some additional information since the original posting. The best way to understand what is happening with the slightly different forms (binds vs. literals) of the query from different environments (Java vs. Toad) is to enable tracing during the execution and compare the execution paths from the resulting trace files. This will require that you have access to the database host to retrieve the files.

• In Toad, open an interactive SQL window (I don't use Toad but I'm sure you'll understand what I mean) and issue the SQL command "alter session set sql_trace=true"
• Run your query - it would be a good idea to add a comment to the query such as "/* Toad with literals */"
• For the Java test, build a test case that issues the "alter session..." statement and then runs the query. Again, add a comment to the query to identify it as coming from the Java test.
• Don't worry about turning the tracing off - this will happen when the sessions are disconnected and in some cases the disconnection method of stopping the trace is preferred.
• Find out where your trace files on the database host are by "select value from v$parameter where name like 'user_dump_dest' "
• Find the .trc files by searching for the query comment strings
• Use the TKPROF utility from the OS command line to process the trace file - " tkprof filename.trc tkprof filename.out "
• Examine/post the execution paths and times that you see.