I have a table A with 3 million rows that spans over 2 months. I have created index on created_date column. Timestamp (minutes,seconds) is not stored in created_date column. Query on this table has a where clause looking for created_date range of 10 days(without any timestamp in where clause). As timestamp is not stored in created_date column, index on that column is showing very low distinct_keys and query is doing lot of Logical IO's. Can someone please advise whether storing timestamp will help in any way to reduce huge Logical IO's.
Thanks for your prompt reponse. Query is using the index on created_date column and it has low selectivity. So I thought that this would cause heavy logical io's. I cannot modify query to supress index or add hints to force non usage of index on this column. Can you please suggest me if there is any other way.
If all queries against this table use the 10 day where clause, then the index will never be good to use. If you have any queries going against a specific date, then it's probably good for those (1/60th of the table).
Depends on how big the table really is, in number of blocks (not rows).
If you need to keep the index for other sql, try using stored outlines - they work great for adjusting apps/sql that you can't otherwise modify...
"False data can act only as a distraction. Therefore. I shall refuse to perceive you." - Bomb #20