Performance of the query based on timestamp vs date comparison
779375May 3 2011 — edited May 3 2011Hello, All,
I have a set of tables changes in which are tracked by LAST_MODIFIED field (timestamp, which is updated every time respective record gets changed). All those tables are going to be big ones - up to 7M of records in each
There is an index on LAST_MODIFIED for all those tables, but still queries like 'select * from TABLE where LAST_MODIFIED > sample_timestamp' are quite slow (approximately 2-3 sec for 1M of records).
What I noticed that running a query like this - 'select * from TABLE where LAST_MODIFIED > sample_timestamp+0' gives 50-60 times speedup but as far as I understand adding 0 converts TIMESTAMP to DATE (therefore cutting milliseconds which are important).
So the questions are:
1. why my original query is so slow? Are indices built on TIMESTAMP fields not effective in general?
2. Is there a way to cheat Oracle by doing some similar trick like I did but with no losing milliseconds?
Thank you very much!
Max