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.
