Oracle SQL - Non updatable Views


If you create a view with the WITH READ ONLY option, data manipulation via that view is impossible by definition.

Consider the following code

create or replace view avg_evaluations as
select course
,      avg(evaluation) as avg_eval
from   registrations
group  by course;

The AVG_EVALUATIONS view definition contains a GROUP BY clause.

There would be no one-to-one map between the rows of the view and the rows of the underlying table.

Therefore, data manipulation via the AVG_EVALUATIONS view is impossible.

If you use SELECT DISTINCT in your view definition, it makes your view not updatable.

The set operators UNION, MINUS, and INTERSECT result in non-updatable views.

Try to avoid using SELECT DISTINCT in view definitions, each view access will force a sort whether or not you need it.

Related Topic