Integer v.s. Number

[elaine3839]

I have a couple of questions regarding to Integer and Number during my new db project physical model design.

1. Should I always use Integer instead of Number for all the ID fields? e.g. people_id, group_id, contact_id etc.

2. What the value range for Integer? Same as Number(38)?

3. Since we can't specify the precision for Integer(at least I didn't find a way to do it), how many bytes will the Integer field take? Is vsize() the only way we can find it out?

4. For a field that I know I need to use Number but not sure how many precision I should give it, shall I use my best guess to assign a precision or just define it as NUMBER? What's the drawback if I just define it as NUMBER?

I tried a couple of Oracle docs, but no one answered my questions directly. So I have to rely on you guys' talent and experience. Thanks in advance for all your help!

[marist89]

SQL> create table xyz ( x number, y integer);

Table created.
SQL> desc xyz
 Name                          Null?    Type
 ----------------------------- -------- --------------------
 X                                      NUMBER
 Y                                      NUMBER(38)

  1  select column_name, data_type, data_length, data_precision, data_scale
  2  from dba_tab_columns
  3* where table_name = 'XYZ'
SQL> /

COLUMN_NAME DATA_TYPE  DATA_LENGTH DATA_PRECISION DATA_SCALE
----------- ---------- ----------- -------------- ----------
X           NUMBER              22
Y           NUMBER              22                         0

[elaine3839]

According to Oracle doc, DATA_LENGTH column in dba_tab_columns shows the length of the column in bytes. Also, VSIZE returns the number of bytes in the internal representation of expr.

So my question now is: does VSIZE give me the ACTUAL number of bytes this column uses while DATA_LENGTH is the max # of bytes this column could take? i.e. does it behave similar to varchar2 datatype?

Also, if the answer to the above question is 'yes', may I draw a conclusion that I can feel free to define the column as INTEGER w/o worry about wasting space since it will only use the space based on the actual data length instead of always 22 bytes?

Thanks!

[jmodic]

Yes and yes. Yes.

Yes.