SQL & PL/SQL

Hi guys, I hope you can help me on this old issue, I can't figure a way out. It's a performance issue.

This is a simplification of my current scenario, that is slow (12 minutes)

update tb_1
set (col1, col2) = (
select colA, colB
from tb_2
where tb_2.colW = tb_1.colW
and tb_1.colX between tb_2.colY and tb_2.colZ)
where exists (selec count(*) from tb_2 where tb_2.colW = tb_1.colW and tb_1.colX between tb_2.colY and tb_2.colZ)

The thing is that Oracle goes 2 times on tb_2 (as show on Oracle's action plan), so my total time is 12 minutes. If I remove the "exists" query, the total time drops to 6 minutes, for it goes only once on tb_2.

On SQL Server I use the "update from" method, so it takes only 6 minutes (these times are approximate)

The "exists" condition is importante, for that will be rows on tb_1 that have no tb_2, I can't simply remove it.
The primary_key on tb_1 is col1;
on tb_2 it's colA, colY and colZ.

Although Oracle can't be sure of it, only 1 row in tb_2 will return from the subquery (if the criteria is met), and Oracle is using tb_2's index to get the data, but this table has many rows.

Here are my different trials and the results on Oracle. Notice that when I use "nvl" is to compensate the removal of the exists clause.

1)

update tb_1
set (col1, col2) = (
select nvl(colA, tb_1.col1), nvl(colB, tb_1.col2)
from tb_2
where tb_2.colW = tb_1.colW
and tb_1.colX between tb_2.colY and tb_2.colZ)

This time it didn't work, for the "nvl" is inside the subquery, so when there are no rows to return, it's simply useless.

2)

update tb_1
set col1 = nvl((select colA from tb_2 where tb_2.colW = tb_1.colW and tb_1.colX between tb_2.colY and tb_2.colZ), tb_1.col1),
col2 = nvl((select colB from tb_2 where tb_2.colW = tb_1.colW and tb_1.colX between tb_2.colY and tb_2.colZ), tb_1.col2)

This time it worked, but the time was again 12 minutes, the execution plan showed 2 access on tb_2.


3)

update
( select tb_1.col1, tb_1.col2, tb_2.colA, tb_2.colB
from tb_1, tb_2
where tb_2.colW = tb_1.colW
and tb_1.colX between tb_2.colY and tb_2.colZ )
set col1 = colA, col2 = colB

This time I got the "ORA-01779: cannot modify a column which maps to a non key..." error. I tried to solve this using the "bypass_ujvc" hint, on my fourth try (next one). Just for the records: I didn't need the exists because I used a regular join on the tables.

4)

update /*+ bypass_ujvc */
( select tb_1.col1, tb_1.col2, tb_2.colA, tb_2.colB
from tb_1, tb_2
where tb_2.colW = tb_1.colW
and tb_1.colX between tb_2.colY and tb_2.colZ )
set col1 = colA, col2 = colB

This fourth example worked on my development environment, Oracle 8, but when I tried to see it in the customers database, which is Oracle 10, the same "ORA-01779: cannot modify a column which maps to a non key..." showed up.

Do you guys have any suggestion on how to solve this? I'm thinking about the "merge" statement, but I never used it and can't figure a way to get an equivalent syntax.

Thank you all for your help and time used to understand this question.

Daniel