Key preserved table improvements in 21c

Key preserved table is a cool feature available since Oracle 7.3
 
Let’s start with a simple demo using EMP and DEPT table
 
demo@ATP19C> create table emp as select * from scott.emp;
 
Table created.
 
demo@ATP19C> create table dept as select * from scott.dept;
 
Table created.
 
demo@ATP19C> alter table dept
  2  add constraint dept_pk
  3  primary key(deptno);
 
Table altered.
 
demo@ATP19C> alter table emp
  2  add constraint emp_fk
  3  foreign key(deptno)
  4  references dept;
 
Table altered.
 
Then a view that join these two tables
 
demo@ATP19C> create or replace view v
  2  as
  3  select e.empno,e.ename,e.sal,e.hiredate,d.dname,d.loc
  4  from emp e , dept d
  5  where e.deptno = d.deptno;
 
View created.
 
One of the nice features available since Oracle 7.3 is we can update this view, even though it is a join, we called it is a Key preserved table on EMP table (ie, for every row in that view maps to one row in the EMP table)
 
demo@ATP19C> select column_name,updatable,insertable,deletable
  2  from user_updatable_columns
  3  where table_name ='V' ;
 
COLUMN_NAM UPD INS DEL
---------- --- --- ---
EMPNO      YES YES YES
ENAME      YES YES YES
SAL        YES YES YES
HIREDATE   YES YES YES
DNAME      NO  NO  NO
LOC        NO  NO  NO
 
So we can update only those column in this view belong to the EMP table but not the DEPT table and that is what it is summarized in the above dictionary view.
 
Now let’s update the view on one of the columns from EMP table.
 
demo@ATP19C> update v
  2  set sal = 6000
  3  where empno = 7499;
 
1 row updated.
 
And the changes were available in the underlying base table too.
 
demo@ATP19C> select empno,ename,sal from v where empno = 7499;
 
     EMPNO ENAME             SAL
---------- ---------- ----------
      7499 ALLEN            6000
 
demo@ATP19C> select empno,ename,sal from emp where empno = 7499;
 
     EMPNO ENAME             SAL
---------- ---------- ----------
      7499 ALLEN            6000
 
Now let’s update those columns in this view belong to the DEPT table, and this is how it fails in Oracle 19c
 
demo@ATP19C> update v
  2  set dname = 'X'
  3  where empno = 7499 ;
set dname = 'X'
    *
ERROR at line 2:
ORA-01779: cannot modify a column which maps to a non key-preserved table
 
But now in 21c database.
 
demo@ATP21C> update v
  2  set dname = 'X'
  3  where empno = 7499 ;
 
1 row updated.
 
demo@ATP21C> select empno,ename,deptno
  2  from emp
  3  where empno = 7499 ;
 
     EMPNO ENAME          DEPTNO
---------- ---------- ----------
      7499 ALLEN              30
 
demo@ATP21C> select dname from dept where deptno = 30;
 
DNAME
--------------
X
 
The above update works. This is not a “bug” in 21c instead it was a nice enhancement to Oracle 21c optimizer. If we are doing an update and if optimizer thinks it is safe then it was allowed to made the changes to the view.
 
So in the above case, the employee 7499 belongs to the dept no 30 and that dept no 30 maps to a single row in that DEPT table, so the update to the view was allowed. What happens if we update multiple rows in the view
 
demo@ATP21C> select empno,ename,deptno
  2  from emp
  3  where empno in ( 7499,7839 );
 
     EMPNO ENAME          DEPTNO
---------- ---------- ----------
      7839 KING               10
      7499 ALLEN              30
 
demo@ATP21C> update v
  2  set dname = 'X'
  3  where empno in ( 7499,7839 );
 
2 rows updated.
 
demo@ATP21C> select deptno,dname from dept where deptno in (10,30);
 
    DEPTNO DNAME
---------- --------------
        10 X
        30 X
 
Since each employee in the above update maps to two different department and each department no maps to two different rows in the DEPT table it was allowed. Lets say what happens if we update multiple rows in the view that maps to same department number.
 
demo@ATP21C> select empno,ename,deptno
  2  from emp
  3  where empno in ( 7499,7698 );
 
     EMPNO ENAME          DEPTNO
---------- ---------- ----------
      7698 BLAKE              30
      7499 ALLEN              30
 
demo@ATP21C> update v
  2  set dname = ename
  3  where empno in ( 7499,7698 );
update v
       *
ERROR at line 1:
ORA-30926: unable to get a stable set of rows in the source tables
 
demo@ATP21C> update v
  2  set dname = 'X'
  3  where empno in ( 7499,7698 );
update v
       *
ERROR at line 1:
ORA-30926: unable to get a stable set of rows in the source tables
 
So during the update if multiple rows of the child table maps to the same rows in the parent (in this case two different employees maps to same dept no) then the update is not be allowed to processed.
 
 
 
 

JSON Datatype - Part I

One of many nice things about Oracle database 21c is “JSON” datatype, which is an optimized native binary storage format using the OSON format. All json documents in autonomous database are automatically stored in OSON format. This delivers big performance benefits to json applications, including faster query performance, efficient updates, multi-value function index and efficient storage sizes. This native json format is completely transparent to applications and applications always use standard json documents, but all the database operations on json documents are optimized under the covers via binary format.
 
OSON is based on tree encoding and a simple json documents are serialized into OSON byte array having tree pointers for easy navigation offsets.
 
Let’s compare the text storage of json documents with json datatypes
 
demo@ATP21C> create table test_json(x number,y json);
 
Table created.
 
demo@ATP21C> create table test_text(x number,y clob check(y is json format json) );
 
Table created.
 
Now the table “big_table” got loaded with some 100K rows(copy of all_objects) and the average size of json document sized 620 bytes in size.
 
demo@ATP21C> select round(avg(length(json_object(*))),2) size_bytes  from big_table;
 
SIZE_BYTES
----------
    622.89
 
To make it big, let’s aggregate that into a bigger json document using SQL/JSON function’s and now it has grown into a bigger size of 59.5 MB
 
demo@ATP21C> select round(length(json_arrayagg(json_object(*) returning blob)),2) size_bytes from big_table;
 
SIZE_BYTES
----------
  62388939
 
When we inserted that bigger json document into our test table
 
demo@ATP21C> insert into test_json(x,y)
  2  select 1,json_arrayagg(json_object(*) returning blob)
  3  from big_table;
 
1 row created.
 
demo@ATP21C> insert into test_text(x,y)
  2  select x, json_serialize(y returning clob) from test_json;
 
1 row created.
 
Now the size of large json document from both tables are
 
demo@ATP21C> select round( length(y)/1024/1024,2) size_mb from test_text;
 
   SIZE_MB
----------
      59.5
 
demo@ATP21C> select round( length(y)/1024/1024,2) size_mb from test_json;
 
   SIZE_MB
----------
     27.63
 
Pretty much the json datatype size half the storage of text-based storage for json documents. The reason for this half the storage for new json datatype is that the json document it got many repeated json objects as array, so each “key” got repeated within the array and these repeated “keys” with the documents are replaced with ID’s and result in efficient reduction in space.
 
Now let’s see the effect of JSON datatype on many small documents instead of one large document.
Let’s reload the above two tables as every row as json document into each table.
 
demo@ATP21C> truncate table test_json;
 
Table truncated.
 
demo@ATP21C> truncate table test_text;
 
Table truncated.
 
demo@ATP21C> insert all
  2     into test_json(x,y) values(r,j)
  3     into test_text(x,y) values(r,j)
  4  select rownum r, json_object(*) j
  5  from big_table;
 
200000 rows created.
 
demo@ATP21C> select round( sum(length(y))/1024/1024,2) size_mb from test_text;
 
   SIZE_MB
----------
      59.4
 
demo@ATP21C> select round( sum(length(y))/1024/1024,2) size_mb from test_json;
 
   SIZE_MB
----------
     64.63
 
demo@ATP21C>
 
now the json datatype got little bit bigger, because each json document persisted in that datatype is self-contained and we got no repeated “keys” there to benefit the compression.