Calculating Table & Index Usage in ASE
- one row for each clustered index ( ie no row for table if CI is available)
- one row for each nonclustered index,
- one row for each table that has no clustered index,
- one row for each table that contains text or image columns.
- one row for each function-based index or index created on a computed column.
For Caluluating the size for each index and table we can use below table
First Row (indid >1) : For NCI , we will calculate index and reserved usage as below , it will not contain table size.
Second Row (indid=0) : For a table only, we can calculate usage as specified in below table
Third Row (indid =1) : As we know, CI resides tigtly couple with table so its size will also include table size.
| indid | Data Pages | Index Page | Reserved Pages |
| >1(NCI) | data_pages(db_id(), tabid,indid) | reserved_pages(db_id(), tabid, indid) | |
| 0(TBL) | data_pages(db_id(), tabid,indid) | reserved_pages(db_id(), tabid, indid) | |
| 1(CI) | data_pages(db_id(), tabid,0) | data_pages(db_id(), tabid,1) | reserved_pages(db_id(), tabid, indid)+reserved_pages(db_id(), tabid, 0) |
For calulating the Table size
1. If the table has CI
Add All NCI usages for each columns with CI usages i.e. Calulate row 1st for all NCI and add all with 3 rd row .
2. If the table does not has CI :
Add All NCI usages for each columns with Table Usage i.e Calulate 1st row for all NCI and add all with 2nd row.
Check the Sybase Wiki @ sybasewiki.com
Categories: ASE
Anurag has more than 6+ years of experience in Sybase Database Development .His Area of expertise includes Performance, Query Optimization, Cost Optimization, TSQL Development. He is also involved in Consultancy to Financial Firms for Database Implementation and Maintenance. He has supported many Global Financial firms.