data distribution question

Floyd Wellershaus — Wed, 2 Aug 2006 15:08:08 -0700 (PDT) — source: Usenet: comp.databases.informix

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: ( 507669, 305889) 9: ( 496176, 324590) 10: ( 545764, 335032) 11: ( 575454, 337654) 12: ( 662279, 338354) 13: ( 655045, 357904) 14: ( 1352666, 359863) 15: ( 684749, 368087) 16: ( 521739, 368452) 17: ( 687000, 371474) 18: ( 528608, 371973) 19: ( 679561, 388584) 20: ( 538942, 392313) 21: ( 494174, 393604) 22: ( 546620, 403774) … … ======================== -<<Floyd Wellershaus>>- Database Administrator Unix Administrator email: fwellers@yahoo.com Home: 703-430-0805 Cell: 703-477-6045 ======================== http://www.one.org/

↪ replying to Floyd Wellershaus

John Carlson — Thu, 03 Aug 2006 00:36:44 -0400 — source: Usenet: comp.databases.informix

On Wed, 2 Aug 2006 15:08:08 -0700 (PDT), Floyd Wellershaus <fwellers@yahoo.com> wrote: >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 ? How many rows are in the table? JWC > >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: ( 507669, 305889) > 9: ( 496176, 324590) > 10: ( 545764, 335032) > 11: ( 575454, 337654) > 12: ( 662279, 338354) > 13: ( 655045, 357904) > 14: ( 1352666, 359863) > 15: ( 684749, 368087) > 16: ( 521739, 368452) > 17: ( 687000, 371474) > 18: ( 528608, 371973) > 19: ( 679561, 388584) > 20: ( 538942, 392313) > 21: ( 494174, 393604) > 22: ( 546620, 403774) >' >' > > > > > > > > > > >======================== >-<<Floyd Wellershaus>>- >Database Administrator >Unix Administrator > > > >email: fwellers@yahoo.com > > >Home: 703-430-0805 > > >Cell: 703-477-6045 >======================== > > >http://www.one.org/

↪ replying to John Carlson

Floyd Wellershaus — Thu, 3 Aug 2006 02:45:43 -0700 (PDT) — source: Usenet: comp.databases.informix

>>How many rows are in the table? << Almost 400 million. ======================== -<<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: John Carlson <jwcarlson1@yahoo.com.invalid> To: informix-list@iiug.org Sent: Thursday, August 3, 2006 12:36:44 AM Subject: Re: data distribution question On Wed, 2 Aug 2006 15:08:08 -0700 (PDT), Floyd Wellershaus <fwellers@yahoo.com> wrote: >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 ? How many rows are in the table? JWC > >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: ( 507669, 305889) > 9: ( 496176, 324590) > 10: ( 545764, 335032) > 11: ( 575454, 337654) > 12: ( 662279, 338354) > 13: ( 655045, 357904) > 14: ( 1352666, 359863) > 15: ( 684749, 368087) > 16: ( 521739, 368452) > 17: ( 687000, 371474) > 18: ( 528608, 371973) > 19: ( 679561, 388584) > 20: ( 538942, 392313) > 21: ( 494174, 393604) > 22: ( 546620, 403774) > > > > > > > > > > > > >======================== >-<<Floyd Wellershaus>>- >Database Administrator >Unix Administrator > > > >email: fwellers@yahoo.com > > >Home: 703-430-0805 > > >Cell: 703-477-6045 >======================== > > >http://www.one.org/ _______________________________________________ Informix-list mailing list Informix-list@iiug.org http://www.iiug.org/mailman/listinfo/informix-list

↪ replying to Floyd Wellershaus

Art S. Kagel — Thu, 03 Aug 2006 15:30:38 -0400 — source: Usenet: comp.databases.informix

Floyd Wellershaus wrote: > 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 <SNIP> The overflows represent less than 30% of the total number of rows, so unless only two rows fall on a page, changes are an indexed scan will indeed tend to be faster for most queries affecting only a subset of the data. Art S. Kagel

↪ replying to Floyd Wellershaus

Art S. Kagel — Thu, 03 Aug 2006 15:30:38 -0400 — source: Usenet: comp.databases.informix

Floyd Wellershaus wrote: > 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 <SNIP> The overflows represent less than 30% of the total number of rows, so unless only two rows fall on a page, changes are an indexed scan will indeed tend to be faster for most queries affecting only a subset of the data. Art S. Kagel

Related threads

Your privacy choices