This forum is now a read-only archive. All commenting, posting, registration services have been turned off. Those needing community support and/or wanting to ask questions should refer to the Tag/Forum map, and to http://spring.io/questions for a curated list of stackoverflow tags that Pivotal engineers, and the community, monitor.

If I use option 1 or option 2 it's sub second, but option 3 takes 30 seconds.
For some reason option 3 causes Oracle to do a full table scan.
The column in question is defined as a VARCHAR2 and has an index.
The actually query is more complex but it seems to boil down to me using option 3 and the explain plan changing and I have no idea why.

Have you tried the addValue method WITHOUT a sql type? It might be due to type compatibility that Oracle decides to do a full table scan. I'm also wondering what a normal jdbc template would do when you use a ? instead of a named value.

Comment

I'd agree with rishishehrawat that most likely the use of a bind variable is confusing the optimizer and forcing a table scan. Try using a hint or avoid the bind variable. There is lots of interesting info for query optimization in the Oracle docs.

Comment

Hi option 1 uses the % and uses the index, I think if you are using %852% the index can't be used, but wouldn't be that sure.

Originally I had addValue without the sql type and had the same problem. I thought because the string passed in was a number Oracle was doing a type conversion which would cause a full table scan so I added in the type to be sure.

I'll try a normal template and let you know.

Thanks
David

Comment

I tried JdbcTemplate and then a direct java connection and got the same results, so apologies Spring is innocent in this.
But thanks to all as it helped me find a solution, I read the link trisberg gave me (not all of it obviously) and found out I needed bind variable peeking.
This is what was causing the full scan, or a lack of it.
I then found thishttp://asktom.oracle.com/pls/asktom/...19398056075583