Subject:            How to Calculate Next Extent Size
Last Revision Date: 11 September 1994
Author:Ellen Tafeen                                                                



When creating a table, the storage parameters (whether explicitly stated
on the CREATE TABLE statement or defaulting to the tablespace specifica-
tions) provide absolute values for the first and second extents. Please
note the kernel automatically allocates a minimum of two Oracle blocks
for the first extent. They are used for the segment header and a data block.
The space calculation for any subsequent extent entails:

       (size of current extent) * (1.PCTINCREASE)

If the extent size doesnot fall on an Oracle block boundary, the size is
rounded up to the next Oracle block.

Prior to creating a table it is often desirable to predetermine the
initial amount of storage needed, especially when MINEXTENTS > 2
and PCTINCREASE > 0. The following formulas can help you calculate
the next extent x(i) size. Assume:

   x1=INITIAL,  x2=NEXT,  p=PCTINCREASE,  m=MAXEXTENTS,
   P=1 + (p/100),  r()=value rounded up to next Oracle block

x1 = 2 Oracle blocks or r(x1) if x1 > 2 Oracle blocks
x2 = r(x2)
xi = r(P * x(i-1))   where i>2

The total space available to a table can then be derived as:

   r(x1) + r(x2) + r(x2 * P) + r(x2 * P^2) ...+ r(x2 * P^(m-2))

which can be expressed as :

   x1 + SIGMA[i=0 to m-2] r(x2 * P^i)

As you'll notice, the calculation for each extent is rounded up to
the next Oracle block. As a result of this intermediate rounding up,
a table's total storage requirements may be larger than anticipated.

For example, CREATE TABLE ABC ....
             STORAGE (INITIAL 2048 NEXT 6144 PCTINCREASE 10
                      MINEXTENTS 4)
             where Oracle blocksize is 2048

x1 = 4096 (minimum of 2 Oracle blocks)
x2 = 6144
x3 = r((1.10) * x2)
   = r((1.10)(6144)
   = r(6758)
   = 8192
x4 = r((1.10) * x3)
   = r((1.10)(8192)
   = r(9011)
   = 10240

The storage calculated to allocate the first 4 extents when the table is
initially created is:

   x1 + x2 + x3 + x4 = 4096 + 6144 + 8192 + 10240
                     = 28672 bytes

The dictionary does not store the size of the last extent allocated.
Whenever a new extent is created, the kernel recalculates the existing
extent sizes inorder to derive the next extent size. If a user decides
to custom design the size of the next extent by issuing

         ALTER TABLE ABC STORAGE (PCTINCREASE 25)

the next extent size will be larger than predicted. The kernel will recal-
culate the existing extent sizes using a PCTINCREASE of 25 instead of the
actual PCTINCREASE used when the extents were allocated. In the example
above, the prior extents were calculated using PCTINCREASE 10. The next
extent size will be derived from a larger prior extent than is allocated
on the database.