Expression based fragmentation
My current DB design stores 30 days of data, each in a separate table. To search we first search the table list for the table and then search the given table. New requirements is to store 180 days and single table so users don't have to search the list of tables. I'm testing IDS 7.3 on HP K class (AUTORAID disks) running HPUX 10.20. I estimate 2 gig per day to store our daily transactions and three indexes. I do know that the larger the table grows, the slower the insert rate will become, almost reverse exponentially. I'd like to use expression based fragmentation to mimick the original design - I'm hoping that I'll see a performance boost when inserting into a new day. Has anyone done this that could give me some tips and/or verify my ideas? TIA JLK
JLK62 wrote: > > My current DB design stores 30 days of data, each in a separate table. To > search we first search the table list for the table and then search the given > table. New requirements is to store 180 days and single table so users don't > have to search the list of tables. I'm testing IDS 7.3 on HP K class (AUTORAID > disks) running HPUX 10.20. I estimate 2 gig per day to store our daily > transactions and three indexes. I do know that the larger the table grows, the > slower the insert rate will become, almost reverse exponentially. I'd like to > use expression based fragmentation to mimick the original design - I'm hoping > that I'll see a performance boost when inserting into a new day. > > Has anyone done this that could give me some tips and/or verify my ideas? Your idea is valid and with in-place alter it is even practical to add a new fragment and drop off older ones no longer needed. You will need to define a dbspace for each fragment you plan to maintain so for 180 daily fragments you will need 182 dbspaces at least (one for each of the 180 current day fragments one for the new day to come and you should have a REMAINDER fragment to trap garbage and in case you do not get the chance to create the new fragment in time for the day's work to begin). Art S. Kagel
Be careful about using REMAINDER fragments. This hurts fragment elimination, and will adversely affect performance (albeit not as dramatically as not fragmenting at all). If you can avoid a REMAINDER fragment, you should. Only use it if business logic dictates you must have it. Art S. Kagel wrote in message <36F7F765.311C@bloomberg.net>... >JLK62 wrote: >> >> My current DB design stores 30 days of data, each in a separate table. To >> search we first search the table list for the table and then search the given >> table. New requirements is to store 180 days and single table so users don't >> have to search the list of tables. I'm testing IDS 7.3 on HP K class (AUTORAID >> disks) running HPUX 10.20. I estimate 2 gig per day to store our daily >> transactions and three indexes. I do know that the larger the table grows, the >> slower the insert rate will become, almost reverse exponentially. I'd like to >> use expression based fragmentation to mimick the original design - I'm hoping >> that I'll see a performance boost when inserting into a new day. >> >> Has anyone done this that could give me some tips and/or verify my ideas? > >Your idea is valid and with in-place alter it is even practical to add >a new fragment and drop off older ones no longer needed. You will need >to define a dbspace for each fragment you plan to maintain so for 180 >daily fragments you will need 182 dbspaces at least (one for each of >the 180 current day fragments one for the new day to come and you >should have a REMAINDER fragment to trap garbage and in case you do not >get the chance to create the new fragment in time for the day's work >to begin). > >Art S. Kagel