This discussion is archived
9 Replies Latest reply: Apr 11, 2013 8:18 PM by rp0428 RSS

Prepared statement doesn't use Oracle index on TIMESTAMP column

1002261 Newbie
Currently Being Moderated
We've a table with one of the columns being defined as TIMESTAMP(6) datatype (We also have an index on this column)

What we're observing is, when we use prepared statement to bind to this column, the query becomes slow. On the other end, if we use a 'Statement', the query runs fast.

Any hints/clues !

Please let me know if you need more info,

Java version : 1.6
jdbc driver - ojdbc6
Oracle version : 11.2.0.3.0
Driver type : thin driver
  • 1. Re: Prepared statement doesn't use Oracle index on TIMESTAMP column
    aksarben Journeyer
    Currently Being Moderated
    Since your symptom is a slow query, the first thing I'd do is talk to my DBA
  • 2. Re: Prepared statement doesn't use Oracle index on TIMESTAMP column
    jschellSomeoneStoleMyAlias Expert
    Currently Being Moderated
    999258 wrote:
    Please let me know if you need more info,
    What exactly is "slow"?
    Exactly how did you measure it?
    Exactly how did you format the prepared statement versus the statement?
    How did you insure that the two above were in fact identical?
  • 3. Re: Prepared statement doesn't use Oracle index on TIMESTAMP column
    rp0428 Guru
    Currently Being Moderated
    >
    Please let me know if you need more info,
    >
    Ah - you mean like the Java code you are using that actually has the problem?

    Or did you mean the actual query being used and the values being bound?

    Or did you mean the table and index DDL?

    I vote for ALL OF THE ABOVE.

    And read my reply in this thread. The Oracle 11.1 JDBC driver fixed a bug that had existed for years related to DATE and TIMESTAMP conversions and your issue could be related to that.
    https://kr.forums.oracle.com/forums/thread.jspa?messageID=10180949
  • 4. Re: Prepared statement doesn't use Oracle index on TIMESTAMP column
    1002261 Newbie
    Currently Being Moderated
    Thanks for replying....

    The original query is kinda big but I can explain the situation with a simple query.

    Consider the below query which I want to run,

    SELECT * FROM MYTABLE T WHERE T.CRTN_TMST BETWEEN TO_DATE( ?, 'MM/DD/YYYY HH24:MI:SS') AND TO_DATE( ?, 'MM/DD/YYYY HH24:MI:SS') order by CRTN_TMST desc

    I've an index on CRTN_TMST and it is defined as TIMSTAMP(6) datatype in Oracle.

    so If I run the above query using preparedstatement, the query takes a longer time to return where as if I run using statement (in which case i'll providing the actual input value) the query returns way faster. ALso, the same sql if I run from PL/SQL developer or TOAD, it runs faster as well

    Please don't concentrate much on the sql and let me know if you want the real sql I'm using,

    PLease let me know if you need more info
  • 5. Re: Prepared statement doesn't use Oracle index on TIMESTAMP column
    1002261 Newbie
    Currently Being Moderated
    Thanks for replying,

    I don't think the java code has the problem as I basically using a JUnit to test between preparedstatement and statement

    The issue I'm observing is when I use preparedstatement to bind the variable, it looks like the query which runs in database doesn't make use of index where as if I use a statement (in which case it'll as if I'm running the sql from

    a SQL client tool) it returns fast.

    An additional info, my table is partiioned

    So whatever research I've done, I see this issue was there in the past....and seen statements like these

    "When you have a table that contains a millenium rows of data and you have a Index set up for the table, your PreparedStatement can not take the advantage of the Index for queries. Statement will out-perform PreparedStatement."

    One of the posts says this,
    http://stackoverflow.com/questions/15357571/oracle-prepared-statement-hangs
  • 6. Re: Prepared statement doesn't use Oracle index on TIMESTAMP column
    1002261 Newbie
    Currently Being Moderated
    Another similar post recently on the same issue,

    query is very slow if using PreparedStatement
  • 7. Re: Prepared statement doesn't use Oracle index on TIMESTAMP column
    761757 Newbie
    Currently Being Moderated
    As people have already said , execution plan would be the key for your debug. I had a problem similar to this couple of days back and after a detailed discussion with our DBA he pointed out the problem was because of degree of parallelism (DOP) . He suggested me to provide a sql hint in the query to specify the parallelism and there was for sure improvement in the processing time of the prepared statement .

    But before doing anything , you might have to get your queries execution plan verified by a DBA as he can for sure point you out any logical mistakes.
  • 8. Re: Prepared statement doesn't use Oracle index on TIMESTAMP column
    1002261 Newbie
    Currently Being Moderated
    Appreciate the reply...

    Just to update you guys, I just had a glance at this 11g documentation http://docs.oracle.com/cd/E11882_01/server.112/e25523/part_avail.htm#BABHCBGJ

    Now I'm able to replicate the issue in pl/sql developer and TOAD....so no Java involved here and this tells me this is the way Oracle works with partitioned tables,t


    I'm able to see what the documentation says in terms of explain plan....it's just sad that dynamic pruning doesn't work exactly like static pruning at runtime
  • 9. Re: Prepared statement doesn't use Oracle index on TIMESTAMP column
    rp0428 Guru
    Currently Being Moderated
    >
    I don't think the java code has the problem as I basically using a JUnit to test between preparedstatement and statement
    >
    What does that have to do with it?

    You are using two DIFFERENT pieces of code (neither of which you have posted) when you use two different types of statements.
    >
    The issue I'm observing is when I use preparedstatement to bind the variable, it looks like the query which runs in database doesn't make use of index where as if I use a statement (in which case it'll as if I'm running the sql from
    >
    Is that just a guess or is it based on fact, for example an execution plan (which you haven't posted)?

    You also haven't posted the table and index DDL. You mention indexes and a later post mentions a link to a doc that mentions partition pruning.

    Is the table partitioned? What is the partition key? The DDL would have answered both of those questions and you keep making us guess.

    For a partitioned table if Oracle is not performing partitioning pruning at all (which the unposted execution plan would show) then it may not have anything to do with the index. It may be doing a full table scan instead of a single partition.

    Why are you using a query with TO_DATE if the column is defined as TIMESTAMP? Why aren't you using TO_TIMESTAMP? Have you tried that?

    You keep making us guess. You said at first if we need more info you will provide it but then you don't provide it when we ask for it.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points