Date Filters and their impact over Index

I was discussing with my friend on date filters and how they impact the performance, thought this topic is worth blogging.

With tables holding historic data, you would see columns ‘Effective Date or termination date’ which would logically mark the record as inactive and also helps to pick the current active record. Again few store NULL or any futuristic date in these columns to determine the active status. Now from this perspective let us see how date formatting can impact the optimizer decisions

Yes the actual cardinality is 26721. Now am not going to SQL Profiles to go further to validate the plan because my first bet is always on the data,application design, the first question is why this SQL and what use the operator ‘>’ .

The SQL intent is to pick active trades whose end timestamp is 01-01-9999, so let us say this to Oracle and see how this overcomes the trouble created by the operator ‘> systimestamp’ that is sky is the limit 🙂

Both timestamp and to_timestamp yield the same results in terms of access path. So avoid using sky limit (> systimestamp) in your SQL’s if you know what you are looking for. SQL’s like these do they impact partitions, watch out for the next post.