Re: Index clustering and performance

Land, Todd wrote: > > I remember a thread from long ago . . . can you refresh my memory? > > My database performance is declining, and my DBA wants to dump and reload > the database, hoping that will help. > > Isn't there something I can do to my indices (re-cluster?) to regain > performance? > > The OnLine Administrator's Guide (admitedly an older copy) doesn't say > anything about perfomance except for initial tuning parameters. Help! > (We're using OnLine 7.x) Hi, sometimes, when you have a lot of small extents for a table, it's a good idea to unload the table, drop it and reload the table. The same procedure will be performed internally when you create a clustured index. But if all the tables inside a specific dbspace have a lot of small extents it is a good idea to unload all the tables, drop the dbspace and create it again. Afterwards you can reload all the tables, one by one. You can avoid this by creating a well calculated first extent for each table. If you don't have RAID 5 disks, try to detach the indexes from the table ( create index ... in anotherdbspace ). Monitor the growth of your tables. Compare the number of pages used to the number of pages allocated ( after each UPDATE STATISTICS you can select this information from systables ). Collect this information in a separate table and store the current date. ( It's best done by using a stored procedure ). At least nobody can tell if two or more extents will result in a lack of performace, but if you have only one extent it should be good enough. Bye Stefan. stefan@weideneder.de