Re: Estimating Table/Index Space

From: Jack Silvey <jack_silvey_at_yahoo.com>
Date: Wed, 07 Aug 2002 20:19:19 -0800
Message-ID: <F001.004AE883.20020807201919@fatcity.com>

Walter,

I extrapolate from a test table, which I have found is the most reliable way.

Create a table that is identical to the production table, insert x rows (10,000 rows should do) and analyze compute. If you don't have the data, just write a pl/sql program to insert the correct number of rows in a loop, using correct column sizes.

(As an aside, one standard way of deciding on number of rows to use is to decide on your tolerance for margin of error. One standard way of calculating margin of error is via this formula:

M = 1/SQRT(N) where:

M = margin of error
N=sample size
so 10,000 rows yields a MOE of < 1%)

Get avg_row_len from dba_tables for that table and multiply times the expected number of rows. Add y percent where y = pctfree. Add a fudge factor of z percent, I use 20.

Create your indexes afterwards and extrapolate. Add a fudge factor.

The reason that Oracle pulled the sizing formulas from the docs is that they were not accurate enough and they recieved many complaints. I have found this method to be very reliable.

hth,

Jack

• Walter K <ORA1034_at_sbcglobal.net> wrote:
  > Does someone have a simple formula for estimating
  > the
  > space (row basis) for a table and a regular b-tree
  > index? I recall a formula that had a fixed value for
  >
  > the row overhead (data or index) as well as overhead
  >
  > for the column(s) and then space for the data
  > itself--
  > plug in the values and voila!
  >
  > This is for an 8.1.7 database.
  >
  > Thanks!
  > -w
  >
  > --
  > Please see the official ORACLE-L FAQ:
  > http://www.orafaq.com
  > --
  > Author: Walter K
  > INET: ORA1034_at_sbcglobal.net
  >
  > Fat City Network Services -- (858) 538-5051 FAX:
  > (858) 538-5051
  > San Diego, California -- Public Internet
  > access / Mailing Lists
  >
  

  ---

  
  > To REMOVE yourself from this mailing list, send an
  > E-Mail message
  > to: ListGuru_at_fatcity.com (note EXACT spelling of
  > 'ListGuru') and in
  > the message BODY, include a line containing: UNSUB
  > ORACLE-L
  > (or the name of mailing list you want to be removed
  > from). You may
  > also send the HELP command for other information
  > (like subscribing).
  

  ---

Do You Yahoo!?
HotJobs - Search Thousands of New Jobs
http://www.hotjobs.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jack Silvey
  INET: jack_silvey_at_yahoo.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Wed Aug 07 2002 - 23:19:19 CDT