Re: Fragmentation Strategies

Just wanted to add a 'me-too.' I had a situation where the only suitable column for fragmentation was an account number, which for some reason (which predates me) is a char(10). The last digit is a checksum, and there is a very wide range of values with no rhyme or reason, so I wound up fragmenting on donor_acct[9,9] in '0', '1', etc. A little simple testing revealed that this was pretty fast, even though it was very, very ugly. But converting this to a numeric will have to wait on getting this silly AS400 out of here...anybody need a boat anchor? David Jon Vemo wrote... >At 08:01 PM 4/21/97 -0600, stiglich@promarkone.com wrote: >>Hello, >> >> We are considering using fragmentation by expression for some >>of our tables. Here is our problem: we want to fragment by the >>1st two char's of the column. i.e. col_1[1,2] = "XY". Does anyone >>have any thoughts on this? Would this hinder performance? >> >We did a very similar thing. We partitioned our tables on column which >is char(9), but used for the first 4 >characters in the fragment >expression (i.e.fragment by expression ((colA <= '0052' ) AND (colA >= >'0001' ) ) in dbspace1 , >((colA <= '0240' ) AND (colA > '0052' ) ) in dbspace2 , >etc.... > >This appears to work quite well, with minimal overhead. Because this is >a char field, string compares are necessary, but performing against just >the first 4 was OK (performance wise)....I wouldn't recommend going much >beyond 4 though. >We did some comparisons of the this scheme, versus a mod function on an >integer, and found no significant performance differences either way. But >since we selected the above scheme, we can now achieve fragment elimination >in queries (since colA happens to be part of a key), which would not have >been possible had we selected a mod function on what would likely have had >to be an added integer/serial column. >>Also, if you do not use the remainder clause, what will happen to >>rows that do not meet the fragmentation criteria? Could this be an >>off-handed way of maintaining data integrity? >Maybe someone can correct me if I'm wrong, but I believe an error will be >returned to the application, indicating that in insert operation failed. Correct. David Coburn World Vision US