Re: Re[2]: fragmented system tables

OK I'm dense. Still it will not help unless he also drops the database and recreates it. However, my main point still stands: This is not necessary, the fragmentation of the system tables in a database has NO EFFECT ON PERFORMANCE! This is not off the top of my head, I have had extensive discussions with the folk who rewrote the optimizer and search engines for 7.3 and 7.24 and their take is that the Data Dictionary Cache, there since 7.10, eliminates any dependence on the system tables on disk once the dictionary entry for a table has been read in. I have also seen it work. We had a situation where the optimizer was choosing the wrong index because of the depth figure in sysindexes. We modified that value and no effect until the engine was bounced because the depth value in the Data Dictionary Cache was being used. ---- Original Msg from: David Ashby <david.ashby@workcover.nsw.gov.au> At: 5/13 20:06 Art, Richard means do an alter table. David Ashby ______________________________ Reply Separator _________________________________ Subject: Re: fragmented system tables Author: <kagel@bloomberg.com> at WCA-INET Date: 13/5/98 6:18 PM Richard Stanford wrote: > > Sven Tolkemit schrieb: > > > we discovered, that the system tables (systables,sysindexes, etc.) on > > our Online 7.23.UC1 are very fragmented. We think, this leads into > > Volker Fraenkle wrote: > > > Yes, there is. You have to unload and drop the tables. After, you can > > create it with "first size" and "next size" (to prevent your tables from > > high fragmentation) and load the tables. > > John Carlson wrote: > > > Unless something has changed, should you do this with system tables? > > I've never tried to drop and recreate a system table before. > > > > Last I heard, this is a big no-no. > > What you can do is take a dbexport, go in and modify the schema to set the > NEXT size for all the system tables at the top. This way, they all go into > two extents, but hopefully no more. I never bothered to do any serious > benchmarking of this, and usually don't do it, but it is (as far as I know) > the only safe way of reducing extent sizes in system tables. > > Has anyone benchmarked this? I'd imagine better results in larger > systems (1000+ tables, etc). This won't work either since the system tables are created along with the database and are not contained in the schema file that dbexport produces. Art S. Kagel