monline

People love to write queries that use functions in conditions, but this can easily lead to full table scans.

It's easy to write queries with conditions like this:

However this way you force a full table scan (!) if you have just an ordinary index on the create_date column. You have two choices to avoid this:

1. Recreate the index on the create_date column as a function-based one using the TRUNC() in the index definition. This way you can keep your old query/code ... however there're some requirements on the database side that you might not be able to fulfill (eg. if you're just the developer of the software and have absolutely no influence on the DBA ... this sounds pretty bad, but it happens to me once in a while ).
2. The other alternative is not to use functions in queries at all (if possible). Eg. in the example above one could remove the TRUNC() from the condition and modify the values in the bind variables to achieve the same effect.

The point is: you should always check the execution plan of your queries! This way you can spot inappropriate index-usage before the code gets out of your hands and possibly raises serious performance issues at the client/customer.

PS: Sometimes you have to decide whether you write a code fast or at high quality ... but then it takes a lot longer and you might miss your deadline. The good developer always finds the best trade-off between time and quality. So it's a hard time to be a good one.