9.1D (Type I) Index Storage Areas

Posted by Grant Holman on 18-Apr-2016 10:25

Is it good practice to create Storage Area(s) purely for indexes?  If 'yes', then what are the advantages of this approach?

Thanks.

Posted by Keith Sudbury on 18-Apr-2016 10:36

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.

Posted by George Potemkin on 18-Apr-2016 10:51

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.

Posted by gus on 18-Apr-2016 11:02

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.

All Replies

Posted by Keith Sudbury on 18-Apr-2016 10:36

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.

Posted by George Potemkin on 18-Apr-2016 10:51

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.

Posted by gus on 18-Apr-2016 11:02

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.

Posted by Grant Holman on 19-Apr-2016 02:06

Great, that's all useful to know.  Many thanks to all who replied.

This thread is closed