Re: Row Chaining: Yet to get answer

From: Ron Rogers <RROGERS_at_galottery.org>
Date: Fri, 23 Jun 2000 11:29:18 -0400
Message-Id: <10537.110276@fatcity.com>

Sandeep,
As I understand the row chaining issue and how to prevent it, you have = to calculate the size of the complete row of data, then calculate how many = rows will fit into a block of data on your system, and then figure the = table initial and extent size accordingly. Keep in mind that all rows of = data has some overhead that must be used in the calulations. To get an = accurate calculation and inserting of rows it is usefull if the data = columns contain 100% of the column description. This will fill the row = completely as described and calculated.
As an example: a CHAR(300) and a VARCHAR2(300) do not take up the same = space in a row if only 10 characters are entered into the column. This = will throw your block usage off a lot and you will have partial used = blocks.
The calculations that I use are listed here and I ask the developers for a = row count that will handle 2 years of data( trying to plan sufficient = storage and not have to re-arrange or extend the datafiles). TO DETERMINE THE SIZE OF A TABLE STORAGE step 1. NUMBER OF ROWS IN TABLE. ---------------

step 2. NUMBER OF ROWS IN BLOCK.

NUMBER =3D21
CHAR =3D COLUMN SIZE
VARCHAR =3D COLUMN SIZE
DATE =3D 7 Z=3D3+(SUM OF COLUMN SIZES)+(# OF COLUMNS < 250)+(3* # OF COLUMNS > 250)

X=3D DBBLK SIZE / Z       ---------------------------------------------

Y=3DDBBLK-(%FREE(DBBLK-(52+4(X)))

Y>=3DX*Z  ADJ X TO GET "TRUE"               ---------------------

step 3. TOTAL BLOCKS NEEDED.

TOT BLOCKS=3DROWS IN TABLE/ROWS PER BLOCK TABLE SIZE =3DDBBLK SIZE * TOT BLOCKS / 1024 GIVES TABLE SIZE IN K. Hope this helps,
Ron Rogers
DBA
Atl.GA

=20

>>> sandeep.dubey_at_induscorp.com 06/23/00 11:14AM >>>
Hi gurus,

I asked to clear a doubt about row chaining, but have not rece'd any = answer.
I am asking again.

On a bulk Insert(and no update), will there be any row chaining at all? If yes, How to prevent it? If no, I have it although less than 1%.

Thanks in advance.

Sandeep=20

--=20
Author: Sandeep Dubey
INET: sandeep.dubey_at_induscorp.com=20

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 Fri Jun 23 2000 - 10:29:18 CDT