Subject: Re: Large table performance -Reply Date: Wed, 20 Nov 1996 13:25:16 -0800 Although after doubling the page size there are still 4 index levels, there may be an improvement in the "average I/O cost" to process the index per random record fetch. Assume a 75 page database buffer pool. Make the simplifying assumption (though not completely accurate) that, at steady-state, levels 2 & 3 become permanantly cached in both cases and that level 0 always incurs an I/O in both cases. Also, assume that fetching the data page holding the record always costs an I/O in either case. Level 1 is the pivotal factor: For a 1kb page size, level 1 costs (1 - 75/818) or about 0.9 I/O's For a 2kb page size, level 1 costs (1 - 75/204) or about 0.6 I/O's. On average, it will cost 1.9 I/O's to process the index for a 1kb page size and 1.6 I/O's for the 2kb page size. An additional factor to consider is the cost to fetch the pointer page (PP) which points to a table's data pages (DP). In general, the index retrieval looks like this: Index(L3)->Index(L2)->Index(L1)->Index(L0)->PP->DP For a 1kb page size, the table has 70,000 pages which requires about 280 pointer pages. I/O cost (1 - 75/280) or 0.7 For a 2kb page size, the table has 35,000 pages which requires about 70 pointer pages. I/O cost (1 - 75/70) or 0. So the total I/O cost to fetch a random record: Index(L3)->Index(L2)->Index(L1)->Index(L0)->PP->DP 1kb 0 + 0 + 0.9 + 1 + 0.7 + 1 = 3.6 I/O's 2kb 0 + 0 + 0.6 + 1 + 0 + 1 = 2.6 I/O's On average, it takes 1 less disk I/O to fetch a record with the 2kb page size. >>> Deej Bredenberg 11/20/96 12:55pm >>> >level 0: 24,089 level 1: 818 level 2: 28 level 3: 1 >Doubling the page size to 2K means that 58.9 records can be stored per >page. The index levels will then contain the following numbers of >pages: >level 0: 12,043 level 1: 204 level 2: 4 level 3: 1 >So you still have 4 levels either way!