Is it good practice to create Storage Area(s) purely for indexes? If 'yes', then what are the advantages of this approach?
Thanks.
For Type I areas you can get some performance and maintenance gains by splitting tables and indexes out into their own areas (within reason). How much depends on your application and how intermingled the blocks are. Type I areas were the biggest reason regularly scheduled dump and reloads used to be required.
You will not get anywhere near as much of an improvement compared to upgrading to a supported version of OE and using properly configured Type II areas though. Not to mention all of the performance improvements available in modern versions of OE.
1. If you get a corrupted block in an index area then you know you can repair database by rebuilding indexes.
2. Index rebuild will be faster if you truncate an index area(s) before running idxbuild.
3. since each type i data area has its own free list, you get slightly better allocation concurrency when tables and indexes are not competing with each other when blocks are being allocated to one or the other.
4. because the rows in the area with the tables are stored in a smaller space (because there aren't any index blocks spread out among the table blocks), index compression will be slightly better.
For Type I areas you can get some performance and maintenance gains by splitting tables and indexes out into their own areas (within reason). How much depends on your application and how intermingled the blocks are. Type I areas were the biggest reason regularly scheduled dump and reloads used to be required.
You will not get anywhere near as much of an improvement compared to upgrading to a supported version of OE and using properly configured Type II areas though. Not to mention all of the performance improvements available in modern versions of OE.
1. If you get a corrupted block in an index area then you know you can repair database by rebuilding indexes.
2. Index rebuild will be faster if you truncate an index area(s) before running idxbuild.
3. since each type i data area has its own free list, you get slightly better allocation concurrency when tables and indexes are not competing with each other when blocks are being allocated to one or the other.
4. because the rows in the area with the tables are stored in a smaller space (because there aren't any index blocks spread out among the table blocks), index compression will be slightly better.
Great, that's all useful to know. Many thanks to all who replied.