low index distinct keys
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: low index distinct keys

  1. #1
    Join Date
    Sep 2000
    Posts
    77

    low index distinct keys

    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

  2. #2
    Join Date
    Mar 2002
    Posts
    534
    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.

  3. #3
    Join Date
    Sep 2000
    Posts
    77
    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.
    Thanks

  4. #4
    Join Date
    Oct 2005
    Location
    Indianapolis
    Posts
    100
    stored outlines work for me
    "False data can act only as a distraction. Therefore. I shall refuse to perceive you." - Bomb #20

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    drop the index?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  6. #6
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,968
    Quote Originally Posted by slimdave
    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.
    this space intentionally left blank

  7. #7
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Quote Originally Posted by gandolf989
    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.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  8. #8
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Quote Originally Posted by jmodic
    Because the query will not be able to use this ineficient index and will be forced to use FTS.

    ka-ching!
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  9. #9
    Join Date
    Oct 2005
    Location
    Indianapolis
    Posts
    100
    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

  10. #10
    Join Date
    Sep 2000
    Posts
    77
    Thanks to everyone. I will explore stored outlines option.
    Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width