There are a few posts on the blog describing problems with updatable join views or, to be more precise, join views which were key-preserved but which the optimizer did not recognize as key-preserved. Some scenarios are addressed in 12c:
- Constant involved in key-preservation – fixed
- Key-preservation ensured by aggregation – fixed
- Analytic view update – still not legal / handled (and still illegal in 19.3)
Update (May 2021)
An exchange on the Oracle-l list server has highighted a significant change documented in Oracle 21c – the compile-time restriction on updatable join views has been removed, to be replaced by a run-time error message (ORA-30936: unable to get a stable set of rows in the source tables) if the actual data set causes problems due to the lack of a uniqueness constraint. This means that updatable join views now behave the same way as the update portion of the merge command and, as pointed out by Andrew Sayer, this change can be enabled by setting a fix-control in 19.11
This doesn’t fixed the initial problem in the Analytic View update article above, but does allow the final example in that article to operate – updating the base table from an analytic view of the same table.
Oracle Scratchpad 
[…] There are a couple of variations to the uniqueness strategy that fail in some versions of Oracle, and I’ve got a short list of tests of recent examples that used to fail in earlier versions of Oracle at this URL. […]
Pingback by Updatable Join Views | Oracle Scratchpad — November 1, 2019 @ 12:24 pm GMT Nov 1,2019 |