Unused Indexes

S

Subhransu

Member
Hi All,
Recently we figured out that in some tables Index size is more than 10 GB. So we want to drop unused indexes from the DB. Would someone let me know if there are other ways to do it apart from XREF codes one by one?

Your help will be highly appreciated.

Thanks.
Subhransu
 
You can monitor _IndexStat, _UserIndexStat system tables for a while
Change
-baseindex 1
-indexrangesize 1000
server parameters to increase size of stat table.
 
you can also try to rebuild your index(s)
if the index increased due to program bug/error, it will not shrink by itself
try the PROUTIL with "-C idxbuild" command, it will compress/repair the index(s)
 
Where did the 10GB number come from?

Knowing that will help a lot in choosing the proper course of action.

One way to get ridiculously over-sized storage for indexes is to create all of your indexes in type 2 storage areas with 512 block clusters. So if you're just looking at the disk space that might be part of what you're seeing. Otherwise you ought to start by running "proutil dbname -C dbanalys > dbname.dba" and then reading the information about individual indexes to determine which ones are large and whether or not their size makes sense for your application. If the size shown by dbanalys doesn't correspond to the size you see on disk look to the cluster size.
 
You must log in or register to reply here.
Back
Top