Triggers and constraints: diagnostic mechanism, part II

The following question we can ask - what the column DATA represents in the index
structure ? Of course it's not rowid.
The most reasonable suggestion is what Oracle keeps in this index-like structure
the pairs "key - count" for the constraint. Each pair reflects the number of times the constraint voilation
has been detected for particular key value. Each DML operation executed in the current transaction
can change this information.
OK, let's check this suggestion.

SQL> begin
2 execute immediate 'set constraint all deferred';
3 for i in 1..257 loop
4 insert into child_t values(i, mod(i,4));
5 end loop;
6 end;
7 /

PL/SQL procedure successfully completed.

The dump shows:

row#0[8024] flag: ------, lock: 0, len=8, data:(4): 40 00 00 00
col 0; len 1; (1): 80
row#1[8015] flag: ------, lock: 0, len=9, data:(4): 41 00 00 00
col 0; len 2; (2): c1 02
row#2[8006] flag: ------, lock: 0, len=9, data:(4): 40 00 00 00
col 0; len 2; (2): c1 03
row#3[7997] flag: ------, lock: 0, len=9, data:(4): 40 00 00 00
col 0; len 2; (2): c1 04

Really:

SQL> select y, to_char(count(*),'XX') hex
2 from child_t group by y
3 order by 1
4 /

Y HEX
---------- ---
0 40
1 41
2 40
3 40

Looks truth, right ?

Ok, let's remove some rows with y = 1:

SQL> delete from child_t where y = 1 and x <= 127;

32 rows deleted.

SQL> select y, to_char(count(*),'XX') hex
2 from child_t group by y
3 order by 1
4 /

Y HEX
---------- ---
0 40
1 21
2 40
3 40

Dump is:

row#0[8024] flag: ------, lock: 0, len=8, data:(4): 40 00 00 00
col 0; len 1; (1): 80
row#1[8015] flag: ------, lock: 2, len=9, data:(4): 21 00 00 00
col 0; len 2; (2): c1 02
row#2[8006] flag: ------, lock: 0, len=9, data:(4): 40 00 00 00
col 0; len 2; (2): c1 03
row#3[7997] flag: ------, lock: 0, len=9, data:(4): 40 00 00 00
col 0; len 2; (2): c1 04

Now remove all rows with Y=1:


row#0[8024] flag: ------, lock: 0, len=8, data:(4): 40 00 00 00
col 0; len 1; (1): 80
row#1[8015] flag: ---D--, lock: 2, len=9, data:(4): 01 00 00 00
col 0; len 2; (2): c1 02
row#2[8006] flag: ------, lock: 0, len=9, data:(4): 40 00 00 00
col 0; len 2; (2): c1 03
row#3[7997] flag: ------, lock: 0, len=9, data:(4): 40 00 00 00
col 0; len 2; (2): c1 04

As we can see Oracle doesn't set count to zero when it has to - instead,
it marks the index record as deleted.

If after that we input new record with y = 1 the key is actual again:

row#0[8024] flag: ------, lock: 0, len=8, data:(4): 40 00 00 00
col 0; len 1; (1): 80
row#1[8015] flag: ------, lock: 2, len=9, data:(4): 01 00 00 00
col 0; len 2; (2): c1 02
row#2[8006] flag: ------, lock: 0, len=9, data:(4): 40 00 00 00
col 0; len 2; (2): c1 03
row#3[7997] flag: ------, lock: 0, len=9, data:(4): 40 00 00 00
col 0; len 2; (2): c1 04

If we input parent row for key value = 2:

SQL> insert into parent_t values(2);

1 row created.

when Oracle marks index row as deleted but doesn't change DATA value:

row#0[8024] flag: ------, lock: 0, len=8, data:(4): 40 00 00 00
col 0; len 1; (1): 80
row#1[8015] flag: ------, lock: 2, len=9, data:(4): 01 00 00 00
col 0; len 2; (2): c1 02
row#2[8006] flag: ---D--, lock: 2, len=9, data:(4): 40 00 00 00
col 0; len 2; (2): c1 03
row#3[7997] flag: ------, lock: 0, len=9, data:(4): 40 00 00 00
col 0; len 2; (2): c1 04

If now we remove all rows with Y = 2

SQL> delete from child_t where y = 2;

64 rows deleted.


Oracle does nothing with index

row#0[8024] flag: ------, lock: 0, len=8, data:(4): 40 00 00 00
col 0; len 1; (1): 80
row#1[8015] flag: ------, lock: 2, len=9, data:(4): 01 00 00 00
col 0; len 2; (2): c1 02
row#2[8006] flag: ---D--, lock: 2, len=9, data:(4): 40 00 00 00
col 0; len 2; (2): c1 03
row#3[7997] flag: ------, lock: 0, len=9, data:(4): 40 00 00 00
col 0; len 2; (2): c1 04

The removement of the parent record doesn't affect the index too:

SQL> delete from parent_t where x = 2;

1 row deleted.


row#0[8024] flag: ------, lock: 0, len=8, data:(4): 40 00 00 00
col 0; len 1; (1): 80
row#1[8015] flag: ------, lock: 2, len=9, data:(4): 01 00 00 00
col 0; len 2; (2): c1 02
row#2[8006] flag: ---D--, lock: 2, len=9, data:(4): 40 00 00 00
col 0; len 2; (2): c1 03
row#3[7997] flag: ------, lock: 0, len=9, data:(4): 40 00 00 00
col 0; len 2; (2): c1 04

But if now we insert several child records with this key, Oracle
marks the index record as actual and resets DATA value:

SQL> insert into child_t select rownum, 2 from dual
2 connect by level <= 10;

10 rows created.

SQL> select y, to_char(count(*),'XX') hex
2 from child_t group by y
3 order by 1
4 /

Y HEX
---------- ---
0 40
1 1
2 A
3 40


row#0[8024] flag: ------, lock: 0, len=8, data:(4): 40 00 00 00
col 0; len 1; (1): 80
row#1[8015] flag: ------, lock: 2, len=9, data:(4): 01 00 00 00
col 0; len 2; (2): c1 02
row#2[8006] flag: ------, lock: 2, len=9, data:(4): 0a 00 00 00
col 0; len 2; (2): c1 03
row#3[7997] flag: ------, lock: 0, len=9, data:(4): 40 00 00 00
col 0; len 2; (2): c1 04

Triggers and constraints: diagnostic mechanism, part I

Throughout the prevoius topic the essential question is - what is diagnostic area, where it is located and what mechanism Oracle uses to handle it.
Let's check what is temp segment usage in our case. Maybe we'll see something interested.

SQL> create table parent_t (x int);

Table created.

SQL> create table child_t (x int, y int);

Table created.

SQL> alter table parent_t add constraint parent_t_pk primary key (x);

Table altered.

SQL> alter table child_t add constraint child_t_fk

2 foreign key (y) references parent_t (x) deferrable;

Table altered.

At the beginning we will execute INSERT statement which should not generate any constraint violations for not-deferred constraint mode and check temp segment usage.

SQL> insert into child_t select rownum, null from dual connect by level <= 10000;  10000 rows created.  SQL> select sid from v$mystat where rownum = 1;

       SID
----------
       127

SQL> select x.segtype, x.extents, x.blocks, x.segfile#, x.segblk#
  2  from v$tempseg_usage x, v$session y
  3  where x.session_addr = y.saddr and y.sid = 127
  4  /

no rows selected

Well, nothing unexpected.

Now let's set DEFERRED MODE and insert next 10000 rows:

SQL> set constraint all deferred;

Constraint set.

SQL> insert into child_t select rownum, null from dual connect by level <= 10000;  10000 rows created.  SQL> select x.segtype, x.extents, x.blocks, x.segfile#, x.segblk#
  2  from v$tempseg_usage x, v$session y
  3  where x.session_addr = y.saddr and y.sid = 127
  4  /

no rows selected

And finally let's insert rows what force constraint violation - Y column value
will contain values from 0 to 3 inclusively and no one has parent key:

SQL> insert into child_t select rownum, mod(rownum,4) from dual connect by level <= 10000;  

10000 rows created.  

SQL> select x.segtype, x.extents, x.blocks, x.segfile#, x.segblk#, x.segrfno#
  2  from v$tempseg_usage x, v$session y
  3  where x.session_addr = y.saddr and y.sid = 127
  4  /

SEGTYPE      EXTENTS     BLOCKS   SEGFILE#    SEGBLK#   SEGRFNO#
--------- ---------- ---------- ---------- ---------- ----------
INDEX              1        128        202     255753          2

Now we see temporary segment usage and it's type is INDEX.

Let's dump sereval blocks from this structure.

SQL> alter system dump tempfile 2 block min 255753 block max 255760;

System altered.

We will see segment header and index leaf block:

Start dump data blocks tsn: 3 file#: 2 minblk 255753 maxblk 255880
buffer tsn: 3 rdba: 0x0083e709 (2/255753)
scn: 0x0875.7bd4b765 seq: 0x00 flg: 0x08 tail: 0xb7651000
frmt: 0x02 chkval: 0x0000 type: 0x10=DATA SEGMENT HEADER - UNLIMITED
  Extent Control Header
  -----------------------------------------------------------------
  Extent Header:: spare1: 0      spare2: 0      #extents: 1      #blocks: 127
                  last map  0x00000000  #maps: 0      offset: 4128
      Highwater::  0x0083e70b  ext#: 0      blk#: 1      ext size: 127
  #blocks in seg. hdr's freelists: 0
  #blocks below: 1
  mapblk  0x00000000  offset: 0
                   Unlocked
     Map Header:: next  0x00000000  #extents: 1    obj#: 8644361 flag: 0x40000000
  Extent Map
  -----------------------------------------------------------------
   0x0083e70a  length: 127

  nfl = 1, nfb = 1 typ = 2 nxf = 0 ccnt = 0
  SEG LST:: flg: UNUSED lhd: 0x00000000 ltl: 0x00000000 

buffer tsn: 3 rdba: 0x0083e70a (2/255754)
scn: 0x0875.7bd4b7d3 seq: 0x00 flg: 0x08 tail: 0xb7d30600
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Block header dump:  0x0083e70a
 Object id on Block? Y
 seg/obj: 0x83e709  csc: 0x875.7bd4b764  itc: 2  flg: -  typ: 2 - INDEX
     fsl: 0  fnx: 0x0 ver: 0x01

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x02   0x0006.02b.000050aa  0x00873a96.165c.02  ----    4  fsc 0x0000.00000000

Leaf block dump
===============
header address 242391644=0xe729a5c
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 1
kdxcosdc 0
kdxconro 4
kdxcofbo 44=0x2c
kdxcofeo 7997=0x1f3d
kdxcoavs 7953
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 0=0x0
kdxledsz 4
kdxlebksz 8032
row#0[8024] flag: ------, lock: 2, len=8, data:(4):  c4 09 00 00
col 0; len 1; (1):  80
row#1[8015] flag: ------, lock: 2, len=9, data:(4):  c4 09 00 00
col 0; len 2; (2):  c1 02
row#2[8006] flag: ------, lock: 2, len=9, data:(4):  c4 09 00 00
col 0; len 2; (2):  c1 03
row#3[7997] flag: ------, lock: 2, len=9, data:(4):  c4 09 00 00
col 0; len 2; (2):  c1 04

Let's now delete from the CHILD_T table all rows with y = 2 and check
if there will be any changes in this segment:

SQL> delete from child_t where y = 1;

2500 rows deleted.

row#0[8024] flag: ------, lock: 2, len=8, data:(4):  c4 09 00 00
col 0; len 1; (1):  80
row#1[8015] flag: ---D--, lock: 2, len=9, data:(4):  01 00 00 00
col 0; len 2; (2):  c1 02
row#2[8006] flag: ------, lock: 2, len=9, data:(4):  c4 09 00 00
col 0; len 2; (2):  c1 03
row#3[7997] flag: ------, lock: 2, len=9, data:(4):  c4 09 00 00
col 0; len 2; (2):  c1 04

OK, the record for key value = 1 has been marked as deleted.

Now go forward and create parent row for Y=2:

SQL> insert into parent_t values(2);

1 row created.

As we can see, now the row for key value = 2 has been marked as DELETED too:

row#0[8024] flag: ------, lock: 2, len=8, data:(4):  c4 09 00 00
col 0; len 1; (1):  80
row#1[8015] flag: ---D--, lock: 2, len=9, data:(4):  01 00 00 00
col 0; len 2; (2):  c1 02
row#2[8006] flag: ---D--, lock: 2, len=9, data:(4):  c4 09 00 00
col 0; len 2; (2):  c1 03
row#3[7997] flag: ------, lock: 2, len=9, data:(4):  c4 09 00 00
col 0; len 2; (2):  c1 04

And finally we can create parent row for Y=0 in AUTONOMOUS transaction:

SQL> declare
  2   pragma autonomous_transaction;
  3  begin
  4   insert into parent_t values(0);
  5   commit;
  6  end;
  7  /

PL/SQL procedure successfully completed.

row#0[8024] flag: ------, lock: 2, len=8, data:(4):  c4 09 00 00
col 0; len 1; (1):  80
row#1[8015] flag: ---D--, lock: 2, len=9, data:(4):  01 00 00 00
col 0; len 2; (2):  c1 02
row#2[8006] flag: ---D--, lock: 2, len=9, data:(4):  c4 09 00 00
col 0; len 2; (2):  c1 03
row#3[7997] flag: ------, lock: 2, len=9, data:(4):  c4 09 00 00
col 0; len 2; (2):  c1 04

No change - as we could reasonably expect.

Now touch rows in the table with Y=0, but don't touch Y value itself:

SQL> update child_t set x = x where y = 0;

2500 rows updated.

No changes in the leaf block:

row#0[8024] flag: ------, lock: 2, len=8, data:(4):  c4 09 00 00
col 0; len 1; (1):  80
row#1[8015] flag: ---D--, lock: 2, len=9, data:(4):  01 00 00 00
col 0; len 2; (2):  c1 02
row#2[8006] flag: ---D--, lock: 2, len=9, data:(4):  c4 09 00 00
col 0; len 2; (2):  c1 03
row#3[7997] flag: ------, lock: 2, len=9, data:(4):  c4 09 00 00
col 0; len 2; (2):  c1 04

But if we touch Y column in UPDATE statement

SQL> update child_t set y = y where y = 0;

2500 rows updated.

we can see what the row with key value = 0 is marked as deleted - because
now the parent key exists:

row#0[8024] flag: ---D--, lock: 2, len=8, data:(4):  01 00 00 00
col 0; len 1; (1):  80
row#1[8015] flag: ---D--, lock: 2, len=9, data:(4):  01 00 00 00
col 0; len 2; (2):  c1 02
row#2[8006] flag: ---D--, lock: 2, len=9, data:(4):  c4 09 00 00
col 0; len 2; (2):  c1 03
row#3[7997] flag: ------, lock: 2, len=9, data:(4):  c4 09 00 00
col 0; len 2; (2):  c1 04

Triggers and constraints

Oracle documentation specifies the Trigger Execution Model which explains how triggers counteract with integrity constraints. One interesting question concerns to the intergity conception and Oracle internal mechanisms of constraint checking.

Let's do the following:

SQL> create table t (x int primary key);

Table created.

SQL> create table t_r(x int references t(x), y int)
2 /

Table created.

SQL> create or replace trigger aifer_usual
2 after insert on t_r
3 for each row
4 begin
5 dbms_output.put_line(:new.y);
6 if :new.y = 6 then
7 insert into t values(:new.x);
8 end if;
9 end;
10 /

Trigger created.

SQL> create or replace trigger aifer_usual
2 after insert on t_r
3 for each row
4 begin
5 dbms_output.put_line(:new.y);
6 if :new.y = 6 then
7 insert into t values(:new.x);
8 end if;
9 end;
10 /

Trigger created.

SQL> set serveroutput on
SQL> insert into t_r select 1, rownum from dual connect by level <= 8;
1
2
3
4
5
6
7
8

8 rows created.

SQL> select * from t;

X
----------
1

No surprices - everything works as it's described. Really, Oracle claims what the integrity has to be guaranteed at the end of SQL operator execution (for non-deferrable constraints):

"...If any of the results of a DML statement execution violate an integrity constraint, then Oracle rolls back the statement and returns an error...
...Even though a constraint is defined to verify that each mgr value matches
an empno value, this statement is legal because Oracle effectively performs
its constraint checking after the statement completes. Figure 21-11 shows that Oracle performs the actions of the entire SQL statement before any constraints are checked..."

But if we cover trigger code in an autonomous transaction, we will find than the result is differ:

SQL> roll;

Rollback complete.

SQL> create or replace trigger aifer_usual
2 after insert on t_r
3 for each row
4 declare
5 pragma autonomous_transaction;
6 begin
7 dbms_output.put_line(:new.y);
8 if :new.y = 6 then
9 insert into t values(:new.x);
10 end if;
11 commit;
12 end;
13 /

Trigger created.

SQL> insert into t_r select 1, rownum from dual connect by level <= 8;
1
2
3
4
5
6
7
8
insert into t_r select 1, rownum from dual connect by level <= 8
*
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.SYS_C0026536) violated - parent key not
found


SQL> select * from t;

X
----------
1

Why ? After all at the end of INSERT statement all rows in the table satisfy intergity constraint.

Of course we can think that the statement checks the constraint using a single point-in-time snapshot at SCN of the statement begin. But in the case of non-autonomous trigger the parent record did not exist too at the start point of operator - and INSERT was in success.

Probably the internal mechanism of Oracle is based on the transaction diagnostic area stack processing. The following example shows the basis for that conclusion.

Let's create the table with deferred constrain:

SQL> create table t_r(x int references t(x) deferrable initially deferred, y int);

Table created.

SQL> create or replace trigger aifer_usual
2 after insert on t_r
3 for each row
4 declare
5 pragma autonomous_transaction;
6 begin
7 dbms_output.put_line(:new.y);
8 if :new.y = 6 then
9 insert into t values(:new.x);
10 end if;
11 commit;
12 end;
13 /

Trigger created.

SQL> insert into t_r select 1, rownum from dual connect by level <= 8;
1
2
3
4
5
6
7
8

8 rows created.

SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02291: integrity constraint (SCOTT.SYS_C0026537) violated - parent key not
found

Notice what now at the end of transaction all data satisfy the intergity constraint too but the exception is raised like as in the example above.

But it's easy to correct.

SQL> delete from t;

1 row deleted.

SQL> commit;

Commit complete.

SQL> select * from t_r;

no rows selected

SQL> insert into t_r select 1, rownum from dual connect by level <= 8;
1
2
3
4
5
6
7
8

8 rows created.

SQL> delete from t_r where y <=6;

6 rows deleted.

SQL> commit;

Commit complete.

But if we leave in the table any row what was inserted before insertion of the parent row, we will have our usual exception:

SQL> delete from t_r;

2 rows deleted.

SQL> delete from t;

1 row deleted.

SQL> commit;

Commit complete.

SQL> insert into t_r select 1, rownum from dual connect by level <= 8;
1
2
3
4
5
6
7
8

8 rows created.

SQL> delete from t_r where y < 6;

5 rows deleted.

SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02291: integrity constraint (SCOTT.SYS_C0026537) violated - parent key not
found

So we can imagine the internal meachinsm of constraint checking as follows (the follwing text is no more that the speculation about possible internal Oracle mechanism):

1)

For each new row the integrity constraint is checked, based on the current single point-in-time snapshot.

2)

If the constraint is violated the exception information is placed in the diagnostic area stack.

3)

Than the statement / transaction ends Oracle checks the content of the diagnostic area stack and generates the exception if it is not empty.

4)

Any DML operator in the same transaction touches the error stack and can clear it
(if it's action corrects the prevoius errors). For this reason INSERT statement in the non-autonomous trigger "removes" errors generated by rows with y = 1...6 from the error stack. At the end of the basic INSERT statement the error stack is clear.

Autonomous DML operator can't touche the local error stack and can't clear it - because of this the basic INSERT failed dispite the presence of the parent row. When we use deferred constraint the diagnostic area error stack is not clear at the end of statement but is doesn't raise the exception - Oracle waits for the transaction end to check the error stack. When we use DELETE statement we remove the rows what are the cause of errors in the stack - so we clear the stack too.

ROWNUM and ORDER SIBLINGS BY

Oracle ROWNUM pseudocolumn is a tricky thing and the many people put into the following trap:

SQL> SELECT ename, rownum FROM emp WHERE rownum <= 5 ORDER BY ename;

ENAME  ROWNUM
------ ------
ALLEN       2
JONES       4
MARTIN      5
SMITH       1
WARD        3

We have a mess of rownums what is natural behaviour of Oracle (considering when rownum
is assigned to the row and when ORDER BY is executed).

But for ORDER SIBLINGS BY the behavoiur seems differ:

SQL> select ename, rownum from emp
2 start with ename = 'KING'
3 connect by prior empno = mgr
4 /

ENAME  ROWNUM
------ ------
KING        1
JONES       2
SCOTT       3
ADAMS       4
FORD        5
SMITH       6
BLAKE       7
ALLEN       8
WARD        9
MARTIN     10
TURNER     11
JAMES      12
CLARK      13
MILLER     14

Now we have natural result - hierarchy order corresponds to rownum sequence.
But if we include ORDER SIBLINGS BY ename, we will not get a mess in rownum as
we could expect:

SQL> select ename, rownum from emp
2 start with ename = 'KING'
3 connect by prior empno = mgr
4 order siblings by ename;

ENAME    ROWNUM
-------  ------
KING          1
BLAKE         2
ALLEN         3
JAMES         4
MARTIN        5
TURNER        6
WARD          7
CLARK         8
MILLER        9
JONES        10
FORD         11
SMITH        12
SCOTT        13
ADAMS        14

It looks like if Oracle retrieves child rows for each parent node, sorts them in accordance
with ORDER SIBLINGS BY clause and after that assignes rownums.
Unfortunately I couldn't find the details of this behaviour in Oracle documentation and Metalink.

SKIP LOCKED and the locking moment

SELECT ... FOR UPDATE statement locks data at the moment of a cursor opening.
It's a conceptual thing because SELECT ... FOR UPDATE has to return locked (serialized) consistent snapshot. Really, let's open FOR UPDATE cursor in transaction A:

SQL> REM Transaction A
SQL> var rc refcursor
SQL> exec open :rc for select ename from emp where ename in ('KING','ALLEN') for update;

PL/SQL procedure successfully completed.

Then in transaction B we can't lock 'KING' row until commit or rollback in transaction A:

SQL>REM We wait for A completion
SQL> select ename from emp where ename = 'KING' for update;
...

FOR UPDATE SKIP LOCKED voilates the concept of serialized snapshot - and now we
don't need to try to lock rows before fetch:

SQL>REM Transaction A
SQL> exec open :rc for select ename from emp where ename in ('KING','ALLEN') for update skip locked;

PL/SQL procedure successfully completed.

We can lock 'KING' in another transaction (no lock before fetch):

SQL>REM Transaction B
SQL> select ename from emp where ename = 'KING' for update;

ENAME
----------
KING

Fetching of SKIP LOCKED cursor ignores 'KING'...

SQL>REM Transaction A
SQL> print rc

ENAME
----------
ALLEN

...and locks 'ALLEN' row at the moment of fetching (we are waiting for commit/rollback in A):

SQL> REM Transaction B - we are waitnig for A completion
SQL> select ename from emp where ename = 'ALLEN' for update;
...

Read consistency and flashback query

In one of Oracle professional forums the question has been asked: how can I see the history of changes made by my transaction ? One of the answers was: use a flashback query. This is a wrong answer.

When we query data Oracle allowes us to view only changes committed before SCN of query start or changes made by our transaction before the beginning of query. Oracle uses the undo information to reconstruct the required versionsof blocks. they are known as CR (consistent read) versions of blocks. In fact it has to rollback all transactions in blocks what have not been committed before the query beginning. At the same time Oracle can have to perform "partial rollback" for our own transaction.The example below illustrates it:

SQL> var rc refcursor
SQL> select * from t;

no rows selected

SQL> insert into t values(1); -- Changes before cursor open

1 row created.

SQL> exec open :rc for select * from t; -- Cursor open, SCN assigned

PL/SQL procedure successfully completed.

SQL> -- Change data after the query beginning.
SQL> -- Our transaction changes the same block using the same ITL slot
SQL>
SQL> insert into t values(2);

1 row created.

SQL>-- Oracle has to perform partial rollback
SQL>-- in our transaction to recover consistent version of block.

SQL>-- It has to rollback "insert into t values(2)"
SQL>
SQL> print rc

X
-------------------------
1

SQL> exec open :rc for select * from t; -- Now we have new query SCN > SCN of changes

PL/SQL procedure successfully completed.

SQL> print rc

X
-------------------------
1
2

Everything works as it stated in concepts. But it's easy to rollback the transaction in the block entirely - owing to RBA information in the block ITL slot and ability to definethe transaction status using the transaction table in undo header. But what to do if we need to rollback our own transaction partially - up to the last change before the query beginning ? It's not a trivial question.The undo records don't contain the information about SCN, andITL slot contains only Commit SCN, and the block header contains the information about SCN of last changes.
Probably Oracle uses the same mechanism to provide the consistent read and statement-level rollback - statement-level system savepoints. In this case Oracle knows to what point it should rollback the data in the block.
In that sense flashback query works differ than a usual select query when we talk about seeing the changes made by our transaction. Because there is not any relation between SCN in "AS OF SCN ()" clause and the system-level savepoint of the query, Oracle is not able to define the boundary for the partial rollback. Moreover, even if we had the relation between SCN and undo entry, we still would not able to provide the correct partial rollback. Savepoints separate one SQL operator from another (because the transaction performs operators in series). But not SCNs do. So if we rollbacked our transaction to the certain SCN we would have a risk to get to the "middle" of DML operator - and get to the inconsistent data snapshot.
In flashback queries Oracle doesn't have any alternative as to rollback ALL changes what have not been committed before specified SCN, including the changes of our transaction. So it should be taken into account when we try to use flashback queries.

SQL> insert into t values(1);

1 row created.

SQL> select * from t;

X
-------------------------

1

SQL> select dbms_flashback.get_system_change_number scn from dual;

SCN
-------------------
9300655466989

SQL> select * from t as of scn (9300655466989);

no rows selected

NOT IN and NULL in multi-column case

When I interview people I usually ask the question like this:

Suppose I have table T ( ID INT, NAME VARCHAR2(10)) and three row in that:

ID NAME
---- ---------
1 ALLEN
2 SCOTT
3 WARD

What is the result of the query

SELECT * FROM T WHERE (ID, NAME) NOT IN ((3, 'ALLEN'), (2,NULL)) ?

I am quite surpised how many people answer "no one row" on this question.
Probably it is the result of the confusion with the fact what the query

SELECT * FROM T WHERE (NAME) NOT IN ('ALLEN', NULL)

returns no rows.

This is the example how the common sense can play a bad joke.

In reality the expression

(ID, NAME) NOT IN ((3 'ALLEN'), (2,NULL))

is the equivalent of

(ID, NAME) != (3, 'ALLEN') AND (ID, NAME) != (2, NULL)

We can decompose this expression deeper so we will have

(ID != 3 OR NAME != 'ALLEN') AND (ID != 2 OR NAME != NULL)

So now it's clear what the result of the estimation for the record

1, ALLEN

is:

(TRUE OR FALSE) AND (TRUE OR NULL) == TRUE AND TRUE == TRUE

The estimation for (2, SCOTT) is

(TRUE OR TRUE) AND (FALSE OR NULL) == TRUE AND NULL == NULL

and (3, WARD) estimated as

(FALSE OR TRUE) AND (TRUE OR NULL) == TRUE AND TRUE == TRUE

So we have the final result

1, ALLEN
3, WARD


You will have "no rows returned" result only in case

NOT IN ((...), (...), (NULL,NULL,...,NULL)) where (NULL,NULL,...,NULL) is the cartige
contained only NULL values.