Results 1 to 14 of 14
  1. #1
    Join Date
    Nov 2002
    Location
    Omaha, NE
    Posts
    25

    Unanswered: Record Selectivity > 100%?

    When taking a database snapshot I have two databases showing their record selectivity being greater than 100% - how is this possible?

    Rows deleted = 39389
    Rows inserted = 50490
    Rows updated = 61096
    Rows selected = 58807271
    Rows read = 356136828

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Anything seems to be possible at the end of the work day, especially on Monday.

    Try looking at the problem again tomorrow morning.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    What makes you think that the selectivity is greater than 100%?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    Nov 2002
    Location
    Omaha, NE
    Posts
    25
    Ha! This has been occurring for quite a while and I haven't found anything in my research so it was time to post here Here's the snapshot for yesterday...

    Rows deleted = 290529
    Rows inserted = 368325
    Rows updated = 57111
    Rows selected = 5232382
    Rows read = 3495144

    stolze, I'm saying that because the number of records selected/records read is greater than 100% (I've heard this calculation defined as 'selectivity' before)

    5232382/3495144 = 149%

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I believe it reflects index-only access cases. Index access is not included in "rows read" but the rows returned to the application are still counted in "rows selected".
    ---
    "It does not work" is not a valid problem statement.

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Now that would be a strange way to define "selectivity". Selectivity is the amount of rows that qualify a certain predicate. Whether those rows are read from the pages or not (like with index-only access) is irrelevant.

    If you look up "Rows read", you find:
    This is the number of rows read from the table.
    And "Rows selected" is:
    This is the number of rows that have been selected and returned to the application.
    I haven't tested this, but I'd believe that join operations could easily influence those numbers. I.e. a row may be read only once and joined to several other rows, causing it to be counted multiple times in the "Rows selected" section. Also, if you have some table functions that generate data on the fly, it could be that those rows are not counted in "Rows read". Add to that what n_i mentioned about index-only queries, and the whole issue seems less suspicious.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    Jan 2008
    Posts
    88
    Hi. i tried to test what n_i has suggested , and I found its true . but i couldnt get the reason why??

  8. #8
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    If a query is answered based on an index only, DB2 doesn't read any rows from a table. Hence, it must not count those rows as "Rows read".
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  9. #9
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    If I am not mistaken that number represent # of rows brought back by the app. If that number is greater then your total records then your app is doing a cartesian join. Generally it is not a good thing.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  10. #10
    Join Date
    Nov 2002
    Location
    Omaha, NE
    Posts
    25
    Thanks for the clarification on my terminology, Stolze. I have probably been confusing my terms over the years

    n_i, you're probably right on this one as this is for an ERP application and there seem to be indexes for every possible scenario. That might also explain why suddenly another of my databases which had a similar scenario is now performing terribly and this ratio has gone way out of whack. Thanks for your help.

  11. #11
    Join Date
    Jan 2008
    Posts
    88
    but isnt that good that everything is being picked by indexes...i dont see any harm in rows selected is greater than rows read..

  12. #12
    Join Date
    Jan 2008
    Posts
    88
    but isnt that good that everything is being picked by indexes...i dont see any harm in rows selected is greater than rows read..

  13. #13
    Join Date
    Nov 2002
    Location
    Omaha, NE
    Posts
    25
    Yes, it has been a very good thing however last week suddenly the number of Rows Read shot up while the number of Rows Selected stayed constant. So I had a very good scenario and then suddenly last week it got very bad. I was trying to look for an explanation of the scenario when things were good to see if I could find some reasons for what happened.

    I have a SQL statement that I think is the culprit for the incraese but it was running even when times were good. So with the explanation by n_i I'm going to explore why DB2 is no longer choosing index only access and instead performing a table scan.

  14. #14
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by azs0309
    but isnt that good that everything is being picked by indexes...i dont see any harm in rows selected is greater than rows read..
    That depends. Every index you add to your system must be maintained by DB2 on INSERT/UPDATE/DELETE operations. So if you system is biased to mostly queries, then adding indexing may well offset the additional overhead introduced by maintaining those indexes. If you have mostly data modifications, you have to carefully balance the amount of indexes you create.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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