Performanace Issue

articles: 

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.

Comments