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

Anything seems to be possible at the end of the work day, especially on Monday.

Try looking at the problem again tomorrow morning.

What makes you think that the selectivity is greater than 100%?

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%

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

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:
And "Rows selected" is:
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.

Hi. i tried to test what n_i has suggested , and I found its true . but i couldnt get the reason why??

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

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.

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.

but isnt that good that everything is being picked by indexes...i dont see any harm in rows selected is greater than rows read..

but isnt that good that everything is being picked by indexes...i dont see any harm in rows selected is greater than rows read..

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.

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.