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

Home -> Community -> Usenet -> c.d.o.misc -> Re: Inserts and row chaining

Re: Inserts and row chaining

From: Allen Kirby <akirby_at_att.com>
Date: 1997/01/16
Message-ID: <32DE79C5.1EE6@att.com>#1/1

Unknown wrote:

> Chuck, thanks for your reply. However, I am still a little unclear.
> Lets say I have a 100-byte row that I wish to insert and the next
> block on the freelist will exceed (or go under) the PCTFREE value
> in 75 bytes. My boss insists that Oracle will place 75 bytes into
> this block and 25 into the following block. I cannot believe Oracle
> would do that because we would have chained rows all over the place.
>

Brett,

Tell your Boss that if Oracle worked that way, then the only time a block wouldn't contain a chained row is if the last row size was the EXACT size to fill the block to PCTFREE threshold but not go under or over. And I promise you, Oracle does not chain rows on inserts. Oracle may be a lot of things but it's not STUPID (well, not in this case)!

OK, I did the unthinkable and read the book (Oracle 7 Server Administration - chapter 3). Here is how it works:

When a row is ready to insert, Oracle checks the block to see if inserting the row would cause the block to exceed PCTFREE free space. If so, it does not do the insert. It then checks PCTUSED - if the block is at least PCTUSED filled, then the block is taken off the free list. That's why they tell you not to set PCTFREE + PCTUSED = 100. Because you'll never take the block off the free list even though you can't use it for inserts. e.g.

if PCTFREE is 20 and PCTUSED is 80 and the block is currently 75% full and the new row to be inserted is 10% of the block size:

an insert is not performed because (100% - 85%) is less than PCTFREE. the block is not taken off the free list because it hasn't reached PCTUSED (80%) capacity. This will repeat over and over. I believe we had this problem and were never reusing blocks.

If you change PCTUSED to 60 then the block will be taken off the free list after the first insert attempt fails. It will be put back on the free list when a delete is performed and the data in the block is < 60%.

My condolances to you. Of course, his position on this may explain why he's a manager! If he's like some managers, he'll probably claim that the Oracle manual is wrong!
Good Luck anyway.

-- 
---
Allen Kirby			AT&T ITS Production Services
akirby_at_att.com			Alpharetta, GA.
Received on Thu Jan 16 1997 - 00:00:00 CST

Original text of this message

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