Oracle allows us to delete or update table rows based on a query. E.g.:
delete from (select * from mytable);
or
delete from (select t.*, mod(id, 2) as m from mytable t)
where m = 0;
or
delete from (select t.*, dbms_random.value(id - id, 1) as r from mytable t)
where r < 0.5;
(I had to "use" a column value here (id - id), because otherwise Oracle (19c) generates the same random value for all rows.)
But we cannot
delete from (select t.*, row_number() over (order by id desc) as rn from mytable t)
where rn > 3;
This results in ORA-01732: data manipulation operation not legal on this view.
Why? An analytic function merely adds a column to the existing data set, just as MOD and DBMS_RANDOM.VALUE do in the statements above. It doesn't change the number of rows. So what can possibly speak against using this query for an update or delete? It seems there is a flaw in Oracle's detection whether a view is updatable or not when it comes to analytic functions.
I am proposing to remove this restriction and allow analytic functions in updatable/deletable views.