How do I exclude unchanged fields in Flashback query resultset?

Question

Is it possible to exclude unchanged fields in Flashback query resultset?

Consider I have following table

create table first_table
(
    id int generated as identity,
    name NVARCHAR2(1024),
    age smallint,
    notebook nclob,
    userpic clob,
    salary float
)

If the table has very frequent updates (e.g. on notebook field) following versioned query

select ROWID, VERSIONS_OPERATION, VERSIONS_STARTSCN, VERSIONS_STARTTIME, VERSIONS_XID, id, name, age, notebook, userpic, salary
from FIRST_TABLE versions between scn 1469193 and 1482882;

will pull heavy userpic value for every row even though it's the same.

Can I somehow avoid that and instead get NULLs for unchanged values ?

Answer 1

You can use LAG() Analytic Function in order to compare a column's value for the current row with the previous row. So, pick any column's value unmatched its value of LAG to display only the changes while keeping others NULL as

SELECT versions_starttime, f.ID, f.name, f.age,
       CASE WHEN NVL(TO_CHAR(f.notebook),' ') != 
                 NVL(LAG(TO_CHAR(f.notebook)) OVER 
                     (PARTITION BY TO_CHAR(f.notebook) 
                          ORDER BY VERSIONS_STARTTIME NULLS FIRST),' ')
            THEN
                 f.notebook
             END AS notebook, 
       CASE WHEN NVL(TO_CHAR(f.userpic),' ') != 
                 NVL(LAG(TO_CHAR(f.userpic)) OVER 
                     (PARTITION BY TO_CHAR(f.userpic) 
                          ORDER BY VERSIONS_STARTTIME NULLS FIRST),' ')
            THEN
                 f.userpic
             END AS userpic,
       f.salary                       
  FROM first_table 
  VERSIONS BETWEEN SCN 1469193 AND 1482882 f