Performanace Issue
I have worked in a table with one timestamp field namely Actiondate. We have updated the sysdate value of inserting time to that field. It contains around 30 lakhs records.
I want to get the records which are inserted a particular day.
I run the following 2 queries..
select * from history
where trunc(actiondate) > trunc(sysdate)
order by docintno
OR
select * from history
where trunc(actiondate) > '01-dec-2009'
order by docintno
Both are taking too much time to give the results.
Instead of that queries we have changed the conditions, now it gave the results very quick time.
The Tuned queries are..
The following query gives the records which are updated December 1st of 2009.
select * from history
where actiondate > '01-DEC-09 12.00.00.000000 AM'
order by docintno
The following query gives the records which are updated last 24 hours.
select * from history
where actiondate > sysdate -1
order by docintno
From this I came to conclusion to avoid the functions in table fields.
- atchaya's blog
- Login to post comments

Use function based index
In this case a function based index can be created to improve performance.