Update Statistics - again

I see much speech on "update statistics", now I confused. =20 I provide sample from my data-base: =20 =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D Distribution for psoft8.ps_proj_resource.business_unit =20 Constructed on 2005-06-11 =20 High Mode, 0.500000 Resolution --- DISTRIBUTION --- ( ) 1: ( 4, 2, GREEN) --- OVERFLOW --- =20 1: ( 10226, SILVER) 2: ( 31502, BLUE) 3: ( 14552, ORANGE) 4: ( 7231, YELLOW) 5: ( 340089, BROWN) 6: ( 11365, PURPLE) 7: ( 130252, TAN) 8: ( 2668972, SALSA) 9: ( 27886, WHITE) 10: ( 13673, MUSTARD) 11: ( 74385, RED) 12: ( 119002, BLACK) 13: ( 17431, PINK) =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =20 In my sample, everything in overflow. I should take this to be bery bad thing then? =20 much thank you

Jerry, Overflow is not bad. In the DISTRIBUTION section you will see something that looks like this, I'm labeling the columns A, B, C and D A B C D <-- this won't be in your output 1:( 868317, 70, 75) 2:( 868317, 24, 100) A = distribution bin number B = # rows represented by this bin C = number of unique values in this bin D = highest value in this bin in the OVERFLOW section you'll see A B C <- nor will this 1:( 779848, 75) 2:( 462364, 100) A = overflow bin number B = number of rows with the value in column C C = the value stored in this overflow bin Any value that appears in the table more than 25% of the distribution bin size will be put into an overflow bin. This is done to help the optimizer handle data skew. BTW - distribution bin size is determined by number of bins / rows in the table and the number of bins is determined by confidence/resolution. For your data it just looks like the business_unit column of the ps_proj_resource table isn't very unique and everything ends up in the overflow buckets. This overflow helps you out actually, the optimizer has exact numbers for the business_unit column instead of the estimates it would use if the data were represented by distributions. Check out the following links for more information. http://www.iiug.org/waiug/present/meeting200501/UpdateStatistics2003.ppt (power point) http://www-106.ibm.com/developerworks/db2/zones/informix/library/techarticle/mil ler/0203miller.html Salsa is a pretty popular color. Green, not so much. Hope this helps, someone please correct me if I'm wrong, Andrew ----- Original Message ----- From: "Hamilton, Jerry" <hamiltoj@fleishman.com> To: <ids@iiug.org> Sent: Thursday, June 16, 2005 9:01 AM Subject: Update Statistics - again [5166] > I see much speech on "update statistics", now I confused. > =20 > I provide sample from my data-base: > =20 > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D > Distribution for psoft8.ps_proj_resource.business_unit > =20 > Constructed on 2005-06-11 > =20 > High Mode, 0.500000 Resolution > > --- DISTRIBUTION --- > ( ) > 1: ( 4, 2, GREEN) > > --- OVERFLOW --- > =20 > 1: ( 10226, SILVER) > 2: ( 31502, BLUE) > 3: ( 14552, ORANGE) > 4: ( 7231, YELLOW) > 5: ( 340089, BROWN) > 6: ( 11365, PURPLE) > 7: ( 130252, TAN) > 8: ( 2668972, SALSA) > 9: ( 27886, WHITE) > 10: ( 13673, MUSTARD) > 11: ( 74385, RED) > 12: ( 119002, BLACK) > 13: ( 17431, PINK) > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= > > =20 > In my sample, everything in overflow. I should take this to be bery bad > thing then? > =20 > much thank you > > >

On a key such as this one with relatively few values, this will happen. The reason is that the resolution is 0.5 which equates to 200 buckets but there are only 14 unique values. The engine divides the number of rows by the number of buckets to determine how many records should be recorded in each bucket. It then tries to assign values to each bucket to try to get each bucket to contain that number of rows, but any value that exists in more than a certain percentage of the number of rows in the bucket (IB 25%) is extracted to an overflow bucket by itself and the buckets rebalanced. In your case, because there are so few unique values, all but GREEN are such outliers. Should not be a problem, however, I would avoid beginning any index key with this column as its filter value is very low resulting in a deeper less efficient index than one that begins with a better filter column. Art S. Kagel ----- Original Message ----- From: Jerry Hamilton <hamiltoj@fleishman.com> At: 6/16 11:59 I see much speech on "update statistics", now I confused. =20 I provide sample from my data-base: =20 =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D Distribution for psoft8.ps_proj_resource.business_unit =20 Constructed on 2005-06-11 =20 High Mode, 0.500000 Resolution --- DISTRIBUTION --- ( ) 1: ( 4, 2, GREEN) --- OVERFLOW --- =20 1: ( 10226, SILVER) 2: ( 31502, BLUE) 3: ( 14552, ORANGE) 4: ( 7231, YELLOW) 5: ( 340089, BROWN) 6: ( 11365, PURPLE) 7: ( 130252, TAN) 8: ( 2668972, SALSA) 9: ( 27886, WHITE) 10: ( 13673, MUSTARD) 11: ( 74385, RED) 12: ( 119002, BLACK) 13: ( 17431, PINK) =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =20 In my sample, everything in overflow. I should take this to be bery bad thing then? =20 much thank you

Dear Jerry, no, this is not bad. It simply means , that in column business_unit in table ps_proj_resource there are 4 entries in the value range up to the value 'GREEN' , with two distinct values (could be ' ' and 'GREEN', but we don't know this) At the same time there are 340089 rows with the value 'BROWN' Although BROWN falls in the range up to 'GREEN' this value is treated specially and not included in the ordinary distribution, as the number of rows that qualify 'BROWN' is very high compared to all other values in the range up to 'GREEN' . If the value 'BROWN' was included into the range , the distribution would be misleading, as it would suggest 340093 rows in the range with three distinct values. There would be no information that actually 340089 out of the 340093 are 'BROWN' . To avoid this misleading staistical information IDS stores the statistics for such values separately as OVERFLOWs This allows to optimizer to better estimate the number of rows returned. Similar for all other OVERFLOW values. With best regards Tilman -- Tilman Model-Bosch IBM Data Management Solutions, Informix Advanced Support c\\\\o SAP AG TECHDEV 05 Neurrotstr.16 69190 Walldorf forum.subscriber@iiug.org wrote on 16/06/2005 16:01:34: > I see much speech on "update statistics", now I confused. > =20 > I provide sample from my data-base: > Distribution for psoft8.ps_proj_resource.business_unit > =20 > Constructed on 2005-06-11 > =20 > High Mode, 0.500000 Resolution > > --- DISTRIBUTION --- > ( ) > 1: ( 4, 2, GREEN) > > --- OVERFLOW --- > =20 > 1: ( 10226, SILVER) > 2: ( 31502, BLUE) > 3: ( 14552, ORANGE) > 4: ( 7231, YELLOW) > 5: ( 340089, BROWN) > 6: ( 11365, PURPLE) > 7: ( 130252, TAN) > 8: ( 2668972, SALSA) > 9: ( 27886, WHITE) > 10: ( 13673, MUSTARD) > 11: ( 74385, RED) > 12: ( 119002, BLACK) > 13: ( 17431, PINK) > In my sample, everything in overflow. I should take this to be bery bad > thing then? > much thank you > >