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)
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 |