Index distinct keys and performance
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Index distinct keys and performance

  1. #1
    Join Date
    Sep 2000
    Posts
    77

    Index distinct keys and performance

    I have a table with 30 million rows.
    One of the table's column is date column -- target_date not null. Non-unique Index has been created on this column.
    Data was populated into this column using TRUNC(SYSDATE) function.
    Daily volume of this table is around 50,000 rows.
    USER_INDEXES Distinct_keys column's value is 605.
    Index selectivity percentage is .002.
    Can someone please advise if the index on this column is good to have or not. Thanks in advance.
    Thanks

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Quote Originally Posted by CHALAVADI
    Can someone please advise if the index on this column is good to have or not.
    Noone can tell you that, not without knowing some other details. Like what kind of queries are you expecting to benefit from this index, what kind of DML operations are regularly performed on this table, what type of database this is (OLTP, DW) etc....

    In any case, if this turned out to be a good candidate for a B-tree index, I'd certanly consider using index compression on it.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Sep 2000
    Posts
    77
    Database is for an heavy OLTP application. Queries are based on target_date column with date range between 1 and 15 days.
    Thanks

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Whether the index is useful or not is going to depend on the query, and the range of dates that the query is selecting is critical. Much depends on the clustering factor of the index, whether you are using bind variables, the statistics on the table & column & index etc..

    I think that what you really need is a copy of Jonathan Lewis' book "Cost-based Oracle Fundamentals" to understand how the optimizer will work out whether to use an index or not, because it's a non-trivial topic.
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

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