SQL & PL/SQL

Hello,

i have serious problems with this.

-- drop table testtable;
create table testTable (id number(10,0) primary key, usage number(10,10));

-- delete from testtable;
insert into testtable values (11, 0.5);
insert into testtable values (10, 0.3);
insert into testtable values (12, 0.3);
insert into testtable values (9, 0.3);
insert into testtable values (8, 0.9);
insert into testtable values (3, 0.0);
insert into testtable values (2, 0.02);
insert into testtable values (1, 0.05);
insert into testtable values (7, 0.7);
insert into testtable values (6, 0.4);
insert into testtable values (5, 0.2);
insert into testtable values (4, 0.1);

select * from testtable;

-- without FOR UPDATE
select * from (
select tt.id id_, tt.*
from testtable tt
where tt.usage > 0.1
order by tt.usage desc, tt.id desc
)
where rownum <= 10;

--> WORKS

-- without ORDER BY
select * from (
select tt.id id_, tt.*
from testtable tt
where tt.usage > 0.1
)
where rownum <= 10
for update of id_;

--> WORKS

-- without WHERE ROWNUM <= 10
select * from (
select tt.id id_, tt.*
from testtable tt
where tt.usage > 0.1
order by tt.usage desc, tt.id desc
)
for update of id_;

--> WORKS


-- But what i need is this:
select * from (
select tt.id id_, tt.*
from testtable tt
where tt.usage > 0.1
order by tt.usage desc, tt.id desc
)
where rownum <= 10
for update;

--> ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc., SQL State: 42000, Error Code: 2014

select * from (
select tt.id id_, tt.*
from testtable tt
where tt.usage > 0.1
order by tt.usage desc, tt.id desc
)
where rownum <= 10
for update of id_;

--> ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc., SQL State: 42000, Error Code: 2014

I have tried every single solution i could come up with.
But nothing worked.

My latest idea is to include a comment in the query and set up an ON SELECT trigger which evaluates the comment and enforeces the lock.
But i'm not sure if this is even possible.

I cannot split the statement into two because i need the lock immediately when the wanted rows are selected.

One major criteria for the rows is the order by. Without it i get a random set of rows.

And the rownum <= 10 is also needed because i don't want to lock the whole table but only the few needed rows.

I tried row_number() over (order by ...) but this is considdered a window/group-function which disallows the for update as well as the order by.
During these tests i noticed, that when using the row_number()-function the resultset is ordered automatically (without an additional order by clause).
But this doesn't help anyway.

I tried using piped functions to wrap the select to apply the rownum manually by cursor skip, but this doesn't work either. First of all i wasn't able to wrap the query the way i imagined and second the lock would be applied to the whole resultset anyway but only the reduced rows would be returned.

I heared about LOCK-hints from other DBs, is there anything similar here?

Any other solution??

btw. it has to be high-performance after all.

Greetings Finomosec;