0

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 ?

2
  • smallint is an alias to NUMBER(38) (which is NOT small). NVARCHAR2/NCLOB may not be needed if your DB is using character set similar to AL32UTF8. Pictures are usually stored as BLOB (not CLOB) along with the mime-type of the picture in a separate column. Oct 3 '20 at 11:32
  • @MichaelKutz Thank you, Michael. This table is just to illustrate the problem. It's not used in production. Is there a way to excluded unchanged fields from versioned response?
    – expert
    Oct 3 '20 at 12:13
1

Is there a way to excluded unchanged fields from versioned response?

Not really.

Flashback uses the information found in UNDO. Flashback Data Archive stores the results from the Log files in a table.

So, you get ALL of the data when you do a Flashback query.

The common method is to use LAG + DECODE. But, I'm afraid that this will problematic for your LOB columns.

Personally, I'd pawn off the problem to the UI (Display Tier).

1
  • Thanks! I noticed that if I pull data directly from history tables LOB columns have NULL value. Could you please point me to the documentation where this behavior is explained?
    – expert
    Oct 3 '20 at 14:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.