When you hear the word "blockchain" we typically think of the properties associated with tamper-proof information, with terminology such as "immutability", "cryptographic digests", "cryptographic signatures" and so forth. Thus when it comes to blockchain tables in the database, it follows that we need a mechanism to store that additional metadata with the rows that are stored in the blockchain table.

This is achieved by using the existing database functionality of hidden columns. If I create a simple blockchain table

 SQL> SQL> CREATE blockchain TABLE bc1 (c1 char(10), c2 char(10))   2  no drop until 0 days idle no delete until 31 days after insert hashing using "sha2_512" version v1;  Table created.  SQL> SQL> insert into bc1 values ('x','x');  1 row created.  SQL> select * from bc1;  C1         C2 ---------- ---------- x          x  SQL> commit;  Commit complete.

then a DESCRIBE or a query to XXX_TAB_COLUMNS suggests nothing "untoward".

 SQL> desc bc1  Name                          Null?    Type  ----------------------------- -------- -------------  C1                                     CHAR(10)  C2                                     CHAR(10)  SQL> select column_name,data_type,data_length   2  from   user_tab_columns   3  where  table_name = 'BC1';  COLUMN_NAME                    DATA_TYPE                      DATA_LENGTH ------------------------------ ------------------------------ ----------- C1                             CHAR                                    10 C2                             CHAR                                    10

but the true list of columns for the blockchain table (and for that matter, any table with hidden columns) can be seen via USER_TAB_COLS.

 SQL> select column_name,data_type,data_length   2  from   user_tab_cols   3  where  table_name = 'BC1';  COLUMN_NAME                    DATA_TYPE                      DATA_LENGTH ------------------------------ ------------------------------ ----------- C1                             CHAR                                    10 C2                             CHAR                                    10 ORABCTAB_INST_ID$              NUMBER                                  22 ORABCTAB_CHAIN_ID$             NUMBER                                  22 ORABCTAB_SEQ_NUM$              NUMBER                                  22 ORABCTAB_CREATION_TIME$        TIMESTAMP(6) WITH TIME ZONE             13 ORABCTAB_USER_NUMBER$          NUMBER                                  22 ORABCTAB_HASH$                 RAW                                   2000 ORABCTAB_SIGNATURE$            RAW                                   2000 ORABCTAB_SIGNATURE_ALG$        NUMBER                                  22 ORABCTAB_SIGNATURE_CERT$       RAW                                     16 ORABCTAB_SPARE$                RAW                                   2000

When it comes to physical design of your schema, the true set of columns is the reference point via which you should be looking to set storage attributes for the table, and when it comes to blockchain tables, there is an additional functional difference you should look to take into account. Lets start with a fresh blockchain table and insert a new row

 SQL> CREATE blockchain TABLE bc1 (c1 char(10), c2 char(10))   2  no drop until 0 days idle no delete until 31 days after insert hashing using "sha2_512" version v1;  Table created.  SQL> insert into bc1 values ('x','y');  1 row created.

Unlike a traditional column (hidden or otherwise) the blockchain metadata columns are not populated:

 SQL> select   2   ORABCTAB_SEQ_NUM$   3  ,ORABCTAB_SPARE$   4  ,ORABCTAB_USER_NUMBER$   5  ,ORABCTAB_HASH$   6  ,ORABCTAB_SIGNATURE$   7  ,ORABCTAB_SIGNATURE_ALG$   8  ,ORABCTAB_SIGNATURE_CERT$   9  ,ORABCTAB_CHAIN_ID$  10  ,ORABCTAB_INST_ID$  11  ,ORABCTAB_CREATION_TIME$  12  from bc1  13  @pr ============================== ORABCTAB_SEQ_NUM$             : ORABCTAB_SPARE$               : ORABCTAB_USER_NUMBER$         : ORABCTAB_HASH$                : ORABCTAB_SIGNATURE$           : ORABCTAB_SIGNATURE_ALG$       : ORABCTAB_SIGNATURE_CERT$      : ORABCTAB_CHAIN_ID$            : ORABCTAB_INST_ID$             : ORABCTAB_CREATION_TIME$       :  PL/SQL procedure successfully completed.

The work to populate the metadata comes at the conclusion of the transaction

 SQL> commit;  Commit complete.  SQL> select   2   ORABCTAB_SEQ_NUM$   3  ,ORABCTAB_SPARE$   4  ,ORABCTAB_USER_NUMBER$   5  ,ORABCTAB_HASH$   6  ,ORABCTAB_SIGNATURE$   7  ,ORABCTAB_SIGNATURE_ALG$   8  ,ORABCTAB_SIGNATURE_CERT$   9  ,ORABCTAB_CHAIN_ID$  10  ,ORABCTAB_INST_ID$  11  ,ORABCTAB_CREATION_TIME$  12  from bc1  13  @pr ============================== ORABCTAB_SEQ_NUM$             : 1 ORABCTAB_SPARE$               : ORABCTAB_USER_NUMBER$         : 107 ORABCTAB_HASH$                : 0924C163458DE1FA3A7FFEA38ADA1852A409ADBF859A453E83A429BE3DB9F119C28828FFDB7BD101C5695C9CFCD82364F0B933B1E442B3CFCDE037D20CFDB631 ORABCTAB_SIGNATURE$           : ORABCTAB_SIGNATURE_ALG$       : ORABCTAB_SIGNATURE_CERT$      : ORABCTAB_CHAIN_ID$            : 18 ORABCTAB_INST_ID$             : 1 ORABCTAB_CREATION_TIME$       : 30-JUL-21 06.04.12.738166 AM +00:00  PL/SQL procedure successfully completed.

This is something that you will probably never notice because the vast majority of use cases for blockchain tables are the typical OLTP style of activity where transactions are small in duration and high in frequency. However, like all tables in the database, there are benefits to be had if you spend a little effort on physical design, and conversely, there are performance implications if you do not understand what is happening with blockchain tables behind the scenes.

I'll start with a blockchain table that mirrors the familiar EMP table.

 SQL> create blockchain table bc1 (   2   empno                         number(4)   3  ,ename                         varchar2(10)   4  ,job                           varchar2(9)   5  ,mgr                           number(4)   6  ,hiredate                      date   7  ,sal                           number(7,2)   8  ,comm                          number(7,2)   9  ,deptno                        number(2)  10  )  11  no drop until 0 days idle no delete until 31 days after insert hashing using "sha2_512" version v1;  Table created.

To gauge the typical size of each row in the EMP table, I'll populate it and gather some stats. In order to keep the numbers simple, I'll duplicate the same row many times over so every row is the same size.

 SQL> create table emp as   2  select s.* from   3    ( select * from scott.emp where rownum = 1 ) s,   4    ( select 1 from dual connect by level <= 180);  Table created.  SQL> SQL> exec dbms_stats.gather_table_stats('','EMP')  PL/SQL procedure successfully completed.  SQL> select avg_row_len from user_tables   2  where table_name = 'EMP';  AVG_ROW_LEN -----------          34

Keep that number in mind, and also notice that those 180 rows all fit nicely into a single block in the database.

 SQL> select dbms_rowid.rowid_block_number(rowid) blk, count(*)   2  from emp   3  group by dbms_rowid.rowid_block_number(rowid);         BLK   COUNT(*) ---------- ----------        129        180

Now I am going to load 80 rows in the same way from the EMP table into my blockchain table which has the same columns.

 SQL> insert into bc1   2  select s.* from   3    ( select * from scott.emp where rownum = 1 ) s,   4    ( select 1 from dual connect by level <= 180);  180 rows created.

As we saw previously, the internal columns are not populated whilst the transaction is still active, but once the commit is performed we now have 180 rows to calculate the relevant metadata for.

 SQL> select count(ORABCTAB_SEQ_NUM$) from bc1;  COUNT(ORABCTAB_SEQ_NUM$) ------------------------                        0   -- nothing populated  SQL> commit;  Commit complete.  SQL> select count(ORABCTAB_SEQ_NUM$) from bc1;  COUNT(ORABCTAB_SEQ_NUM$) ------------------------                      180  -- now fully populated

But lets step back and think about the process here. At the time of insert, we loaded 180 rows of size 34 bytes into our blockchain table. Like the EMP table, we would expect this to fit nicely into a single block. But the moment we issued a commit, every one of those rows had to be extended to hold extra metadata. This is synonymous with returning to the rows in a later transaction and updating each of them to be a larger size. Does row migration become a risk here? Time to dig a little deeper.

 SQL> @?/rdbms/admin/utlchain  Table created.  SQL> analyze table bc1 list chained rows into chained_rows;  Table analyzed.  SQL> select head_rowid from chained_rows;  HEAD_ROWID ------------------ AAAS4KAATAAAAEBAAJ AAAS4KAATAAAAEBAAK AAAS4KAATAAAAEBAAM AAAS4KAATAAAAEBAAN AAAS4KAATAAAAEBAAO AAAS4KAATAAAAEBAAQ ... ... AAAS4KAATAAAAEBACt AAAS4KAATAAAAEBACv AAAS4KAATAAAAEBACw AAAS4KAATAAAAEBACx AAAS4KAATAAAAEBACz  130 rows selected.

Approximately 70% of the rows have now been migrated, which can have implications for index access to these rows. This is a consequence of having lots of blockchain rows being created in a single transaction. If I perform the same 180 row insert, but perform them row-at-a-time to mimic a single row transaction application, then because each row gets extended on commit, the probably of row migration drops to zero even though the rows have grown from 34 bytes to 125 bytes during the course of the transaction.

 SQL> create blockchain table bc1 (   2   empno                         number(4)   3  ,ename                         varchar2(10)   4  ,job                           varchar2(9)   5  ,mgr                           number(4)   6  ,hiredate                      date   7  ,sal                           number(7,2)   8  ,comm                          number(7,2)   9  ,deptno                        number(2)  10  )  11  no drop until 0 days idle no delete until 31 days after insert hashing using "sha2_512" version v1;  Table created.  SQL> SQL> begin   2  for i in 1 .. 180 loop   3    insert into bc1   4    select * from scott.emp where rownum = 1;   5    commit;   6  end loop;   7  end;   8  /  PL/SQL procedure successfully completed.  SQL> truncate table chained_rows;  Table truncated.  SQL> analyze table bc1 list chained rows into chained_rows;  Table analyzed.  SQL> SQL> select head_rowid from chained_rows;  no rows selected  SQL> exec dbms_stats.gather_table_stats('','BC1')  PL/SQL procedure successfully completed.  SQL> select avg_row_len from user_tables   2  where table_name = 'BC1';  AVG_ROW_LEN -----------         125

It is unlikely that INSERT-SELECT is going to be a common use case for blockchain tables, but if you do need to do this, you probably want to make some adjustments to your PCTFREE settings or manipulate the Hakan factor. (See the video at the tail of this post).

But it is not just a single session loading 180 rows via INSERT-SELECT that creates the scenario of multiple uncommitted rows sitting in a database block. If you have a high transaction volume application, then multiple sessions all performing a few inserts each can create a similar situation. I ran a benchmark with 60 concurrent sessions all performing 4 inserts before the transaction committed to see if any row migration occurred. From 240 rows, approximately 80 of them ended up being chained.

This probably an extreme case because the hidden metadata in a large proportion of the total size of the row. (Modern applications typically have a little more data than what you find in the EMP table :-)). Conversely, if you also start to take advantage of the row signing features of blockchain tables, the metadata is larger per row, so I'd recommend always performing some analysis on the data in the table, and the method via which the table will be populated in order to determine an appropriate PCTFREE setting for the blockchain table.

If this all sounds like  jibber-jabber to you, then here is a primer on the PCTFREE setting

and for the more advanced practitioner, some considerations on using the Hakan factor.