Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Use Merge instead of bypass_ujvc hint from a table partition

736189Jan 24 2011 — edited Feb 1 2011
We recently verified that the bypass_ujvc hint does not work in our 10g environment
Tom confirms it in http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:760068400346785797#tom2316038000346372366

I do have 2 statements like below. How can I convert it into MERGE? They are run time executed UPDATE statement.

1.
EXECUTE IMMEDIATE
'UPDATE /*+ bypass_ujvc */ ( ' ||
'SELECT tb.' || a || ' AS a1, ' ||
'x.b AS b1 ' ||
'FROM ' || t1 || ' tb, ' ||
t2 || ' x ' ||
'WHERE tb.' || c1 || ' = x.c2) t ' ||
'SET a1 = b1';

2.
EXECUTE IMMEDIATE
'UPDATE /*+ bypass_ujvc */ ( ' ||
'SELECT tb.' || a || ' AS a1, ' ||
'x.b AS b1 ' ||
'FROM ' || t1 || ' PARTITION(' || partition_name1 || ') tb, ' ||
t2 || ' PARTITION(' || partition_name2 || ') x ' ||
'WHERE tb.' || c1 || ' = x.c2) t ' ||
'SET a1 = b1';

Thanks in advance,
Sharmin
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 1 2011
Added on Jan 24 2011
4 comments
862 views