Blockchain vs Immutable tables

Hi,

as you know 21c is here for linux and some new features are (as always) pretty exciting. two of them are Blockchain and Immutable tables. Let’s explain and test.

first of all, those are new features for 21c but they are backported to 19c. at 19.10 you are able to use BLOCKCHAIN tables and 19.11 IMMUTABLE tables. I will make my tests at 19.12

if you are familiar to Bitcoin or any other alt coin then you probably now what blockchain is. basically it is a distributed ledger. every transaction is a “block” and each block contains information about transaction like sender, receiver, amount etc and also a hash value of the previous transaction (using some hash functions). That way all new transactions are added to the end of the “chain” and that creates the chain. This ensures that chain is unbreakable you can not tamper with old records because that will breaks the chain (hash values won’t match with next blocks).

So, Oracle uses this technology to create a new table called Blockchain table. every inserted row in this table are stored with some additional information like current timestamp and most importantly, previous row’s hash values! Hash value operations are made at the “COMMIT” time not before. that way Oracle guarantees that every row is a part of chain and you can not tamper with those rows. if you do, chain will be broken and can not be verified. Also, Oracle won’t let you delete or update those rows using any DML statement like delete, update, merge etc.

Immutable tables are pretty similar to Blockchain tables. you can not update those table using any DML as blockchain tables. Difference is rows in the Immutable tables are not linked to each other. Rows does not store any hash value from previous rows.

both of them are designed to create “unchangeable”, Insert only tables. if you think that this is a real necessity especially auditing staff. for example if you store a log information for a credit card information you don’t want anyone to change them and you want to be sure that those rows are intact and unchanged on an investigation.

of course you just need to store table data for a specific amount of time like 5 years etc. No one can store them forever so at some point you must be able to delete old rows. This is something you can define while creating those tables. Also, even if I may not able to delete the rows what would happen if someone drops the table! this is of course forbidden and again you can define a drop policy while creating tables. Let’s start with a blockchain table:

CREATE BLOCKCHAIN TABLE bank_ledger_blockchain (bank VARCHAR2(128), deposit_date DATE, deposit_amount NUMBER)
NO DROP UNTIL 31 DAYS IDLE
NO DELETE until 30 days after insert
HASHING USING "SHA2_512" VERSION "v1";

CREATE BLOCKCHAIN TABLE bank_ledger_blockchain (bank VARCHAR2(128), deposit_date DATE, deposit_amount NUMBER)

NO DROP UNTIL 31 DAYS IDLE

NO DELETE until 30 days after insert

HASHING USING "SHA2_512" VERSION "v1";

you need BLOCKCHAIN keyword (or IMMUTABLE for immutable tables) at the create statement.

“NO DROP UNTIL 31 DAYS IDLE” defines how can you drop this table. if you have no actions on that table for 31 days then and only then you can drop it. By the way after creating table, if you don’t insert any row then you can drop it immediately. you can just use NO DROP to disable dropping of this table (only way is dropping database entirely).

“NO DELETE until 30 days after insert” defines how can you delete “old” rows. in my example you can delete a row after 31 days of it’s insertion. Again you can use NO DELETE LOCKED to disable deleting from table.

hashing part is the default and you have to write this down to define blockchain structure.

let’s insert a row and try to delete or update:

SQL> insert into bank_ledger_blockchain values ('dummy', sysdate, 1);

1 row created.

SQL> commit;

Commit complete.

SQL> delete bank_ledger_blockchain;
delete bank_ledger_blockchain
       *
ERROR at line 1:
ORA-05715: operation not allowed on the blockchain or immutable table


SQL> update bank_ledger_blockchain set deposit_amount = 10;
update bank_ledger_blockchain set deposit_amount = 10
       *
ERROR at line 1:
ORA-05715: operation not allowed on the blockchain or immutable table

SQL> insert into bank_ledger_blockchain values ('dummy', sysdate, 1);

1 row created.

SQL> commit;

Commit complete.

SQL> delete bank_ledger_blockchain;

delete bank_ledger_blockchain

ERROR at line 1:

ORA-05715: operation not allowed on the blockchain or immutable table

SQL> update bank_ledger_blockchain set deposit_amount = 10;

update bank_ledger_blockchain set deposit_amount = 10

ERROR at line 1:

ORA-05715: operation not allowed on the blockchain or immutable table

as you can see modifying is forbidden. you can not even delete rows that passes the retention time that you defined (no delete until…) with a delete statement. You must use DBMS_BLOCKCHAIN_TABLE.DELETE_EXPIRED_ROWS procedure to delete rows beyond the retention.

This is BLOCKCHAIN table. of course there are much more properties like user signs etc but I will leave it here for now. For IMMUTABLE tables, almost everything is the same:

create immutable table bank_ledger_immutable (bank VARCHAR2(128), deposit_date DATE, deposit_amount NUMBER)
no drop until 31 days idle
no delete until 30 days after insert;

create immutable table bank_ledger_immutable (bank VARCHAR2(128), deposit_date DATE, deposit_amount NUMBER)

no drop until 31 days idle

no delete until 30 days after insert;

just change “blockchain” keyword to “immutable” and remove hash clause. pretty straight. as expected you can not delete or update rows. if you want to delete old rows then you must use DBMS_IMMUTABLE_TABLE.DELETE_EXPIRED_ROWS procedure.

Common Feature of both tables is that they can not be modified by authorized db users not even SYS or db vault admins. No one in the database can change those rows. Basic difference of the both tables is Blockchain table has steps for cryptographic linking between the last row and new row but Immutable table has no option like that. what does this provide? Immutable tables can not be modified via Oracle Database Software but how about bypassing DB? if someone modify datafile using Operating system tools then your table data will be modified and you can not be aware of it. Blockchain tables, on the other hand, has a verify function. since every row linked to each other with some hash value, if you modify this data using any tool, like OS tools, verification will be failed because if a row changes then it’s hash value will be change and that hash value is stored on the next row.

Then why immutable tables are exists since blockchain tables are more secure? You might guess that, creating hash values and storing them on the next row causes some extra work which means performance issues.

Let’s make a test (with a flow on purpose):

set serveroutput on

declare
  start_time timestamp;
  end_time timestamp;
  inserted_row_number number := 100000;
  procedure print_elapsed_time(p_table_type varchar2) as
  begin
    end_time := systimestamp;
    dbms_output.put_line('spend time on ' || p_table_type || ' table: ' || (end_time-start_time));
    start_time := systimestamp;
  end;
begin
  start_time := systimestamp;
  
  insert into bank_ledger_blockchain
    select 'dummy bank', sysdate + level, rownum
    from dual 
    connect by level <= inserted_row_number;
  
  print_elapsed_time('BLOCKCHAIN BULK INSERT');
  
  for i in 1..inserted_row_number loop
    insert into bank_ledger_blockchain values ('dummy bank', sysdate + i, i);
  end loop;
  
  print_elapsed_time('BLOCKCHAIN ROW BY ROW');
  
  insert into bank_ledger_immutable
    select 'dummy bank', sysdate + level, rownum
    from dual 
    connect by level <= inserted_row_number;
  
  print_elapsed_time('IMMUTABLE BULK INSERT');

  
  for i in 1..inserted_row_number loop
    insert into bank_ledger_immutable values ('dummy bank', sysdate + i, i);
  end loop;
  
  print_elapsed_time('IMMUTABLE ROW BY ROW');
end;
/

spend time on BLOCKCHAIN BULK INSERT table: +000000000 00:00:00.402270000
spend time on BLOCKCHAIN ROW BY ROW table: +000000000 00:00:01.155506000
spend time on IMMUTABLE BULK INSERT table: +000000000 00:00:00.355110000
spend time on IMMUTABLE ROW BY ROW table: +000000000 00:00:02.073610000

set serveroutput on

declare

start_time timestamp;

end_time timestamp;

inserted_row_number number := 100000;

procedure print_elapsed_time(p_table_type varchar2) as

begin

end_time := systimestamp;

dbms_output.put_line('spend time on ' || p_table_type || ' table: ' || (end_time-start_time));

start_time := systimestamp;

end;

begin

start_time := systimestamp;

insert into bank_ledger_blockchain

select 'dummy bank', sysdate + level, rownum

from dual

connect by level <= inserted_row_number;

print_elapsed_time('BLOCKCHAIN BULK INSERT');

for i in 1..inserted_row_number loop

insert into bank_ledger_blockchain values ('dummy bank', sysdate + i, i);

end loop;

print_elapsed_time('BLOCKCHAIN ROW BY ROW');

insert into bank_ledger_immutable

select 'dummy bank', sysdate + level, rownum

from dual

connect by level <= inserted_row_number;

print_elapsed_time('IMMUTABLE BULK INSERT');

for i in 1..inserted_row_number loop

insert into bank_ledger_immutable values ('dummy bank', sysdate + i, i);

end loop;

print_elapsed_time('IMMUTABLE ROW BY ROW');

end;

spend time on BLOCKCHAIN BULK INSERT table: +000000000 00:00:00.402270000

spend time on BLOCKCHAIN ROW BY ROW table: +000000000 00:00:01.155506000

spend time on IMMUTABLE BULK INSERT table: +000000000 00:00:00.355110000

spend time on IMMUTABLE ROW BY ROW table: +000000000 00:00:02.073610000

I inserted rows using bulk insert and row by row on both table and here are the results. huh! blockchain table is almost faster than immutable table? Actually no! as I said earlier hashing operations are done at the commit time not before on the blockchain table! in my code there is no commit! that’s why we see the same performance result. how about adding commits before printing time:

declare
  start_time timestamp;
  end_time timestamp;
  inserted_row_number number := 100000;
  procedure print_elapsed_time(p_table_type varchar2) as
  begin
    end_time := systimestamp;
    dbms_output.put_line('spend time on ' || p_table_type || ' table: ' || (end_time-start_time));
    start_time := systimestamp;
  end;
begin
  start_time := systimestamp;
  
  insert into bank_ledger_blockchain
    select 'dummy bank', sysdate + level, rownum
    from dual 
    connect by level <= inserted_row_number;
  commit;
  print_elapsed_time('BLOCKCHAIN BULK INSERT');
  
  for i in 1..inserted_row_number loop
    insert into bank_ledger_blockchain values ('dummy bank', sysdate + i, i);
  end loop;
  commit;
  print_elapsed_time('BLOCKCHAIN ROW BY ROW');
  
  insert into bank_ledger_immutable
    select 'dummy bank', sysdate + level, rownum
    from dual 
    connect by level <= inserted_row_number;
  commit;
  print_elapsed_time('IMMUTABLE BULK INSERT');

  
  for i in 1..inserted_row_number loop
    insert into bank_ledger_immutable values ('dummy bank', sysdate + i, i);
  end loop;
  commit;
  print_elapsed_time('IMMUTABLE ROW BY ROW');
end;
/

spend time on BLOCKCHAIN BULK INSERT table: +000000000 00:00:12.555355000
spend time on BLOCKCHAIN ROW BY ROW table: +000000000 00:00:13.749063000
spend time on IMMUTABLE BULK INSERT table: +000000000 00:00:00.312838000
spend time on IMMUTABLE ROW BY ROW table: +000000000 00:00:02.053782000

declare

start_time timestamp;

end_time timestamp;

inserted_row_number number := 100000;

procedure print_elapsed_time(p_table_type varchar2) as

begin

end_time := systimestamp;

dbms_output.put_line('spend time on ' || p_table_type || ' table: ' || (end_time-start_time));

start_time := systimestamp;

end;

begin

start_time := systimestamp;

insert into bank_ledger_blockchain

select 'dummy bank', sysdate + level, rownum

from dual

connect by level <= inserted_row_number;

commit;

print_elapsed_time('BLOCKCHAIN BULK INSERT');

for i in 1..inserted_row_number loop

insert into bank_ledger_blockchain values ('dummy bank', sysdate + i, i);

end loop;

commit;

print_elapsed_time('BLOCKCHAIN ROW BY ROW');

insert into bank_ledger_immutable

select 'dummy bank', sysdate + level, rownum

from dual

connect by level <= inserted_row_number;

commit;

print_elapsed_time('IMMUTABLE BULK INSERT');

for i in 1..inserted_row_number loop

insert into bank_ledger_immutable values ('dummy bank', sysdate + i, i);

end loop;

commit;

print_elapsed_time('IMMUTABLE ROW BY ROW');

end;

spend time on BLOCKCHAIN BULK INSERT table: +000000000 00:00:12.555355000

spend time on BLOCKCHAIN ROW BY ROW table: +000000000 00:00:13.749063000

spend time on IMMUTABLE BULK INSERT table: +000000000 00:00:00.312838000

spend time on IMMUTABLE ROW BY ROW table: +000000000 00:00:02.053782000

here it is! at worst, immutable tables are faster more than 6 times (row by row insert) and at best, almost 40 times faster than blockchain tables. So, while choosing table type be careful. if immutable tables meet the need then use them.

this is just a glance for new table types. I will make more tests and come with new posts.

wish you healthy days.

Edit: I didn’t mentioned about COMPATIBLE parameter. it should be at least 19.10 to create blockchain table and 19.11 for immutable table.

Comments (2)

Leave a Reply Cancel reply