Re: why low PCTUSED

A copy of this was sent to yewpc_at_rnd.celcom.com.my (if that email address didn't require changing) On Tue, 30 Nov 1999 08:35:49 GMT, you wrote:

>Hi i have another question and this question is from the accessement
>test again.
>You are creating the LINE_ITEM table that will exprience extensive
>insert activity. Which space utilization parameter setting is most
>appropriate for this table ?
>A. low PCTUSED
>B. low PCTFREE
>C. high PCTUSED
>D. high PCTFREE
>the answer is A. i thought it should be C. where it allows you to
>insert more record in the block ? why is A ?
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.

The PCTFREE parameter sets the minimum percentage of a data block to be reserved as free space for possible updates to rows that already exist in that block.

The PCTUSED parameter sets the minimum percentage of a block that can be used for row data plus overhead before new rows will be added to the block. After a data block is filled to the limit determined by PCTFREE, Oracle considers the block unavailable for the insertion of new rows until the percentage of that block falls below the parameter PCTUSED. Until this value is achieved, Oracle uses the free space of the data block only for updates to rows already contained in the data block.

Technically, a low pctused (with any setting of pctfree) will allow the block to be considered a candidate for insertation. Argurably -- the answer "B" is correct as well -- if you have a low pctfree, the pctused will not come into play. I would say either A and B are equally correct. The answer A lets the block be inserted into regardless of the setting of pctfree. The answer B lets the block be inserted into until it is full and since they never mention DELETES/UPDATES (i assume the table is always growing, never getting emptied), the space used in a block only goes UP, never goes down -- pctused won't come into play anyway.

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue Nov 30 1999 - 08:25:54 CST