As usual, there are considerations:
1) How active is your database? Are there a lot of inserts, updates and deletes? Or is it mostly static data? R:BASE maintains the indexes as these activities occur. Where you will see benefit from a PACK INDEX is on tables with frequent INSERTs, DELETEs, and/or UPDATEs to indexed values. (UPDATEs to non-indexed values have no bearing on the issue.) 2) Do queries seem to slow down over time on certain tables? Are you running with MANOPT OFF? Queries that let R:BASE perform the optimization (MANOPT OFF) depend upon the index statistics that the engine creates when the index is created or packed. Thing is, these statistics are only updated with a PACK (or RELOAD, or UNLOAD/RUN.) So over time queries against a table with many changes to indexed columns can slow down since the statistics the engine uses to optimize the query are stale. Here, a PACK INDEX will definitely show an improvement. If you're not changing indexed values, then a PACK INDEX will have no impact. >From "Inside R:BASE" by Anne Gillihan: "To determine the 'best' index, the optimizer looks at the duplication and adjacency factors stored in the SYS_INDEXES table for each index. The duplication and adjacency factors are only calculated when an index is built collectively over all rows. It is not calculated as each row of data is added. To update the duplication and adjacency factors, use the PACK KEYS command to rebuild indexes only. The index with the lowest duplication factor, the most unique, is generally considered the 'best' index." That discussion was in the context of 4.5; the PACK INDEX FOR tablename command was added in 7.x and will also update the statistics. We have one database (consolidated sales history) with over a million rows in the detail table. Rows are almost never UPDATEd or DELETEd. The only time we rebuild the database is when we change the structure. Performance of the application does not suffer over time, mostly because we run most queries with MANOPT ON. That is, we're telling the engine how to optimize the query. Our live environment databases have over 340 tables, 3000 columns and 440 indexes. We typically rebuild these 3-4 times a year, or on an as-needed basis. "As-needed basis" is when we observe that either something has slowed terribly or, on very rare occasions, an index on a table gets corrupted and no amount of PACK INDEX will fix it. (Note: this occurs on tables with several thousand rows added and deleted each month. We could probably DROP and CREATE the index, but that requires exclusive access, and since we have to get exclusive access we might as well do a complete rebuild anyway.) Emmitt Dove Manager, DairyPak Business Systems Evergreen Packaging, Inc. [EMAIL PROTECTED] [EMAIL PROTECTED] (203) 643-8022 From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Marc Sent: Wednesday, July 16, 2008 9:32 AM To: RBASE-L Mailing List Subject: [RBASE-L] - How often should you Pack Index Hi We do a Unload all once a month to rebuild the database but should I Pack the Indexesonce per week? Thanks Marc
