low index distinct keys

[CHALAVADI]

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.

[mike9]

why should a timestamp reduce the amount of logical IO? As I understand you limit your query based on a range of 10 days so I don't see the benefit of a timestamp.

Also when accessing 1/6 of all rows (10 days/ total of 60 days) it is probably faster to do a fts then accessing via an index.

Instead of using a index you may partition your table at a daily level. This way you would probably get the best response time for your query.

[CHALAVADI]

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.

[tomcat]

stored outlines work for me

[slimdave]

drop the index?

[gandolf989]

drop the index?

I'm curious, how would this lower the logical io's?

If it were me I would use range partitioning based on date.
Then purging data would involve dropping partitions.

[jmodic]

I'm curious, how would this lower the logical io's?

Because the query will not be able to use this ineficient index and will be forced to use FTS.

[slimdave]

Because the query will not be able to use this ineficient index and will be forced to use FTS.

ka-ching!

[tomcat]

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...

[CHALAVADI]

Thanks to everyone. I will explore stored outlines option.