I like to reorg my database and to compress table and indexes. My database is a very big one, so I need to reorg only table which need to be compressed.
I phone SYBASE support and they say me I have to look at table or index cluster ratio to know if a table or an index need to be reorganized.
They say to use the optdiag command to know this cluster ratio.
I test this command and saw that the result is very complex. And it'll be very difficult to extract tables name from log file.
1) Is there another way to list tables and index to reorg ?
2) Is there a web site where I could find some administration scripts (my database is on Windows) ?
Here is a handy script that I wrote to give you an idea of the state of your tables. Simply run it in the DB in question. If you then order the output, you can see which tables (or indexes) have the most deleted pages, or the greatest ratio of empty pages to pages allocated etc. You can also use this to write a script which generates reorg commands to be run based on smallest tables first... if doing reorg rebuild the database requires at least the size of each table in free space to complete, so by doing the smallest tables first you have then freed up a lot of space before you get to the larger tables.
I hope this helps... Regards, Carl.
select tbl.name "Table Name",
WHEN stat.indexheight > 0 THEN idx.name
END "Index Name",
stat.indexheight "Idx Height",
stat.leafcnt "Idx Leaf Cnt",
Convert(Numeric(27,3), stat.leafcnt * 2.0000 / 1024.0000) "Idx Leaf Sz",
stat.pagecnt "Page Cnt",
Convert(Numeric(27,3), stat.pagecnt * 2.0000 / 1024.0000) "Sz Pgs",
stat.emptypgcnt "Empty Pages",
Convert(Numeric(27,3), stat.emptypgcnt * 2.0000 / 1024.0000) "Sz Empty Pgs",
WHEN stat.pagecnt > 0 THEN Convert(Numeric(23,3), ((Convert(Numeric(23,3), stat.emptypgcnt) / Convert(Numeric(23,3), stat.pagecnt)) * 100.00))
END "Ratio EP/P",
stat.rowcnt "Row Cnt",
stat.delrowcnt "Deleted Row Cnt",
stat.forwrowcnt "Forwarded Row Cnt"
from sysobjects tbl,
where tbl.type = "U"
and tbl.id = stat.id
and idx.indid = stat.indid
and idx.id = tbl.id
order by tbl.name, idx.name