Re: Fragmentation Strategies

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. 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." ---------------------------------------------------------------------------- ---