Monday, October 12, 2009

/*+ BYPASS_UJVC */

The UPDATE statement is more complex than the simple select, insert, delete statements and incomplete or misleading business requirements can make it even more complex. I recently tried to use the hint /*+ BYPASS_UJVC */ to bypass some of the limitations that Oracle imposes on the updates that use a join, such as the fact that the join be key preserved:






Key preserved means that rows from the base tables appear at most once in the output of the view (join) on that table. (Tom Kyte)




In the following example we’ll first take a look at the apparent benefits of the hint and at the danger it represents:




--Create a table
DROP TABLE t2 PURGE;
Table dropped.




CREATE TABLE t2(c1 INT, c2 VARCHAR2(1));
Table created.




INSERT INTO t2 VALUES (1,'A');
1 row created.


INSERT INTO t2 VALUES (2,'B');
1 row created.


INSERT INTO t2 VALUES (3,'C');
1 row created.


COMMIT;
Commit complete.




--Attempt to update it using its own data, strange but similar to the situation that I faced on the current project..
UPDATE
(SELECT ta.c2 ta_c2, tb.c2 tb_c2
FROM t2 ta, t2 tb
WHERE ta.c1=tb.c1) t
SET ta_c2=tb_c2;
UPDATE
(SELECT ta.c2 ta_c2, tb.c2 tb_c2
FROM t2 ta, t2 tb
WHERE ta.c1=tb.c1) t
SET ta_c2=tb_c2
Error at line 9
ORA-01779: cannot modify a column which maps to a non key-preserved table




--If we use the wonder hint the update goes through
UPDATE /*+ BYPASS_UJVC */
(SELECT ta.c2 ta_c2, tb.c2 tb_c2
FROM t1 ta, t2 tb
WHERE ta.c1=tb.c1) t
SET ta_c2=tb_c2;
1 row updated.


COMMIT;
Commit complete.




SELECT * FROM t2;
C1 C2
---------- --
1 A
2 B
3 C


3 rows selected.


--Same can be accomplished using a merge statement that uses the rowid to identify which row to be updated
MERGE INTO t2
USING (SELECT ta.rowid rid, ta.c2 ta_c2, tb.c2 tb_c2
FROM t2 ta, t2 tb
WHERE ta.c1=tb.c1) x
ON (t2.rowid=x.rid)
WHEN MATCHED THEN UPDATE SET c2=tb_c2
WHEN NOT MATCHED THEN INSERT (c2) VALUES (NULL);
Merge successfully completed.


COMMIT;
Commit complete.




SELECT * FROM t2;
C1 C2
---------- --
1 A
2 B
3 C


3 rows selected.


DROP TABLE t2 PURGE;
Table dropped.


CREATE TABLE t2(c1 INT, c2 VARCHAR2(1));
Table created.


INSERT INTO t2 VALUES (1,'A');
1 row created.


INSERT INTO t2 VALUES (2,'B');
1 row created.


INSERT INTO t2 VALUES (3,'C');
1 row created.


COMMIT;
Commit complete.


--The correct solution would be to create the primary key though
ALTER TABLE t2 ADD CONSTRAINT pk_t2 PRIMARY KEY (c1);
Table altered.


—And the original update would execute without any issues
UPDATE
(SELECT ta.c2 ta_c2, tb.c2 tb_c2
FROM t2 ta, t2 tb
WHERE ta.c1=tb.c1) t
SET ta_c2=tb_c2;
3 rows updated.


COMMIT;
Commit complete.






SELECT * FROM t2;
C1 C2
---------- --
1 A
2 B
3 C


3 rows selected.






--So before attempting SQL gymnastics check the referential integrity and if there are any primary, foreign or unique keys missing create them. Lets start all over to demonstrate the dangers of the hint. Create 2 tables:


DROP TABLE t2 PURGE;
Table dropped.


DROP TABLE t1 PURGE;
Table dropped.


CREATE TABLE t1 (c1 INT, c2 VARCHAR2(1));
Table created.


INSERT INTO t1 VALUES (1,NULL);
1 row created.


CREATE TABLE t2(c1 INT, c2 VARCHAR2(1));
Table created.


INSERT INTO t2 VALUES (1,'A');
1 row created.


INSERT INTO t2 VALUES (1,'B');
1 row created.


INSERT INTO t2 VALUES (1,'C');
1 row created.


COMMIT;
Commit complete.


--Normal update fails as expected
UPDATE
(SELECT t1.c2 t1_c2, t2.c2 t2_c2
FROM t1, t2
WHERE t1.c1=t2.c1) t
SET t1_c2=t2_c2;
UPDATE
(SELECT t1.c2 t1_c2, t2.c2 t2_c2
FROM t1, t2
WHERE t1.c1=t2.c1) t
SET t1_c2=t2_c2
Error at line 14
ORA-01779: cannot modify a column which maps to a non key-preserved table




--Hinted update succeeds but can it be trusted?
UPDATE /*+ BYPASS_UJVC */
(SELECT t1.c2 t1_c2, t2.c2 t2_c2
FROM t1, t2
WHERE t1.c1=t2.c1) t
SET t1_c2=t2_c2;
3 rows updated.


COMMIT;
Commit complete.




SELECT * FROM t1;
C1 C2
---------- --
1 C
1 row selected.




--Lets start all over with the same data but in different order
DROP TABLE t2 PURGE;
Table dropped.


CREATE TABLE t2(c1 INT, c2 VARCHAR2(1));
Table created.


INSERT INTO t2 VALUES (1,'C');
1 row created.


INSERT INTO t2 VALUES (1,'A');
1 row created.


INSERT INTO t2 VALUES (1,'B');
1 row created.


COMMIT;
Commit complete.


--We run the update again
UPDATE /*+ BYPASS_UJVC */
(SELECT t1.c2 t1_c2, t2.c2 t2_c2
FROM t1, t2
WHERE t1.c1=t2.c1) t
SET t1_c2=t2_c2;
3 rows updated.


COMMIT;
Commit complete.


SELECT * FROM t1;


C1 C2
---------- --
1 B
1 row selected.


Note how the final results changed due only to a change in the order of the inserts in table t2. This shows that using the hint /*+ BYPASS_UJVC */ allows us to produce non-deterministic results, given the same inputs we might arrive at different results on different executions. This hint should not be used on production systems.


Run the test case and see for yourself. If you have any questions please don't hesitate to ask them on the blog or by email.

3 comments:

  1. Don't be so extreme on conclusions. Yes, it "allows us to produce non-deterministic results". But there is difference of using and abusing.
    This hint is very helpful when there is group function (max(), sum(), etc) with "GROUP BY" on "linked" value inside the view.

    ReplyDelete
  2. I've used this Hint and it's useful under certain conditions. I recommend this link: http://www.dataprix.com/es/update-join-oracle. It explains the limitations on the use of this hint.

    ReplyDelete