DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: Index selectivity

  1. #1
    Join Date
    Sep 2000
    Posts
    77

    Index selectivity

    I have table with 1 million rows and it has index on one of the columns which is a foreign key. After analyze, distinct_keys value for index on this column is 1000. Index selectivity is not even 1%. If I drop this index, query response time is very bad. I thought index selectivity plays a significant role in response time, but the result from above scenario is quite opposite. Is my analysis is correct? Thanks in advance for any advice.
    Thanks

  2. #2
    Join Date
    Feb 2003
    Location
    Leeds, UK
    Posts
    367
    Block selectivity. This explains it better than I can:

    http://www.hotsos.com/e-library/abstract.php?id=5

  3. #3
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    What is the number of blocks used by the table?

    What is the CF value for the index?

    Tamil
    Last edited by tamilselvan; 02-23-2006 at 10:43 PM.

  4. #4
    Join Date
    Sep 2000
    Posts
    77
    Number of blocks used by table = 72,500
    Clustering Factor of index = 1,090,000
    Thanks

  5. #5
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    ouch, no oracle running the show at hotsos? link failed with

    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '://www.hotsos.com/e-library/abstract.php?id=5' at line 1
    I'm stmontgo and I approve of this message

  6. #6
    Join Date
    Feb 2003
    Location
    Leeds, UK
    Posts
    367
    Not sure what's going on, but paste the link into your browser, rather than clicking on it.

  7. #7
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Quote Originally Posted by CHALAVADI
    Number of blocks used by table = 72,500
    Clustering Factor of index = 1,090,000
    Does your predicate have equality or range scan?

    Tamil

  8. #8
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    In fact, what's the actual query?
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  9. #9
    Join Date
    Sep 2000
    Posts
    77
    Here is my query. It is very simple query.
    Select * from daily_transaction
    where product_id = 23 ; -- product_id is the foreign key from product definition table.
    Thanks

  10. #10
    Join Date
    Nov 2002
    Location
    Mooresville, NC
    Posts
    349

    Arrow

    What is your explain plan now and is your table analyzed with compute statistics. Generally speaking if product_id column has 1000 distinct values while your table has 1 Million records then the index scan should take lesser time than the non index scan.So since you deleted the index it's going for full table scan now and execution time is more now. Does this explanation Ok or your question is bit different.
    http://www.perf-engg.com
    A performance engineering forum

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