On Fri, 13 Aug 1999 11:34:44 +0100, "Kevin A Lewis"
<KevinALewis_at_Hotmail.com> wrote:
>Is there a relationship between DB_BLOCK_SIZE and chained rows. I thought
>the controlling factors were the table PCTFREE and PCTUSED settings and the
>use of column types such as VARCHAR2.
>
>My limited understanding of the mathematics suggests to me that there could
>be a relationship. I have seen one or two people on this type of forum
>suggest a link. However I was wondering if anyone else had any practical or
>experimental experience to draw on.
The bigger your block size is, the less problems you should have with
regards to row chaining. There are two different kind of "chaining":
migrated rows and chained rows.
- Chained rows: when a row is inserted in an empty block, there might
not be enough room to hold all the data of one row. The Oracle has no
other option than to split the row and store it in two or more blocks.
Example: You have a table TAB1 with two columns of type
VARCHAR2(2000). Your block size is 2K. If you insert a row with both
columns holding 2000 characters, Oracle will have to store it in 2
blocks (probably in 3 blocks because of the block header overhead).
There is nothing you can do about this with storage parameters of the
table. But if your block size is 8K then the row would not get
chained!
- Migrated rows: When a row is initially inserted, it can fit into a
single block. But when it is updated afterwards, it could be enlarged
to such a size that blockspace reserved for updates (PCTFREE) could
not be sufficient, so the row must be moved to a new block. The row
header remains in the original block, but the raw data is moved to a
different block.
Example: Your PCTFREE of TAB1 is set to 40%, block size is 2K. You
inserted 5 rows into it, each row containing 200 bytes of data. They
all fit into a single block, leaving approx 1K of free space in a
block. Now if you update one of this rows and enlarge it to the size
of 1500 bytes it won't fit in the remaining space of the block. Oracle
will move the whole row into another free block, leaving only a
pointer to a new location in the original block (hence the ROWID
remains unchanged, but the data is stored in a different block). If
the PCTFREE of the table had been set to 85%, then the original insert
would place each of the rows into its own block, leaving enough free
space to accommodate the update of your updated row. But if your block
size is 8K and TAB1 has the same PCTFREE=10, then the free space for
updates would be sufficient for the above update and row would not get
migrated.
>Regards
HTH,
Jurij Modic <jmodic_at_src.si>
Certified Oracle DBA (7.3 & 8.0 OCP)
The above opinions are mine and do not represent
any official standpoints of my employer
Received on Fri Aug 13 1999 - 06:55:23 CDT
