1) a CHAR is simply a a VARCHAR2 that is stored blank padded. A char is stored with a leading byte field -- just like a varchar2. It is in fact a varchar2 that is always the maximum length (or null).

If all of the data in the table would eventually consume 100 bytes (eg: i insert 5 bytes, update it to 10, update it to 20, .... update it to near 100) it would be more efficient to use a char(100) as it preallocated the space and rows will not migrate over time due to updates. If the rows are truly varying length (normal case) a varchar2 is appropriate.

2) you should ALWAYS put the edit in the database anyway. even if the client always does the edit. This is because client applications lie to the database -- have bugs or are rewritten. The edit belongs in the database NO MATTER WHAT.

3) a number(10) would consume less space then a varchar2(10).


1 create table t as
2 select cast( rownum+9999999 as number(10) ) x, cast( rownum+9999999 as varchar2(10) ) y
3 from all_objects
4* where rownum < 100
ops$tkyte@ORA8I.WORLD> /

Table created.

ops$tkyte@ORA8I.WORLD> select min(vsize(x)), max(vsize(x)), min(vsize(y)), max(vsize(y)),
2 avg(vsize(x)), avg(vsize(y))
3 from t;

MIN(VSZE(X)) MAX(VSZE(X)) MIN(VSZE(Y)) MAX(VSZE(Y)) AVG(VSIZE(X)) AVG(VSIZE(Y))
------------ ------------ ------------ ------------ ------------- -------------
2 5 8 8 4.96969697 8

it would take less time to compare (join), less storage in an index and so on to use a number.

there would be a disadvantage to using a varchar2 over a number if the data is truly numeric

4)

it is 40 and 10:

1* select pct_used, pct_free from user_tables where table_name = 'T'
ops$tkyte@ORA8I.WORLD> /

PCT_USED PCT_FREE
---------- ----------
40 10

the sum cannot exceed 100 -- the best value is not a sum of 100. why is it 40/10? They are just reasonable, general purpose numbers. save 10% for updates -- when we have 10% free space -- stop inserting. when the block is 60% free space (40% used) start thinking about using it for inserts again.

The are set somewhat independent of eachother. The default, 10 pct free, simply says "save 10% of the block for updates. we guess that data will grow no more then 10% over time on each block". If I have an 8k block size -- about 800 bytes will be reserved (not available for an INSERT) for udpates. once we hit the pct free mark -- the block is taken OFF of the freelist.

Later we update a row or delete a row on the block. We discover the block is 61% FREE (less then 40% used). That is the signal to put the block back onto the freelist.


In general:

o High PCTFREE, Low PCTUSED – For when you insert lots of data that will be updated and the updates will increase the size of the rows frequently. This reserves a lot of space on the block after inserts (high PCTFREE) and makes it so that the block must almost be empty before getting back onto the free list (low PCTUSED).

o Low PCTFREE, High PCTUSED – If you tend to only ever INSERT or DELETE from the table or if you do update, the update tends to shrink the row in size.


So, you say "we have a delete intensive database" -- that would be a low pctfree and a high pctused, you want the space to be reused as soon as possible.


maybe 10/90 would be better.