Originally posted by Jothi Shankar Kumar Sankararaj:
Guys,
Why is that we should avoid using Oracle functions in the Where clause of a SQL statement? and how does Function Based Indexes help in this scenario?
[ November 04, 2008: Message edited by: Jothi Shankar Kumar Sankararaj ]
Primarily the issue is with date functions (not all functions) because translation between the function and the storage of the date invalidates the where clause and causes a full table scan.
For example:
The claim_dt column may have an index, but the the trunc and to_char functions will invalidate the index, causing a full-table scan to occur.
One of the things you can do to prevent this performance impact is to create a function-based index.
You could also just fix your where clause:
You could also create a redundant column in the table.
[ November 10, 2008: Message edited by: Paul Campbell ]