I'm not sure if this will help cinch the indexing being the culprit or not, but I pulled just the TransIDs out (into a table by themselves) and then tried to do a SELECT * FROM ACCT.Services WHERE TransID = ######### AND ACCT.Dept = 482 to pull specif TransID rows. I spot checked a few that corresponded to the results in the SELECT COUNT(*), TransID, ActnCode, Comment WHERE ACT.Dept = 482 query and have already hit about three that didn't have any results. I'm assuming at this point it is because they're somehow part of the index, but not actual "active rows". My SQL-brain can't comprehend how I can have a query display a result but not find the detail that corresponds with that row id...but I'm sure it's my lack of Cache-101 knowledge...
Thanks for quick reply, David. I wanted to double-check a few things based on your recommendations.
No, there are no grouping or sorting functions in the syntax. The only filtering I'm using against the data is the Dept = 483.
In my additional testing, I tried some additional syntax and noticed the following:
* SELECT COUNT(*) FROM ACCT.Services WHERE ACCT.Dept = 483 yields 1,085,776.
* SELECT COUNT(*), TransID FROM ACCT.Services WHERE ACCT.Dept = 483 AND TransID IS NULL yields 1,085,776.
* SELECT COUNT(*), TransID, ActnCode FROM ACCT.Services WHERE ACCT.Dept = 483 yields 1,085,776.
* SELECT COUNT(*), TransID, ActnCode, Comment WHERE ACT.Dept = 482 yields a result of 1,090,324.
* SELECT COUNT(*), TransID, ActnCode, Comment...[All the rest of the columns; too long to included here] WHERE ACT.Dept = 482 yields a result of 1,090,324.
I did some additional SELECT testing with other fields and consistently saw that if I only added index fields (e.g. TransID and ActnCode - either both or separately) I get the same 1,085,776 result. If I add a non-indexed field (e.g. Comment, TransUserID) to the SELECT listing, I get the higher (1,090,324) result. This holds true in both of those cases whether I have the COUNT(*) included in the SELECT statement or not.
Thanks for the quick reply, Dmitry. No - there haven't been any indexes added or changed at all. I'll take a look at the documentation link you provided. Thanks!
Log in or create a new account to continue