CODEWITHSUNDEEP
sqloracle-database
Clinton
Clinton

Reputation: 23135

Oracle machine integer datatype

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.

Upvotes: 0

Views: 225

Answers (2)

Vincent Malgrat
Vincent Malgrat

Reputation: 67722

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.

Upvotes: 2

Mitch Wheat
Mitch Wheat

Reputation: 300489

How about a Universal Unique Identifier (UUID)?

CREATE table test 
(
    colguid RAW(16) default SYS_GUID() 
)

Upvotes: 0

Related Questions