Dmitry Nikiforov's blog

Triggers and constraints: diagnostic mechanism, part II

2009-05-26T22:55:00.000-07:00

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

2009-05-26T01:53:00.000-07:00

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

2009-03-19T11:10:00.001-07:00

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

2009-03-04T06:05:00.000-08:00

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

2009-03-04T04:10:00.000-08:00

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

2009-02-25T21:46:00.000-08:00

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

2009-02-22T11:27:00.000-08:00

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.

NEXTVAL and CURRVAL in Oracle 11G

2008-01-30T03:58:00.000-08:00

Oracle 11G documentation states what the direct access to NEXTVAL and CURRVAL improves the performance impact. But seems this change is a feature to minimize coder's work, no more.
The trace results are below:

declare
x int;
begin
for i in 1..100000 loop
x := sq_test.nextval;
end loop;
end;

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 12.76 13.69 0 0 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 12.76 13.69 0 0 0 1

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 20

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 0.00 0.00
********************************************************************************

SQL ID : 3uvw0xca08wut
Select SQ_TEST.NEXTVAL
from
dual

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.08 0 0 0 0
Execute 99999 3.64 3.59 0 0 0 0
Fetch 99999 2.51 2.61 0 0 0 99999
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 199999 6.17 6.29 0 0 0 99999

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWSParsing user id: 20 (recursive depth: 1)

It looks like x := sq_test.nextval transforms implicitly into select sq_test.nextval into x from dual. :)

Cursor FOR LOOP vs. OPEN-CLOSE in relation with exceptions

2007-06-15T00:17:00.000-07:00

One of the advantage of cursor FOR-LOOP cycle over ordinar OPEN-FETCH-CLOSE method
is the automatic close the cursor even it's scope is global. In other words FOR-LOOP
automatically closes the cursor in case of exit from the cycle over the exception:

SQL> create or replace package my_pkg
2 is
3 cursor s is select * from emp;
4 end;
5 /

Package created.

SQL> create or replace function f1
2 return number
3 is
4 begin
5 for v in my_pkg.s loop
6 dbms_output.put_line(v.ename);
7 raise no_data_found;
8 end loop;
9 return 1;
10 end;
11 /

Function created.

SQL> declare
2 c int;
3 begin
4 c := f1;
5 exception
6 when others then
7 if my_pkg.s%isopen then
8 dbms_output.put_line('Open');
9 elsif not my_pkg.s%isopen then
10 dbms_output.put_line('Closed');
11 end if;
12 end;
13 /
SMITH
Closed

PL/SQL procedure successfully completed.

SQL> create or replace function f1
2 return number
3 is
4 v my_pkg.s%ROWTYPE;
5 begin
6 open my_pkg.s;
7 fetch my_pkg.s into v;
8 dbms_output.put_line(v.ename);
9 raise no_data_found;
10 close my_pkg.s;
11 return 1;
12 end;
13 /

Function created.

SQL> declare
2 c int;
3 begin
4 c := f1;
5 exception
6 when others then
7 if my_pkg.s%isopen then
8 dbms_output.put_line('Open');
9 elsif not my_pkg.s%isopen then
10 dbms_output.put_line('Closed');
11 end if;
12 end;
13 /
SMITH
Open

PL/SQL procedure successfully completed.

UPDATE option of MERGE and ordinar MERGE

2007-05-31T23:27:00.000-07:00

Q: Can I use MERGE with UPDATE option instead ordinar UPDATE.

Yes, you can but in a single-user environment. MERGE doesn't work like ordinar UPDATE in multi-user system.

Let's create two tables:

SQL> create table t_s (on_column number(2), name varchar2(10))
2 /

Table created.

SQL> create table t_d (id number, on_column number(2), name varchar2(10))
2 /

Table created.

SQL> insert into t_s values(10,'table 1')
2 /

1 row created.

SQL> insert into t_s values(20,'table 1')
2 /

1 row created.

SQL> begin
2 for i in 1..4 loop
3 insert into t_d values(i,20,null);
4 end loop;
5 commit;
6 end;
7 /

PL/SQL procedure successfully completed.

SQL> alter table t_s add constraint t_s_pk primary key (on_column)
2 /

Table altered.

Now we open the new Oracle session and change ON_COLUMN value from 20 to 50 of T_D table in one of the row:

SQL> update t_d set on_column = 50 where id = 3;

1 row updated.

Now in the first session we update NAME column of T_D the using corresponding
value of T_S (ordinar UPDATE):

SQL> update t_d
2 set name =
3 (select name from t_s where t_s.on_column = t_d.on_column)
4 where exists
5 (select null from t_s where t_s.on_column = t_d.on_column)
6 /

Naturally it hangs until COMMIT in the second session:

SQL> update t_d set on_column = 50 where id = 3;

1 row updated.

SQL> commit;

Commit complete.

Now we have the results of UPDATE:

SQL> update t_d
2 set name =
3 (select name from t_s where t_s.on_column = t_d.on_column)
4 where exists
5 (select null from t_s where t_s.on_column = t_d.on_column)
6 /

3 rows updated.

SQL> select * from t_d;

ID ON_COLUMN NAME
---------- ---------- ----------
1 20 table 1
2 20 table 1
3 50
4 20 table 1

This is exactly what Oracle does using the principle of "serialized snapshot" for
DML statements. The same results we have for updatable in-line wiew:

SQL> update
2 (select t_s.name sname, t_d.name dname
3 from t_s, t_d where t_s.on_column = t_d.on_column)
4 set dname = sname
5 /

3 rows updated.

SQL> select * from t_d;

ID ON_COLUMN NAME
---------- ---------- ----------
1 20 table 1
2 20 table 1
3 50
4 20 table 1

MERGE works differ. Compare the results after the same actions:

SQL> merge into t_d
2 using t_s
3 on (t_s.on_column = t_d.on_column)
4 when matched then
5 update set t_d.name = t_s.name
6 /

4 rows merged.

SQL> select * from t_d;

ID ON_COLUMN NAME
---------- ---------- ----------
1 20 table 1
2 20 table 1
3 50 table 1
4 20 table 1

Constituing on Tom Kyte's explanation what MERGE statement emulates the code

for v in (select .... ) loop
update ....;
if sql%rowcount = 0 then
insert ....;
end if;
end loop;

we can think what MERGE operations work like WHERE CURRENT OF construction.
Oracle doesn't specify the details of the MERGE realization but
in favor of this theory two facts say. The first one is what
UPDATE clause of MERGE can't update joined columns of a destination table
- that is not prohibited in an ordinar UPDATE. The second fact is
we can force MERGE to work like an ordinar UPDATE using row-level triggers:

SQL> create or replace trigger force_like_update
2 before update on t_d
3 for each row
4 declare
5 on_column t_d.on_column%type;
6 begin
7 on_column := :old.on_column;
8 end;
9 /

Trigger created.

SQL> merge into t_d
2 using t_s
3 on (t_s.on_column = t_d.on_column)
4 when matched then
5 update set t_d.name = t_s.name
6 /

3 rows merged.

SQL> select * from t_d;

ID ON_COLUMN NAME
---------- ---------- ----------
1 20 table 1
2 20 table 1
3 50
4 20 table 1

This trigger causes the restart of MERGE because it uses ON_COLUMN value
and there are differ versions of the consistent read block and the current read
block (like it happens in an ordinar UPDATE).

CARDINALITY hint, collection and join

2007-02-08T23:44:00.000-08:00

CARDINALITY hint has an unexpected affect on the join results in Oracle 9.2.0.6:

The very strange behaviour has been detected for the following case:

SQL> desc arrayofnumbers
arrayofnumbers TABLE OF NUMBER(20)

SQL> desc tmp_log
Name Null? Type
----------------------------------------- -------- ----------------------------
LOG VARCHAR2(4000)

SQL> CREATE OR REPLACE
2 PROCEDURE tsp
3 (
4 root in VARCHAR2,
5 to_processes in arrayofnumbers,
6 targetProcesses out arrayofnumbers
7 )
8 AS
9 str varchar2(4000);
10 BEGIN
11
12 targetProcesses := arrayofnumbers();
13
14 str := ' r0='to_processes.COUNT;
15
16 select column_value BULK COLLECT INTO targetProcesses from TABLE(to_processes) p;
17
18 str := str ' r1='targetProcesses.COUNT;
19
20 SELECT /*+ cardinality(p 1 ) */ process_id BULK COLLECT INTO targetProcesses
21 FROM glob_data i, TABLE(to_processes) p
22 WHERE waiting=1 AND root = tsp.root AND i.process_id=p.column_value;
23
24 str := str ' r2='targetProcesses.COUNT;
25
26 INSERT INTO tmp_log(log)
27 VALUES ( str);
28
29
30 END;
31 /

Procedure created.

SQL> declare
2 targetProcesses arrayofnumbers := arrayofnumbers();
3 tP arrayofnumbers;
4 l_root varchar2(100) := 'ASF_MM_ActionCompleted';
5 begin
6
7 select process_id bulk collect into tP from glob_data
8 where waiting=1 AND root = l_root;
9
10 for i in 1..10 loop
11 tsp ( l_root, tP, targetProcesses);
12 commit;
13 end loop;
14
15 end;
16 /

PL/SQL procedure successfully completed.

SQL> select * from (select * from tmp_log order by 1 desc) where rownum <=10;
LOG --------------------------------------------------------------------------------
r0=1260 r1=1260 r2=1260
r0=1260 r1=1260 r2=1260
r0=1260 r1=1260 r2=1260
r0=1260 r1=1260 r2=1260
r0=1260 r1=1260 r2=1260
r0=1260 r1=1260 r2=1236
r0=1260 r1=1260 r2=1236
r0=1260 r1=1260 r2=1236
r0=1260 r1=1260 r2=1236
r0=1260 r1=1260 r2=1236

10 rows selected.

The only one user session existed in Oracle at that moment.
This effect has been revealed then new connection is opening in Oracle.
After the first execution of the block the effect can't be revealed.