Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table t
as
select level as id,
  dbms_random.string('x', 10)
   as val,
  dbms_random.random() as q,
  decode(mod(level, 20), 0, 'O', 'U')
    as status
from dual
connect by level < 1000
999 rows affected

/*We will use this expression
for filtering and sorting*/
create index ix_sfu_t
on t(decode(status, 'O', id))
begin
dbms_stats.gather_table_stats(
  sys_context('USERENV', 'CURRENT_SCHEMA'),
  'T',
  cascade => true
);
end;
/
1 rows affected

select /*+gather_plan_statistics*/ *
from t
where
  decode(status, 'O', id) is not null
  and rownum < 5
order by decode(status, 'O', id) asc
for update
skip locked


ID
VAL
Q
STATUS


20
8XS78B92M2
1263442844
O

40
XLLUN9DLV4
-435709224
O

60
2DZ4EUH11J
-1328077826
O

80
PJKQMOBISR
-644473876
O


select *
from table(
  dbms_xplan.display_cursor(
  format => 'BASIC +PREDICATE +ALLSTATS LAST'
  ))


PLAN_TABLE_OUTPUT


EXPLAINED SQL STATEMENT:

------------------------

select /*+gather_plan_statistics*/ * from t where   decode(status, 'O',

id) is not null   and rownum < 5 order by decode(status, 'O', id) asc

for update skip locked

 

Plan hash value: 2984481354

 

----------------------------------------------------------------------------------------------------

| Id  | Operation                     | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |

----------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT              |          |      1 |        |      4 |00:00:00.01 |       8 |

|   1 |  FOR UPDATE                   |          |      1 |        |      4 |00:00:00.01 |       8 |

|*  2 |   COUNT STOPKEY               |          |      1 |        |      4 |00:00:00.01 |       2 |

|   3 |    TABLE ACCESS BY INDEX ROWID| T        |      1 |      4 |      4 |00:00:00.01 |       2 |

|*  4 |     INDEX FULL SCAN           | IX_SFU_T |      1 |        |      4 |00:00:00.01 |       1 |

----------------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - filter(ROWNUM<5)

   4 - filter("T"."SYS_NC00005$" IS NOT NULL)

 


/*To show which rows were locked*/
create table t_log(
  s_id int,
  id int
)
create procedure l(s int)
as
  pragma autonomous_transaction;
  type t_tab is table of t%rowtype
    index by pls_integer;
  t_data t_tab;
  i pls_integer;
  
  /*Lock will be applied
  to result set only*/
  cursor c is
  select *
  from t
  where
    decode(status, 'O', id) is not null
  order by decode(status, 'O', id) asc
  for update
  skip locked;

begin
  
  open c;
  fetch c
    bulk collect into t_data
    /*Will fetch 5 not locked rows*/
    limit 5
  ;
  
  /*Step into recursion
  to demonstrate concurrent locking*/
  if s > 0 then
    l(s-1);
  end if;
  
  forall i in 1..t_data.count
    insert into t_log(s_id, id)
      values(s, t_data(i).id);
  commit;
  close c;
end;
/
begin
  l(3);
end;
/
1 rows affected

/*Observe results*/
select *
from t_log;


S_ID
ID


0
320

0
340

0
360

0
380

0
400

1
220

1
240

1
260

1
280

1
300

2
120

2
140

2
160

2
180

2
200

3
20

3
40

3
60

3
80

3
100

ID	VAL	Q	STATUS
20	8XS78B92M2	1263442844	O
40	XLLUN9DLV4	-435709224	O
60	2DZ4EUH11J	-1328077826	O
80	PJKQMOBISR	-644473876	O

S_ID	ID
0	320
0	340
0	360
0	380
0	400
1	220
1	240
1	260
1	280
1	300
2	120
2	140
2	160
2	180
2	200
3	20
3	40
3	60
3	80
3	100

S_ID	ID
0	320
0	340
0	360
0	380
0	400
1	220
1	240
1	260
1	280
1	300
2	120
2	140
2	160
2	180
2	200
3	20
3	40
3	60
3	80
3	100

S_ID	ID
0	320
0	340
0	360
0	380
0	400
1	220
1	240
1	260
1	280
1	300
2	120
2	140
2	160
2	180
2	200
3	20
3	40
3	60
3	80
3	100