pctused issue

[helpme]

hi all,

I have this litte doubt bout PCTUSED that needs to be cleared.

suppose i have a table with pctused as 60% and pctfree as 40%...

Noe the averrage row length of the table is such that it can hold 1 record with this 60%.

Now suppose i give the value for the first row such that only 45% of the space is occupied and 15% still remains free.

Now i insert another record that mite occupy say more than the available 15% in the block.So would it write the first 15% in this block and the remaining in the next block?
Woud row chaining take place in this case?

Thanx a lot..
Sam

[jmodic]

No, PCTFREE or PCTUSED have no influence in this scenario. There is 55% free space in your block after the initial insert, which is more than your PCTFREE (40%), which means that the block is available for the additional inserts. And that means that whole 55% of the remaining block space is available for the next record. So next record will not be split (15% space in this block, remaining part of the record in next block), the whole record will be stored in this block. Only if the remaining 55% block space would not be enough for this new record would oracle insert it into another block with more free space, but in that case the *whole* record will be put in another block, so your original block will still hold only the initial record. And only if whole empty block would not be enough for insert of a second record would oracle store that record in multiple blocks (row chaining).

[helpme]

thanx modic...so it means in such a scenario ,row chaning would never take place...unless the inserted record length is greater than 60% of the block size?
thanx
Sam

[jmodic]

Originally posted by helpme
so it means in such a scenario ,row chaning would never take place...unless the inserted record length is greater than 60% of the block size?

No, it means that row chaining in such a scenario would never take place unless inserted record length is greater than whole database block, that is 100% of block size (if we neglect some overhead for block header)!

You could later expirience another kind of row chaining, called row migration. But this can happen only when updating existing records, not while inserting.