25Jan/080
Dates and Indexing in Oracle
Warning: file_get_contents(http://feeds.delicious.com/v2/json/urlinfo/data?url=http%3A%2F%2Fstephennimmo.com%2Fblog%2F2008%2F01%2F25%2Fdates-and-indexing-in-oracle%2F) [function.file-get-contents]: failed to open stream: HTTP request failed! HTTP/1.0 999 Unable to process request at this time -- error 999 in /home1/stephenn/public_html/blog/wp-content/plugins/digg-digg/dd.class.php on line 866
Indexing in Oracle for dates can be a bit tricky, especially if you do a lot of querying according to a date without a specific time element. The issue stems from the trunc() function. Any trunc function basically negates the value of an index on a date column in oracle.
Instead of doing something like:
select * from table where date = trunc(to_date('01/01/2008', 'mm/dd/yyyy')
which, if you have an index on date will basically remove the benefits, try using:
select * from table where date between trunc(to_date('01/01/2008', 'mm/dd/yyyy') and trunc(to_date('01/01/2008', 'mm/dd/yyyy') + .999
This allows you to have the power of the trunc without sacrificing the speed of your index.
Thanks to Eddie.