Best Pctfree/ Pctused value for 90% of the columns are updated with number data types

[engiri]

Hi

I have a table with below structure.

LOCN_CODE NOT NULL VARCHAR2(3)
ITEM_CODE NOT NULL VARCHAR2(13)
ITEM_TYPE NOT NULL VARCHAR2(1)
SUPL_CODE NOT NULL VARCHAR2(4)
ITEM_LRCVD_DATE DATE
ITEM_FRCVD_DATE DATE
ITEM_SOLD_DATE DATE
FC_RATE NUMBER(12,3)
WAC NUMBER(10,3)
ORGN_PRICE NUMBER(10,3)
RETL_PRICE NUMBER(10,3)
ST_CODE NOT NULL NUMBER(4)
OPN NUMBER(6)
GRN NUMBER(6)
SAL NUMBER(6)
TRAN NUMBER(6)
PRN NUMBER(6)
SAD NUMBER(6)
DMG NUMBER(6)
WH_QTY NUMBER(6)
SR_QTY NUMBER(6)

The NOT NULL columns will be inserted first and all other columns
will be null. We update the other columns on everyday basis whenver transaction occur.

What will be the best suiting values of Pctfree and Pctused
parameters for the above table.

Can I keep pctfree as 0 since the columns to be updated are
number and date types?

IF the above is sure, I can use pctused 95 for the same.

Thanks
Giri

[engiri]

ANy Advices..?

[jmodic]

No, PCTFREE=0 is certanly not a good choice for this situation.

[engiri]

Can u please explain with reasons..

Thx

[pando]

if you have updates why on earth you want to set pctfree to zero?!

[engiri]

Please go through my question.. which
goes like ..
if the data type is number whether or not it is null or having some
values
should oracle preoccupy the space in the block..?

Thanks

[aspdba]

nopes..i think not..except for char columns

if you know your table is going to be updated later, I think you should keep your pctfree something greater than 0%

[jmodic]

No, Oracle will not preoccupy space for any datatype column if the column is initialy null (not even for CHAR columns).

[engiri]

Originally posted by jmodic
No, Oracle will not preoccupy space for any datatype column if the column is initialy null (not even for CHAR columns).

Then what is the difference between varchar2 and char datatype
?
THx

[jmodic]

VARCHAR2 is variable length character datatype while CHAR is fixed length datatype.

If you have column defined as VARCHAR2(10) and you want to store value 'Hi' in there, Oracle will store only those two characters in that column. If on the other hand the column is of type CHAR10), then everything you store in there is right blank-padded to the length of 10. So the value 'Hi' will actualy be stored as those two characters plus 8 blank characters.

http://download-west.oracle.com/docs...datyp.htm#7223