Hello,
we have a performance problem with hibernate / oracle on a large database table (20 million rows)

basically the problem is as follows.

we have table with a ternery index, say field1, field 2, field3.

the problematic statement is "from table where field1 like ? and field2 like ? and field3 like ?".

in our gui we are allowing parameters for field1/2/3 which are selective enough to allow oracle to use the index.
using plain sql (no prepared statements) with these parameters the queries are fast as hell (<0.1seconds).
in hibernate (using prepared statements) these queries execute for minutes.

my theory for this is as follows.
with prepared statements oracle determines the execution path by analyzing the prepared statement in advance
without taking into account the parameters and decides for a full table scan.
with plain sql oracle decides to use the index above (and this decision is right).

my questions now:
1.) am i right with my theory?
2.) how can a get rid of this problem?
3.) is there any way to tell hibernate (or oracle jdbc) to avoid prepared statements (at all or on specific queries)?

i dont want to use oracle hints on my statements for different reasons (e.g. the application should run on mysql as well and in fact it does well and fast).
thanks for your help in advance.

best michael

You should look into oracle optimizing guide, I would start trying to analyze the table to rebuild the statistics

analyze table &table estimate statics;

If you still suffer from the problem, try building the query dynamically so you don't use like on all fields if they aren't needed (use Criteria for that). You can also fallback to native query and dont use bind variables, but in my experience they should be fast unless there is a problem. Also, check if your database is propertly patched to at least 10.1.5/10.2.4, there is lots of fixes reported in the release notes.
If you don't manage to find a solution that way, try using a different tool that support bind variables and try your query into it to see if the problem persists (I recomend PL/SQL Developer from Allround Automations, they have a trial available and it can run on wine with the Instant Client if you are not on windows), if it doesn't maybe the JDCB driver is doing something weird, try making sure you using the ojdbc5 driver instead of ojdbc14). Hope that helps :)