Re: PCTUSED and PCTFREE

> > I believe that their will be since I am forcing the block to be
> > completey used (pctused 100). Is this assumption correct?

This is not correct. The PCTUSED parameter just says that when the block falls below this level, mark it as available for new rows to be inserted. If the block is more than this percent full, then it is removed from the list of available blocks, the FREELIST.  

> If you really aren't going to do any updating at all I don't think it
> will help. I have read that PCTFREE + PCTUSED should be less than
> 100, but I have never seen any explanation as to why they shouldn't be
> exactly 100.

Here's why PCTFREE + PCTUSED should not always equal 100. Once the block reaches PCTFREE, it is removed from the FREELIST. No more rows can be inserted into that block. Deleting one row from that block will then cause the block to fall below PCTUSED. This means that the block is then put on the FREELIST. Now adding one more row will remove the block from the FREELIST. Removing and adding a block to the FREELIST requires some overhead. In high transaction tables that experience inserts and deletes (OLTP environments), the amount of overhead required to maintain the FREELIST can cause some performance problems. In these situations, it is better to have the PCTFREE + PCTUSED not equal 100. That way, it takes a few deletes before the block is added to the FREELIST and a few insert before it is subsequently removed.

HTH,
Brian

-- 
========================================
Brian Peasland
Raytheons Systems at
  USGS EROS Data Center
These opinions are my own and do not
necessarily reflect the opinions of my 
company!
========================================