Hi Sascha, It must be fate or (crazy coincidence?), but as we speak, I'm right now implementing https://github.com/jOOQ/jOOQ/issues/3369 for jOOQ 3.5.0 - the second-last important change on the 3.5.0 roadmap before we'll finally release (probably by the end of next week). #3369 was actually a driving force for a great new feature (https://github.com/jOOQ/jOOQ/issues/3248), which will allow users to completely control all interaction at the JDBC level with respect to custom data types (the <T> in Field<T>). Examples for such types are XML or JSON types.
With #3369 implemented, jOOQ will know that the original DB data type before <dateAsTimestamp/> rewriting was really DATE, even if the Java type is org.jooq.impl.SQLDataType.TIMESTAMP, or java.sql.Timestamp respectively. jOOQ will then bind the java.sql.Timestamp value to JDBC, but to a variable that is cast back to Oracle DATE: // jOOQ Condition Timestamp dateValue = ... DATE_COLUMN.eq(dateValue); The above will render: -- Generated SQL DATE_COLUMN = CAST(? AS DATE) Experiments (on 11g) and the answer to my 3.5-year-old question (which you've linked) have shown that this will prevent Oracle from widening the indexed DATE column to TIMESTAMP using this INTERNAL_FUNCTION(), which effectively prevents index usage. A quick fix prior to jOOQ 3.5.0 is to explicitly cast your bind values to DATE wherever this matters: DATE_COLUMN.eq(val(dateValue).cast(Date.class)) Another quick fix (since you're not using Oracle TIMESTAMP types) might be to implement a VisitListener and to transform all SQL generated by jOOQ to add that cast for any java.sql.Timestamp value. I'm going to be verifying the above solution also with an E-Banking customer on Oracle, who will be using this feature a lot to improve the performance of their database queries (they're using TIMESTAMP types as well, so the VisitListener solution won't work). This feature will actually be retrofitted into the <dateAsTimestamp/> feature, so after upgrading to 3.5.0, you won't have to do much, except perhaps rewrite your JodaTime converter and regenerate the schema. Hope this helps, Lukas 2014-11-14 15:23 GMT+01:00 Sascha Herrmann <[email protected]>: > Hi! > > We're on Oracle 10g. I have a large table where I see full table scans in > the explain plan. > > The table we're using in our condition has an indexed DATE column (we're > not using TIMESTAMP) but the column contains relevant time information in > the database. > So we're using: > > <!-- Generate java.sql.Timestamp fields for DATE columns. This is >> particularly useful for Oracle databases. Defaults to false --> >> <dateAsTimestamp>true</dateAsTimestamp> > > > We're also forcing DATE and TIMESTAMP to Joda DateTime using a converter. > > Now we're doing: > > DateTime sinceTime = new DateTime( ).minusDays( 1 ); >> List<BigDecimal> idList = create.select( >> PERFORMED_PROCEDURE.PERFORMED_PROCEDURE_ID ).from( >> PERFORMED_PROCEDURE ).where( >> PERFORMED_PROCEDURE.PP_MODIFIED_DATE.greaterThan( sinceTime ) >> ).fetch( >> PERFORMED_PROCEDURE.PERFORMED_PROCEDURE_ID ); > > > with PP_MODIFIED_DATE being the DATE column in question. In the log I see: > > 2014-11-14 15:09:47,027 [main] DEBUG org.jooq.tools.LoggerListener - >> Executing query : select >> "MEDVIEW"."PERFORMED_PROCEDURE"."PERFORMED_PROCEDURE_ID" from >> "MEDVIEW"."PERFORMED_PROCEDURE" where >> "MEDVIEW"."PERFORMED_PROCEDURE"."PP_MODIFIED_DATE" > ? >> 2014-11-14 15:09:47,027 [main] DEBUG org.jooq.tools.LoggerListener - -> >> with bind values : select >> "MEDVIEW"."PERFORMED_PROCEDURE"."PERFORMED_PROCEDURE_ID" from >> "MEDVIEW"."PERFORMED_PROCEDURE" where >> "MEDVIEW"."PERFORMED_PROCEDURE"."PP_MODIFIED_DATE" > timestamp '2014-11-13 >> 15:09:33.57' >> 2014-11-14 15:09:47,027 [main] TRACE org.jooq.tools.StopWatch - Query >> rendered : Total: 0.9ms, +0.806ms >> 2014-11-14 15:09:47,027 [main] TRACE org.jooq.tools.StopWatch - Preparing >> statement : Total: 0.996ms, +0.096ms >> 2014-11-14 15:09:47,027 [main] TRACE org.jooq.tools.StopWatch - Statement >> prepared : Total: 1.148ms, +0.152ms >> 2014-11-14 15:09:47,027 [main] TRACE org.jooq.tools.StopWatch - Binding >> variables : Total: 1.24ms, +0.091ms >> 2014-11-14 15:09:47,027 [main] TRACE org.jooq.impl.DefaultBindContext - >> Binding variable 1 : 2014-11-13 15:09:33.57 (class java.sql.Timestamp) > > > The plan should be fine in theory and when I get the plan for a comparable > statement (with Oracle's to_date()) in SQLPlus I see a nice index access. > But when I run the code in jOOQ I see a full table scan. > > Now, I get the impression that the usage of timestamp prevents Oracle from > using the index resulting in a full table scan. > > I have found: > > http://stackoverflow.com/questions/6612679/non-negligible-execution-plan-difference-with-oracle-when-using-jdbc-timestamp-or > > but the solution is not clear to me. Using java.sql.Timestamp for DATE > columns seems to be the right way to go in jOOQ, since we need the time > information. But if it means that timestamp will be used in the query and > none of our indizes is used, then we're screwed. > > After my previous experience with "jOOQ issues" (which turned out to be > embarrassing goof-ups by us) I am sure that it's us again doing something > stupid. But what? > How can we keep that Date-as-Timestamp handling without losing our indizes > (and rewriting a ton of code)? > > Regards, > > Sascha > > > > > -- > You received this message because you are subscribed to the Google Groups > "jOOQ User Group" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > For more options, visit https://groups.google.com/d/optout. > -- You received this message because you are subscribed to the Google Groups "jOOQ User Group" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/d/optout.
