Hi Lukas, wow I am glad. So we will just hang tight and wait for the release. We're still in the development phase, so waiting for a week is not an issue.
And I am glad that it's (somewhat) an issue. After my previous posts I was afraid it's again us goofing around. :o) We really need to change our mindsets here. We've been using Oracle ADF for 12 years now, and if there was an issue (and boy, were there issues!) it was always the framework. Now with jOOQ we have to double-check (and then re-check) before posting an "issue". Because chances are it's not an issue, but a "pilot error". That was a compliment, in case I wasn't clear enough. :o) Thanks for the swift reply. Looking forward (again) to 3.5. Sascha Am Freitag, 14. November 2014 15:54:55 UTC+1 schrieb Lukas Eder: > > 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] > <javascript:>>: > >> 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] <javascript:>. >> 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.
