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:
to_date()
java.sql.Timestamp
Both approaches work and have exclusive areas of hideousness. My problem is when I'm SELECT
ing 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.
Answers. You can use the TO_DATE function. Convert both to timestamp and compare.
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.
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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With