RE: Sizing Indexes - URGENT

The voice of reason! Thanks Dan. How many times have I been asked for an estimate of an index size (including every tiny overhead and spare byte) without knowing how many rows will be inserted. So they need an exact calculation based on a very rough estimate. As a result my calculation method has evolved into:  

• calculate the average index entry size.
• multiply by the estimated number of rows.
• factor in the PCTFREE setting.
• add up to half as much again depending on the faith you have in the figures you've been given.

Then use the time saved to get on with better value work.  

Your mileage may vary.  

Mike Hately      

 -----Original Message-----
Sent: 17 March 2003 14:14
To: Multiple recipients of list ORACLE-L

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 <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 <mailto: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 






**********************************************************************

The information contained in this e-mail is confidential and intended only
for the use of the addressee. If the reader of this message is not the
addressee, you are hereby notified that you have received this e-mail in
error and you must not copy, disseminate, distribute, use or take any action
as a result of the information contained in it.

If you have received this e-mail in error, please notify
postmaster_at_npower.com (UK 01384 275454) and delete it immediately from your
system.

**********************************************************************




**********************************************************************
 
The information contained in this e-mail is confidential and intended only for the use of the addressee. If the reader of this message is not the addressee, you are hereby notified that you have received this e-mail in error and you must not copy, disseminate, distribute, use or take any action as a result of the information contained in it.
    
If you have received this e-mail in error, please notify postmaster_at_npower.com (UK 01384 275454) and delete it immediately from your system.
    
Neither Npower nor any of the other companies in the Innogy group from whom this e-mail originates accept any responsibility for losses or damage as a result of any viruses and it is your responsibility to check attachments (if any) for viruses.  Npower Limited Registered office: Windmill Hill Business Park, Whitehill Way, Swindon SN5 6PB. Registered in England and Wales: number 3653277. This e-mail may be sent on behalf of a member of the Innogy group of companies.

**********************************************************************

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Hately, Mike (NESL-IT)
  INET: mike.hately_at_npower.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).