Hello!

I would like to ask you for your opinion to a certain problem we encountered and would like to know how I can solve this with hibernate.

We have a timestamp-field (the name is "replaced") in some tables which has the information "is valid until".
When the data record is valid (actual), the value of this field is "9999-12-31 23:59:59:999999" (means indefinitely).
When the data record is invalid (historic), the value of this field is a concrete timestamp.

Now the table has 50.000.000 entries and 30.000.000 entries have the value "9999-12-31 23:59:59:999999", which are 60%.
The database is DB2. There is an index on the column, so DB2 knows the n most used values (this is maintained via the command "runstats").

When a statement retrieves more than 10% of the entries from a table, an index scan is inefficient. Instead the database should use a table scan.
Therefore it is ideal if the database knows the values from an index (it does) and judge for each statement, if it should use an index or a table scan.

When I perform a SELECT with parameter marker via JDBC and I am using the column "replaced" in a where-statement,
the value "9999-12-31 23:59:59:999999" is replaced with a parameter marker. DB2 doesn't know the concrete value now and decides for an index scan.
The performance is not useable. When searching for a concrete value for the column "replaced", the performance is optimal.

When I perform a SELECT without parameter marker via JDBC and I am using the column "replaced" in a where-statement,
the value "9999-12-31 23:59:59:999999" is NOT replaced with a parameter marker. DB2 knows the value and decides for a table scan.
The performance is ok. When searching for a concrete value for the column "replaced", the statement is not cached, so the performance is good but not so good as with parameter marker.

So the ideal strategy is to use a parameter marker for the column replaced, when there is a concrete value and
not to use a parameter marker for default values ("9999-12-31 23:59:59:999999").
We have tested with other columns also and got the best performance when using parameter markers only on certain columns.

What I would like to know:
Does anyone have similarly problems with parameter marker and how do you deal with it?
Can Hibernate configure the use of parameter marker on certain columns (usage on/off)?
Can I provide my own SQL generation class?

Thanks in advance,
CL

It seams that I have to reduce my questions :-)

So I would only like to ask:
Is it possible to provide my own SQL generation class?

Thanks in advance, CL

Just wanted to give another try:
Is it possible to provide my own SQL generation class or to tweak SQL generation?

Thanks in advance, CL

u may write ur own find() in session for retrieving objects from session by redefining find() in Session class.

u wan't have any technique to write own SQL generation..