Re: OBJECT DESING

For the radical approach - as espoused by Dave Ensor - PCTUSED=0 !!! (If this doesn't generate some serious discussion, I'll be amazed!)

This works quite well for tables where records are purged some set length of time after insert. The net result is that blocks are filled, then left alone until purged when they (the vast majority at least) are completely emptied and put back on the free list. Blocks tend to be fully populated and freelist activity is minimized. Be aware that in some older versions of Oracle, there is a bug that requires PCTUSED = 1 (blocks only move back to the free list if used space < PCTUSED instead of <= PCTUSED). I do not know offhand what version fixed this.

Dave Ensor's suggestion was to use this (almost) everywhere, but I'm not really comfortable with it as a blanket policy. It seems that tables with deletes that are "chronologically random" would end up with a LOT of very sparsely populated blocks.

The approach recommended below is to set it rather high to accomplish the block density goal, but at the potential expense of high freelist activity. Which approach is more appropriate depends on the nature of one's insert/delete criteria.

Also, PCTFREE can be set extremely low (1,5,?) for some kinds of objects - static tables, indexes with strictly inorder data (CREATE_DATE, SEQUENCE#, etc.).

I agree with always setting PCTINCREASE =0

Don Granaman
[OraSaurus - Honk if you remember UFI !]

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Thursday, April 18, 2002 8:08 AM

> Seema,
>
> PCTINCREASE - always 0.
>
> PCTFREE - for lookup (code) tables = 10 for others, I use 20. My theory
is
> that lookup tables have little or no updates, so this value should be
small.
> If you have a high-update tables (columns that are updated a 8lot* after
the
> record was created, you may want a higher value here.
>
> PCTUSED - for lookup (code) tables = 90, for others, I use 80. Again, for
> lookup tables with little or no updates, I fill the data blocks as high as
> possible. For other tables, I currently use 80 as a starting point.
>
> You will get many opinions here - read the docs to determine what you
think
> is best for you.
>
> Hope this helps.
>
> Tom Mercadante
> Oracle Certified Professional

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Don Granaman
  INET: granaman_at_cox.net

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).