Re: Fragmentation Strategies

In article <5jin79$2du@cssun.mathcs.emory.edu>, Jon C Vemo <jvemo@cyberspace.com> writes >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. > Agreed. >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. > That is correct (from the DBA course I did at Informix UK). >Hope this helps. >Jon > >---------------------------------------------------------------------------- >--- >Jon C. Vemo "Life is like a dogsled team, if you ain't the >jvemo@cyberspace.com lead dog the scenery never changes." >---------------------------------------------------------------------------- >--- > -- David Williams