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

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

Please check whether index exists in docintno column. if index is not present, please remove the order by clause from this query.

Applying a function to an indexed column will usually cause Oracle to not use the index (at least that is what been told for 9i/10g). The way around this is to use an index hint in your select;

SELECT /*+ INDEX(T1) */ *
FROM history T1
WHERE TRUNC(actiondate) = TRUNC(SYSDATE-1);

As noted above, a function based index could be helpful (I added compression):

CREATE INDEX idx_history_action ON history(TRUNC(actiondate)) COMPRESS;

You are right. If you apply any function like upper, substring, trunc, lower etc to a column in the sql, then it will impact the performance. Hence the corrective action must be planned while doing the datamodel itself...

Thanks for sharing,
Vimal