Performanace Issue



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

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,