When to use RAW datatype column over VARCHAR2 in Oracle?

Well in the database design typically the size matter. The bigger key takes more space in storage, on disc, the sorting takes longer time etc.

From this point the integer database key is the most compact one (implemented as a NUMBER type with zero precision, allocation typically between 2-8 bytes). From various reasons UUID is used as a key – with various motivations that are often independent of the database design rules.

Additionally, the UUID is often stored as formatted string in a VARCHAR2 column.

This is similar design as if you would store DATEs as a string (which is considered not a best practice).

Despite of it the RAW(16) columns allocate 16 bytes, the formatted UUID 36 bytes.

So in summary IMO there a following recommendations

1. Use NUMBER keys
2. If you can’t (and have solid arguments for it) use UUID in RAW(16) format

Note that of course the RAW format is a bit inconvenient to handle than a string (e.g. in setting of a bind variable). This often leads to the decision of storing the UUIDas a string - the vast majority of cases I encountered.

Below a small example illustrating the difference in sizing

create table tab
(id INT,
 RAW_UUID RAW(16) 
); 

insert into tab(ID,RAW_UUID) values (1,sys_guid());
insert into tab(ID,RAW_UUID) values (1000000001,sys_guid());

select * from tab;

        ID RAW_UUID                        
---------- --------------------------------
         1 8135869AECF44FB280A04033888FD518
1000000001 DE04ED07DDD84D1AABE9059F38364C7E



select vsize(id), vsize(raw_uuid)  from tab;

 VSIZE(ID) VSIZE(RAW_UUID)
---------- ---------------
         2              16
         6              16

What you can do is to define a virtual column (i.e. column that allocates no space) that presents the formatted UUID:

alter table tab add ( UUID VARCHAR2(36) GENERATED ALWAYS AS 
    (SUBSTR(LOWER(RAWTOHEX(RAW_UUID)),1,8)||'-'||SUBSTR(LOWER(RAWTOHEX(RAW_UUID)),9,4)||'-'||
    SUBSTR(LOWER(RAWTOHEX(RAW_UUID)),13,4)||'-'||SUBSTR(LOWER(RAWTOHEX(RAW_UUID)),17,4)||'-'||
    SUBSTR(LOWER(RAWTOHEX(RAW_UUID)),21,12)) VIRTUAL VISIBLE); 

Now the table has the text form UUID as well and you can use the familiar query

select * from tab where uuid = 'cbf7e2e2-a9e9-40fb-badc-18cb9a4fe663';

You can even define an index on the virtual column, but always before using UUID think on the Rule 1 above.

A GUID in Oracle is represented as raw(16).

You can get a GUID like this:

select sys_guid() from dual;

That's why you should use raw(16).