How safe is the BYPASS_UJVC hint?

Rather than use a standard correalted update like so:

  update a
    set column1 = ( select column2

                    from b
                    where 
                       b.column3 = a.column 4 and
                       etc. );

I am updating using an inline view like so:

   update ( select column1, column2

             from a, b
             where 
               b.column3 = a.column4 and
               etc. ) 
     set  column 1 = column2;

However, a and b are not key-preserved and thus Oracle throws an exception saying this isn't allowed (ORA-01779) . With a BYPASS_UJVC hint however, Oracle allows this statement (as Jonathan Lewis has pointed out, BYPASS_UJVC seems to stand for 'bypass the strict constraints on updatable join views').

I have done some testing with this hint and Oracle seems to behave itself (no ORA-600 for example) and is somewhat deterministic in what it will do if table B returns more than one row into the join with A. To make sure the update does what I expect/want, I make sure that the primary key of B is satisfied in the join conditions of the inline view.

Does anyone have any experience which would indicate that using BYPASS_UJVC is a bad idea? The main advantage of the updatable inline view is one of performance - presumably this is why Oracle's materialized view process uses them as well as the BYPASS_UJVC hint. Thoughts?

Jeff Richmond
Deutsche Bank
DBA Received on Mon Sep 10 2001 - 12:13:22 CDT