Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Sizing Indexes - URGENT

Re: Sizing Indexes - URGENT

From: Daniel W. Fink <optimaldba_at_yahoo.com>
Date: Mon, 17 Mar 2003 06:13:53 -0800
Message-ID: <F001.0056B8F3.20030317061353@fatcity.com>


Jai,

    The formula you are using is reasonably accurate, but not very useful. One of the main problems I have found with this approach is that the number of rows is not usually known. The business side and designers might have an idea of data sizes, but the reality is that most times they are not accurate. This makes a fine-grained sizing approach ultimately innacurate.

    Use locally managed tablespaces and create estimated sizes. Add in enough space for variances and keep an eye on them. When in doubt, err on the side of overallocation.

-- 
Daniel W. Fink
http://www.optimaldba.com

IOUG-A Live! April 27 - May 1, 2003 Orlando, FL
   Sunday, April 27 8:30am - 4:30pm - Problem Solving with Oracle 9i SQL
   Wednesday, May 1 1:00pm - 2:00pm - Automatic Undo Internals


JayK_at_ibsplc.com wrote:


>
> Dear All,
>
> I have a task on hand to size a database for our application. I have
> used the Oracle rule to find out the rowsize of rows in a table.
>
> 1. Space available for the data (SAD) = data block size - block
> header size = db_block_size - kcbh - ub4 - ktbbh -
> (initrans -1)* ktbit - kdbh
> 2. Available Data Space (ADS) = ceil(SAD * (1-pctfree/100)) - kdbt
> 3. Space used per row (SPR) = (Total size of the columns data
> length) + (1 byte per column with length < 250 )+ (3 *
> 1 byte per column with length >= 250)
> 4. Row Size (ROWSZ) = 3 * ub1 + SPR
> 5. Space used per row(SPROW) = max(ub1 * 3 + ub4 + sb2, ROWSZ)
> + sb2
> 6. Total Size of the table = Expected Number of records * SPROW
>
> I hope this formula is correct.
>
> How can I do the sizing of indexes. Shouldn't I take into account the
> 10 bytes consumed by the ROWID pseudocolumn apart from the column(s)
> that are indexed ?
>
> TIA for all your help.
>
> Best Regards
> Jai
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Daniel W. Fink INET: optimaldba_at_yahoo.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- 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 Mon Mar 17 2003 - 08:13:53 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US