Re: Fragmentation Strategies

jvemo@cyberspace.com (Jon C 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. We also do a great deal of fragmentation by expression using substrings. Performance is no problem. However, in an informix Performance Tuning class, it was suggested that performance might be better by creating a column to use for fragmentation purposes. It was implied that this would reduce the overhead of the searching on sbustrings. We have not tried this as of yet. WARNING!!! This week we upgraded from 7.13 to 7.14. There is a bug that first appears in 7.14 using this fragmentation method(substrings). The bug id is 70104. The optimizer can and will eliminate all fragments on certain queries and give you a result of no rows found. This bug does is not fixed until 7.23. This bug does not exist in 7.13. Barry Leb National Linen Service 1420 Peachtree Street MS #230 Atlanta, GA 30309 e-mail: barryleb@mindspring.com