Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2009
    Posts
    25

    Unanswered: Update in oracle using select

    I am trying to execute the update statement below:

    Code:
    UPDATE     TABLE1 T1
    SET          T1.field2 = (
    SELECT     somefield
    FROM        TABLE2.T2
    WHERE      T2.field1 = T1.field1
    AND          T2.field3= 2
    )
    WHERE      T1.field4  = "somevalue"
    AND          T1.field5 = "someothervalue"
    It's updating about 200,000 records but the query is taking incredibly long any ideas on how I can improve this?

  2. #2
    Join Date
    Dec 2003
    Posts
    1,074
    Looks like you might be missing an EXISTS in your WHERE clause. Otherwise, where's there's no match between T1 and T2, T1.field2 will be set to NULL.

    Code:
    UPDATE TABLE1 T1
    SET T1.field2 =
      (SELECT somefield
      FROM TABLE2.T2
      WHERE T2.field1 = T1.field1
      AND T2.field3   = 2
      )
    WHERE T1.field4 = "somevalue"
    AND T1.field5   = "someothervalue"
    AND EXISTS
      (SELECT 1 FROM TABLE2.T2 WHERE T2.field1 = T1.field1 AND T2.field3 = 2
      )
    That may actually slow things down, but inserting a null may not be appropriate. To increase processing speed, make sure that T2.field1 is indexed, and possibly even T1.field1.

    --=cf

  3. #3
    Join Date
    Apr 2009
    Posts
    30
    You should limt the criteria of updation using LIKE,IN,BETWEEN,EXITS,HAVING etc. keywords. It will help oracle to target exact group of values ,for your updation.

  4. #4
    Join Date
    Feb 2009
    Posts
    25
    I did add an exists clause however that did not speed things up... we ended up adding an index to the other table which sped things up.

  5. #5
    Join Date
    Dec 2003
    Posts
    1,074
    Right. If I wasn't clear, the EXISTS was meant to insure an accurate update, since it doesn't matter how fast your SQL is if it's setting values to NULL inappropriately:

    Code:
    SQL> create table table1 (f number);
    
    Table created.
    
    SQL> create table table2 (f number);
    
    Table created.
    
    SQL> insert into table1 values (1);
    SQL> insert into table1 values (2);
    SQL>  insert into table1 values (3);
    
    3 rows created.
    
    SQL> insert into table2 values (3);
    SQL> insert into table2 values (4);
    
    2 rows created.
    
    SQL> select f from table1;
    
             F
    ----------
             1
             2
             3
    
    SQL> commit;
    
    Commit complete.
    
    SQL> update table1
      2  set f = (select f from table2 where table1.f=table2.f);
    
    3 rows updated.
    
    SQL> select f from table1;
    
             F
    ----------
        <null>
        <null>
             3
    
    SQL> rollback;
    
    Rollback complete.
    
    SQL>  update table1
      2  set f = (select f from table2 where table1.f=table2.f)
      3  where exists (select * from table2 where table1.f=table2.f);
    
    1 row updated.
    
    SQL> select f from table1;
    
             F
    ----------
             1
             2
             3
    This is a very common mistake where I work, so I thought I'd mention it as part of my response to your post. You should always be suspect of an UPDATE statement with a SQL statement in the SET clause, when there's no equivalent EXISTS statement in the main WHERE clause. Sometimes it's ok, but for our data, usually it's wrong.

    --=Chuck

  6. #6
    Join Date
    Dec 2003
    Posts
    1,074
    And just to add another comment, "LIKE" ,"IN", and "BETWEEN" do not remove the Optimizer's ability to use an INDEX.

    Probably the only guarantee where you cannot use an index is if the leading edge of your search value is unknown, as in

    Code:
    WHERE last_name LIKE '%SMITH%'
    But any of the following can use an index, you just have to look at the query plan to find out if it's indeed being utilized:

    Code:
    ... WHERE last_name LIKE 'SMITH%';
    ... WHERE create_date BETWEEN '1-JAN-2009' and '3-FEB-2009';
    ... WHERE status IN ('A','C');
    --=Chuck

  7. #7
    Join Date
    Nov 2003
    Posts
    76
    Think about using a hint in you sub selects.
    You could also increase the Degree of parallelism within the hint temporarily just for the update.

  8. #8
    Join Date
    Feb 2009
    Posts
    25
    Thanks people all very good suggestions.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •