Oracle The old "update from" or bypass_ujvc question

2012-03-17  Source: Original Site  Category:Oracle  Views:1 

Advertisement

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

Related articles
  • Oracle The old "update from" or bypass_ujvc question 2012-03-17

    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.col

  • Oracle Archiving old data from a huge partitioned table 2014-06-22

    I am planning to archive the old data from a huge fact partitioned table by exporting the old partitions from the table, storing them as dump files and then importing them on a need basis. Below is the method I have decided upon. Kindly verify is my

  • Oracle Archiving old data from a partitioned table 2012-06-24

    While sifting through all the options for archiving the old data from a table which is also indexed, i came across a few methods which could be used: 1. Use a CTAS to create new tables on a different tablespace from the partitions on the exisitng tab

  • Ubuntu did the update that was previous to the last update (from now) require restart? 2015-05-19

    did the update that was previous to the last update (from now) require restart? can update packages be replaced by replacing update server to hack my system? can i check from a web-site the last updates? what packages they was?

  • Oracle The old SQL Loader is still here!!? 2013-01-17

    There must be some other way to load data not only from Oracle, but any other databases? The old SQL Loader (even with GUI) is hardly a competitive and handy tool! Where are the Transparent Gateways? What I'm missing? Should I continuously use SQL Se

  • Oracle can we update from 10.2.0.1.0 to 10.2.0.2 or later? 2013-11-15

    Hi Thank you for reading my psot I have Oracle 10.2.0.1.0 on my computer installed and for some reason i should update it to 10.2.0.2 or later. can it be done with some patches or i must download the whole ORACLE 10.2.0.2 Database? Can you please tel

  • Oracle error while updating from front end 2014-04-29

    I'am using oracle forms 6i to connect to a table and i'am trying to update from the frontend, but i get an error saying the following, 'Could not reserve record( 2 tries )...' what is the reason, is it because the record is locked??? and if so is the

  • Oracle Getting automatic updates from database 2014-07-14

    Not sure if this is the correct place to post this as it is databse and Java related, but here goes, please let me know if there is a better place for questions like this. I currently have a servlet based server using http comm's to a flash movie cli

  • Oracle Returning OLD value from UDPATE 2014-12-19

    Can you get/return the old value for a column from an update statement within plsql? For Example: The following will return the new value for a, which I already know. I would like to get the old value for a without having to pre-select the row. decla

  • How to uninstall the security update from DOS 2013-07-08

    I want to uninstalled the Security update that I downloaded from the net using Dos environment? I can't get tru safe mode.

  • Why not import the old posts from jQuery google group? 2014-01-26

    In fact I met different problem of jQuery google group: our users are mostly from China mainland and they cannot visit google group web UI from last year. I have to migrate our forum from google group to a server in China. Then I developed a python t

  • Oracle Removing old backups from earlier incarnation 2015-02-27

    After a reorg of a database and subsequently getting a new DBID in the repository, I now have a few old backups that I seem unable to reach through RMAN. I can see them on the directory, but since they belong to another DBID, I can't list them. Am I

  • Oracle table update from T-SQL question 2014-04-24

    Ok, I have a T-SQL query that I'm trying to convert in Oracle SQL. Here is the T-SQL: Update E Set field1 = 'some text', field2 = NULL from table1 E Left join table2 A On e.fieldx = a.fieldx and e.fieldy = a.fieldy I think I'm close, but I'm not sure

  • Oracle Issues With Updates from the GUI 2015-02-09

    I cannot find a way to fix the update issues from the gui. Every time I try to get a list of available updates, I do not get the downloads. The list of selected updates is quickly shown as if the download happened but (obviously being some of that qu

  • Oracle Multiple table updates from the same page 2013-02-24

    I've got a form to update a table and I have to break up that table into several tables in order to simulate a relational database. I've defined and built all the tables, but now I'm having trouble figuring out how to explain to my Update Page which

  • Oracle install http sever from companion CD- question closed : ) 2012-04-27

    I'm going to install http server from companion CD. In the first step: Specify File Locations Enter or select a name for installation and the full path where you want to install the product. By default, in my pc, Oracle use: Name: OraDb10g_home1 Path

  • Oracle Batch Insert/update from .Net 2013-03-19

    I have a requirement where multiple records have to be inserted/ updated into a table, from .Net application. The records have to be passed to Oracle stored procedure, which will insert data into the tables. I am using Oracle 10g. One option I have i

  • Oracle Difference in Update from cursor and UPDATE-SELECT 2012-04-21

    Thanks, I am having problem in the following , Are similar this? CURSOR C_G IS SELECT * FROM TMP_LGORDER BY N_STAMPA; BEGINFOR CUR_GIO IN C_G LOOPUPDATE MG SET N_STAMPA = CUR_GIO.N_STAMPAWHERE N_PROGRESS = CUR_GIO.N_PROGRESS;END LOOP; END; And BEGIN

  • Oracle The old VNI-2015 chestnut - all Linux this time 2012-06-14

    I've seen lots of posts about this, but none of the answers seem to fix my problem. I am running 8i on a Linux server. I also run Linux on my desk. I have an X window from my machine to the Oracle server. I'm logged in as oracle. I fire up oemapp con

  • Oracle javascript checkbox updated from an application process 2012-07-10

    I have a button on a page that calls a javascript function. That function calls an on demand application process to return a value and update the checkbox. My code works fine when it's updating a text item. But when I'm trying to update a checkbox or