Is there a datatype in Oracle which I can have in a table which is a machine integer? I just want to use this as a unique identifier and I don't need the column to be nullable.
I noticed the INTEGER datatype maps to NUMBER(38) which is over 20 bytes.
There is currently no datatype in Oracle that will be stored as a machine integer (C int?). Oracle numbers are stored as variable-length strings of bytes:
SQL> SELECT DUMP(99), DUMP(999999), DUMP(9999.99) FROM dual;
DUMP(99) DUMP(999999) DUMP(9999.99)
-------------------- ---------------------------- ----------------------------
Typ=2 Len=2: 193,100 Typ=2 Len=4: 195,100,100,100 Typ=2 Len=4: 194,100,100,100
The first byte stores the scale, the other bytes are the base 100 representation of the number.
Some space is lost with this storage method but ultimately most of the time the overhead will be unnoticeable.
For a column with less than 10 billion identifiers, you could go with NUMBER(10) that will take at most 6 bytes.
How about a Universal Unique Identifier (UUID)?
CREATE table test
(
colguid RAW(16) default SYS_GUID()
)
