-
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!
-
Code:
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
Jeff Hunter
-
So -- is NUMBER fixed length or variable length?
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!
-
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|