Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Why is Oracle so slow when I pass a java.sql.Timestamp for a DATE column?

I have a table with a DATE column with time (as usual in Oracle since there isn't a TIME type). When I query that column from JDBC, I have two options:

  • Manually convert the values with Oracle's to_date()
  • Use a java.sql.Timestamp

Both approaches work and have exclusive areas of hideousness. My problem is when I'm SELECTing data. Here are two sample queries:

select *
from TABLE
where TS between {ts '2009-12-08 00:00:00.000'} and {ts '2009-12-09 00:00:00.000'}

select *
from TABLE
where TS between trunc({ts '2009-12-08 00:00:00.000'}) and trunc({ts '2009-12-09 00:00:00.000'})

Both queries work, return the same results and produce the exact same output in EXPLAIN PLAN. This right indexes are used.

Only query one runs 15 minutes while the second query takes 0.031s. Why is that? Is there a central place to fix this or do I have to check all my queries for this column and make utterly sure that the trunc() is in there? How do I fix this issue when I need to select down to a certain second?

[EDIT] The table is partitioned and I'm on Oracle 10.2.0.

like image 525
Aaron Digulla Avatar asked Dec 22 '09 10:12

Aaron Digulla


People also ask

Can Oracle timestamp compare with DATE?

Answers. You can use the TO_DATE function. Convert both to timestamp and compare.

How do I make SQL query run faster in Oracle?

Partitioning your data and creating local partitioned indexes can improve your query performance. On a partitioned table, each partition has its own set of index tables. Effectively, there are multiple indexes, but the results from each are combined as necessary to produce the final result set.

Why is Oracle DB so slow?

The most common causes of slow performance are as follows: Excessive round-trips from the application server to the database. Ideally, each UI operation should require exactly one round-trip to the database. Sometimes, the framework will require additional round-trips to retrieve and make session data persistent.


1 Answers

This is because TIMESTAMP datatype is more accurate than DATE so when you supply TIMESTAMP parameter value into DATE column condition, Oracle has to convert all DATE values into TIMESTAMP to make a comparison (this is the INTERNAL_FUNCTION usage above) and therefore index has to be full scanned.

like image 50
FAB Avatar answered Sep 30 '22 01:09

FAB