Oracle Scratchpad

August 9, 2013

12c Join Views

Filed under: 12c,Oracle,Performance,Tuning — Jonathan Lewis @ 6:36 pm BST Aug 9,2013

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 the updatable join view now behaves 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.

 

 

1 Comment »

  1. […] 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 | Reply


RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by WordPress.com.