RE: data distribution question

Where do you work? ________________________________ From: informix-list-bounces@iiug.org [mailto:informix-list-bounces@iiug.org] On Behalf Of Floyd Wellershaus Sent: Thursday, August 03, 2006 3:58 AM To: Andrew Ford; informix-list@iiug.org Subject: Re: data distribution question Thank you for the reply. Let me try and understand. Taking bin 3, out of 1.9 million rows in it, there are only 9.9k rows that are unique. The rest of the rows are duplicate. The range for that bin contains values between 25763 and 35887. Since I don't see any values in that range, in the overflow, then I guess I can assume that there are no major "skewers" of the value in that bin, and that there is just a very high percentage of values that are duplicated. Is that right so far ? So doesn't that mean that the index would have to do a lot of extra reads to find a value ? Thanks for your patience. ======================== -<<Floyd Wellershaus>>- Database Administrator Unix Administrator email: fwellers@yahoo.com Home: 703-430-0805 Cell: 703-477-6045 ======================== http://www.one.org/ ----- Original Message ---- From: Andrew Ford <andrewford@austin.rr.com> To: Floyd Wellershaus <fwellers@yahoo.com>; informix-list@iiug.org Sent: Wednesday, August 2, 2006 10:50:32 PM Subject: Re: data distribution question By 25% duplicate values do you mean you have 100,000 rows in the table and 25,000 have a value of "A", 25,000 have a value of "B", 25,000 have a value of "C" and 25,000 have a value of "D"? If so, I would think that an index on that column might not serve much purpose unless it was for a foreign key. If you mean you have 75% unique values (or almost unique values) and 25% of the data has the same value then the optimizer will (hopefully) choose an index when you are looking for a few rows living in the 75% of the data that is unique and might choose a scan if you're looking for a few rows in the 25% of the data that is a duplicate and probably will choose a scan if you are looking for a lot of rows in the table. Based on your distributions it looks like your data has some good uniqueness. If the value for that column isn't defined in the overflow buckets you can be pretty sure you only have 0.5% duplicates based on the data from bin #3. You are right, the bins don't have anything to do with index traversal. The optimizer uses the data distributions to make a guess as to which access method will be the best: one of the many indexes you have created or a sequential scan. Once the decision is made you either traverse the index tree looking for data or sequentially scan the table. Andrew ----- Original Message ----- From: Floyd Wellershaus <mailto:fwellers@yahoo.com> Newsgroups: comp.databases.informix To: Ford, Andrew G <mailto:Andrew_G_Ford@homedepot.com> ; informix-list@iiug.org Sent: Wednesday, August 02, 2006 7:29 PM Subject: Re: data distribution question Hmm. Thanks. I like the way you explain it. It helps me to see it more clearly. I'm still a bit confused though. I was taught that a general rule of thumb is that if a table has more than about 25% duplicate values, it would take too many index reads to make the index worthwhile. In your scenario, the index would seem to narrow down to 199 values, but isn't that only if the index knows to go to the correct bin in the first place ? Maybe they do, but I don't know. I didn't think the bins had anything to do with index traversal to find the value. ======================== -<<Floyd Wellershaus>>- Database Administrator Unix Administrator email: fwellers@yahoo.com Home: 703-430-0805 Cell: 703-477-6045 ======================== http://www.one.org/ ----- Original Message ---- From: "Ford, Andrew G" <Andrew_G_Ford@homedepot.com> To: Floyd Wellershaus <fwellers@yahoo.com>; informix-list@iiug.org Sent: Wednesday, August 2, 2006 6:38:34 PM Subject: RE: data distribution question bin# # rows in bucket # unique values highest value in this bin 2: ( 1973055, 9364, 25763) 3: ( 1973055, 9915, 35887) Doesn't look too bad. You've got 1973055 rows in bin #3 representing values between 25764 and 35887 and have 9915 unique values in this range. Calculate this out and you have 1973055 / 9915 = 199 rows for each value between 25764 and 35887. If I'm looking for a row with value of 28742 this index would narrow the search down to approx. 199 rows. Now, if I am looking for a row with even numbered values a sequential scan would be better. It all depends on what info you are trying to get to. Check out this link for some good info on distributions and update statistics by some guy,http://www-1.ibm.com/partnerworld/pwhome.nsf/pi/eac_index_bizinformix_08 mar.html (Did I get it right John?) Andrew Ford -----Original Message----- From: informix-list-bounces@iiug.org [mailto:informix-list-bounces@iiug.org] On Behalf Of Floyd Wellershaus Sent: Wednesday, August 02, 2006 6:08 PM To: informix-list@iiug.org Subject: data distribution question With the below values for the mbntfhst_token column, which is being joined to in the query, I would think these distributions show way too much duplication for an index to be effective. Yet, when I force a scan on that table, the cost goes way way up. What am I missing here ? Thank you. Floyd 1: ( 1973055, 13906, 14381) 2: ( 1973055, 9364, 25763) 3: ( 1973055, 9915, 35887) 4: ( 1973055, 5803, 41746) 5: ( 1973055, 4832, 46654) 6: ( 1973055, 5731, 52437) 7: ( 1973055, 5548, 58053) 8: ( 1973055, 6483, 65065) 9: ( 1973055, 11890, 77038) 10: ( 1973055, 10660, 88268) 11: ( 1973055, 5761, 94404) 12: ( 1973055, 4753, 99639) 13: ( 1973055, 8261, 107909) 14: ( 1973055, 7536, 116822) 15: ( 1973055, 4875, 123111) 16: ( 1973055, 3135, 127073) 17: ( 1973055, 5571, 134148) ... ... Also, take a look at the overflow bucket for that column. 1: ( 1509940, 206221) 2: ( 499152, 249386) 3: ( 568126, 250016) 4: ( 522169, 276801) 5: ( 495874, 277207) 6: ( 564699, 305435) 7: ( 584721, 305658) 8: (