Subject : Re: Table sizing
----- Message Text -----
On Mon, 29 Mar 1999, Cheryl Baker wrote:

> Hi, I just have one question for you. We are using Oracle 8 and were trying
> to set up our tables. Do you have any tips on table sizing for us, it would
> be greatly appreciated.
Here is what I usually do:

1) Get an estimate of the number of records for the table, both now, and
   projected to 6 months from now
2) Find the average length of each record. If you cannot do this
   perfectly, then try to make a good guess.
3) Determine how often the table is updated/inserted/deleted. If it is
   changed often (and the record sizes vary), then the PCTUSED would be
   low, something like 40-60. If the table is static, then I'd make the
   table PCTUSED 85 or 90.
4) Determine my database's block size:
   select value from v$parameter where name = 'db_block_size';

Now I have all of the information needed to size my table:

Records per block = (block size - 110 bytes for overhead) * (PCTUSED/100)/
                    Average Record Size

Total blocks = (records in table) / (records per block)
Total table size = blocks * block size
Give a little fudge factor and make this your INITIAL extent size.
Your NEXT will be determined by your 6 month growth projection.

Best regards,
-Ari Kaplan
Independent Oracle DBA Consultant
<-> For 275+ Oracle tips, visit my Web Page:                      <->
<->                                                               <->
<->                                    <->
<->                                                               <->
<->             email:                    <->

Back to Ari Kaplan's Home Page "> Back to Ari Kaplan's Home Page