Oracle (11.2.0.1):How to Identify the Row Which Is Currently Updated by the Update Statement

Oracle (11.2.0.1) : How to identify the row which is currently updated by the UPDATE statement

You can use the query to monitor long-running DML operations and rollback.
If the update field is not included in the index, then the value of the used_urec field from the v$transaction view will be very close to the number of rows.
When the update operation is performed, these values increase, if rollback is performed, the values are reduced to zero.

V$TRANSACTION lists the active transactions in the system.
    USED_UREC Number of undo records used
    USED_UBLK Number of undo blocks used
select
   substr(s.username,1,28) username,
   substr(s.program,1,25) program,
   s.command,
   t.used_urec,
   t.used_ublk,
   decode(s.command,
     0,'No Command',
     1,'Create Table',
     2,'Insert',
     3,'Select',
     6,'Update',
     7,'Delete',
     9,'Create Index',
     15,'Alter Table',
     21,'Create View',
     23,'Validate Index',
     35,'Alter Database',
     39,'Create Tablespace',
     41,'Drop Tablespace',
     40,'Alter Tablespace',
     53,'Drop User',
     62,'Analyze Table',
     63,'Analyze Index',
     s.command||': Other') command
from 
   v$session     s,
   v$process     p,
   v$transaction t
where s.paddr = p.addr
and s.taddr = t.addr 
order by 1

For example 1. If you update a column that is not indexed, then the number of rows 39915830 and USED_UREC 40000562 approximately coincide .

create table test_update(p1,p2,p3,p4 )
  PCTFREE     1
  INITRANS    1
  MAXTRANS    255
  TABLESPACE  arhiv_data
as 
SELECT a.n_p_u, a.id_reg, a.id_vag, a.vrsvop
  FROM a_vag_atr a;

SELECT count(*)
          FROM test_update a
==>
COUNT(*)                                     
-------------------------------------------- 
                                    39915830 

Session 1

update test_update 
set p2=1234567890
==>
39915830 row(s) updated

Session 2
start update

USERNAME       PROGRAM         COMMAND  USED_UREC   USED_UBLK   COMMAND_1                                             
---------------- ---------------------- ------------------- --------------------
ASUDS          sqlnavigator.exe      6  4181959    62690           Update       

stop update

USERNAME       PROGRAM         COMMAND  USED_UREC   USED_UBLK   COMMAND_1                                             
---------------- ---------------------- ------------------- --------------------
ASUDS          sqlnavigator.exe      6   40000562   601871       Update       

For example 2. if you update the field indexed then the number of lines * 3 is approximately the USED_UREC. 39915830 *3=~116705429

create table test_update(p1,p2,p3,p4 )
  PCTFREE     1
  INITRANS    1
  MAXTRANS    255
  TABLESPACE  arhiv_data
as 
SELECT a.n_p_u, a.id_reg, a.id_vag, a.vrsvop
  FROM a_vag_atr a;

SELECT count(*) FROM test_update a
==>
COUNT(*)                                     
-------------------------------------------- 
                                    39915830 

CREATE INDEX test_ind ON test_update
  (
    p1                              ASC
  )

Session 1

update test_update 
set p1=12
==>
39915830 row(s) updated

Session 2
stop update

USERNAME       PROGRAM         COMMAND  USED_UREC   USED_UBLK   COMMAND_1                                             
---------------- ---------------------- ------------------- --------------------
ASUDS          sqlnavigator.exe      6  116705429   1392538        Update       

For example 3. if you insert into table not indexed then the number of rows is exactly the USED_UREC.

create table test_update(p1,p2,p3,p4 )
      PCTFREE     1
      INITRANS    1
      MAXTRANS    255
      TABLESPACE  arhiv_data

 SELECT count(*)
              FROM test_update a
==>
    COUNT(*)                                     
    -------- 
          0

Session 1

declare
 i pls_integer:=1;
begin
for i in 1..500000 loop
insert into test_update(p1,p2,p3,p4)
values(1,2,3,sysdate); 
end loop;
end;

select count(*) from  test_update
==>
COUNT(*)                                     
----------- 
     500000

Session 2

USERNAME       PROGRAM         COMMAND  USED_UREC   USED_UBLK   COMMAND_1                                             

---

    ASUDS          sqlnavigator.exe      2     500000    5815    Insert

For example 4. if you delete from table not indexed then the number of rows is exactly the USED_UREC.

Session 1

 SELECT count(*) FROM test_update a
    ==>
        COUNT(*)                                     
        -------- 
         500000
delete from  test_update
==>
500000 row(s) deleted

Session 2

    USERNAME       PROGRAM         COMMAND  USED_UREC   USED_UBLK   COMMAND_1                                             
---------------- ---------------------- ------------------- --------------------
        ASUDS      sqlnavigator.exe   7      500000      9616         Delete  

How do I limit the number of rows returned by an Oracle query after ordering?

You can use a subquery for this like

select *
from  
( select * 
  from emp 
  order by sal desc ) 
where ROWNUM <= 5;

Have also a look at the topic On ROWNUM and limiting results at Oracle/AskTom for more information.

Update:
To limit the result with both lower and upper bounds things get a bit more bloated with

select * from 
( select a.*, ROWNUM rnum from 
  ( <your_query_goes_here, with order by> ) a 
  where ROWNUM <= :MAX_ROW_TO_FETCH )
where rnum  >= :MIN_ROW_TO_FETCH;

(Copied from specified AskTom-article)

Update 2:
Starting with Oracle 12c (12.1) there is a syntax available to limit rows or start at offsets.

SELECT * 
FROM   sometable
ORDER BY name
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

See this answer for more examples. Thanks to Krumia for the hint.

Oracle 11g: Compound triggers with an on delete cascade constraint

I figured it out. The Bug 8830338 - BEFORE and AFTER STATEMENT not executed in compound trigger for DELETE CASCADE (Doc ID 8830338.8) for 11.2.0.1 was involved into my fun with triggers. It's claimed to be fixed since the patchset 11.2.0.2, ever since such a compound trigger with an after statement should've been fired and ended up in a bad way.

Oracle query execution plan

I can not reproduce this in 11.2.0.3, I don't think there is a logical explanation for this behavior other than: you hit a bug, that apparently is solved in 11.2.0.3.

One thing that jumped immediately in my eye is the lack of object statistics and - if your output was complete - the fact that OPTIMIZER_DYNAMIC_SAMPLING is set to 0. You could try to reproduce with OPTIMIZER_DYNAMIC_SAMPLING=2. In that case the dynamic sampler kicks in if the object statistics are missing. BTW: don't use this feature instead of correct optimizer statistics. More info about dynamic sampling Dynamic sampling and its impact on the Optimizer

In your - nice documented - question and script/test case you try to make use of append and nologging. This only works for bulk inserts, not for row inserts with values. What would happen is for every insert: push-up the highwater mark and dump a full block of data in the free block, in your case that would have only 1 row .... Luckily, the database ignores this instruction.

Before you fire SQL to a table, make sure that you give it optimizer statistics. This will certainly help your case.

Cycle detection with recursive subquery factoring

From documentation on CONNECT_BY_ISCYCLE:

The CONNECT_BY_ISCYCLE pseudocolumn returns 1 if the current row has a child which is also its ancestor

and that on CYCLE:

A row is considered to form a cycle if one of its ancestor rows has the same values for the cycle columns.

In your example, row 2 does have a child which is also its ancestor, but its id has not been returned yet.

In other words, CONNECT_BY_ISCYCLE checks the children (which are yet to be returned), while CYCLE checks the current row (which is already returned).

CONNECT BY is row based, while recursive CTE's are set-based.

Note that Oracle's documentation on CYCLE mentions an "ancestor row". However, generally speaking, there is no concept of an "ancestor row" in a recursive CTE. It's a set based operation which can yield results completely out of the tree. Generally speaking, the anchor part and the recursive part can even use the different tables.

Since recursive CTE's are usually used to build hierarchy trees, Oracle decided to add a cycle check. But due the set-based way the recursive CTE's operate, it's generally impossible to tell will the next step generate a cycle or not, because without a clear definition of the "ancestor row" cycle condition cannot be defined either.

To perform the "next" step, the whole "current" set needs to be available, but to generate each row of the current set (which includes the cycle column) we just need to have the results of the "next" operation.

It's not a problem if the current set always consists of a single row (like in CONNECT BY), but it is a problem if the recursive operation defined on a set as a whole.

Didn't look into Oracle 11 yet, but SQL Server implements recursive CTE's by just hiding a CONNECT BY behind them, which requires placing numerous restrictions (all of which effectively forbid all set-based operations).

PostgreSQL's implementation, on the other hand, is truly set-based: you can do any operation with the anchor part in the recursive part. It does not have any means to detect cycles, though, because cycles are not defined in the first place.

As was mentioned before, MySQL does not implement CTE's at all (it does not implement HASH JOIN's or MERGE JOINs as well, only the nested loops, so don't be surprised much).

Ironically, I received a letter today on this very subject, which I will cover in my blog.

Update:

Recursive CTE's in SQL Server are no more than CONNECT BY in disguise. See this article in my blog for shocking details:

• SQL Server: are the recursive CTE’s really set-based?

Insert if not exists Oracle

The statement is called MERGE. Look it up, I'm too lazy.

Beware, though, that MERGE is not atomic, which could cause the following effect (thanks, Marius):

SESS1:

create table t1 (pk int primary key, i int);
create table t11 (pk int primary key, i int);
insert into t1 values(1, 1);
insert into t11 values(2, 21);
insert into t11 values(3, 31);
commit;

SESS2: insert into t1 values(2, 2);

SESS1:

MERGE INTO t1 d
USING t11 s ON (d.pk = s.pk)
WHEN NOT MATCHED THEN INSERT (d.pk, d.i) VALUES (s.pk, s.i);

SESS2: commit;

SESS1: ORA-00001

---

Related Topics