CODEWITHSUNDEEP
oracle-databasehibernatejpaguiduuid
Alexander Fedyukov
Alexander Fedyukov

Reputation: 974

How can I hold UUID field in Hibernate mapping and in Oracle table field?

We are going to use UUID for primary key fields. What is the variants to hold UUID in Oracle table and in hibernate(JPA) mappings? Varchar2\String is expensive on my mind. What are the pitfalls of UUID as primary key?

Upvotes: 0

Views: 314

Answers (1)

ibre5041
ibre5041

Reputation: 5288

You can also use RAW(16). From space usage perspective it is the best solution, but still you "waste" a lot of space.

AFAIK there are no pitfalls, but for people having experience with Oracle it is non-natural solution.

PS: numeric id occupying then same space would have 30 valid digits.

PS: you can make a test, create a table having few millions of lines once having NUMBER as PK, second time having UUID as PK. Then compare sizes of PK indexes(segments), number of leaf blocks, ...

SQL> create table t1 as select level as N from dual connect by level <=1000000;
SQL> create table t2 as select sys_guid() as N from dual connect by level <=1000
000;
SQL> create unique index ix1 on t1(n);
SQL> create unique index ix2 on t2(n);
SQL> exec dbms_stats.gather_table_stats('SYS','T1');
SQL> exec dbms_stats.gather_table_stats('SYS','T2');    
SQL> select bytes, segment_name from dba_segments where segment_name in ('IX1',
'IX2');

     BYTES SEGMENT_NAME
---------- --------------------
  17825792 IX1
  31457280 IX2
SQL> select leaf_blocks, index_name from dba_ind_statistics where index_name in
('IX1', 'IX2');

LEAF_BLOCKS INDEX_NAME
----------- ------------------------------
       2087 IX1
       3760 IX2

Upvotes: 1

Related Questions