1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

improve the performance of update by select

Discussion in 'SQL PL/SQL' started by jingc3, Oct 19, 2009.

  1. jingc3

    jingc3 Guest

    Hi There

    I have a query like this:

    update table A set cola = (select colb from B where B.id = A.id)

    The query runs very slow when both A and B are fairly big table. Both A and B have indexes.

    I am wondering if there is any other way to construct the sql so that it can be executed faster.

    Thanks
     
  2. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    if two tables have only col in common, better try using natural join in subselect clause. i think works better
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    That defeats the purpose of the correlated subquery. Depending upon the version of Oracle in use using the MERGE INTO statement might be faster:

    Code (SQL):
    MERGE INTO A USING B ON (b.id = a.id)
    WHEN matched THEN
    UPDATE
    SET a.cola = b.colb;
     
  4. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    in merge stmt "when not matched" clause is not used. is this clause optional?
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO

    It does not need to be used; the statement can be written to use WHEN MATCHED:

    Code (SQL):
    MERGE INTO A USING B ON (b.id = a.id)
    WHEN matched THEN
    UPDATE
         SET a.cola = b.colb;
    WHEN NOT MATCHED:

    Code (SQL):
    CREATE TABLE x(
        c1 NUMBER,
        c2 NUMBER,
        c3 NUMBER,
        c4 varchar2(5)
    );

    MERGE INTO x USING (SELECT 'A' c4 FROM DUAL) d ON (x.c4 = d.c4)
    WHEN NOT MATCHED THEN
      INSERT VALUES (0,0,0,'A');
    or both:

    Code (SQL):
    --
    -- Create BONUSES table
    --
    CREATE TABLE bonuses (employee_id NUMBER, bonus NUMBER DEFAULT 100);

    --
    -- Populate the table with employees
    -- who made sales
    --
    INSERT INTO bonuses(employee_id)
       (SELECT e.employee_id FROM employees e, orders o
       WHERE e.employee_id = o.sales_rep_id
       GROUP BY e.employee_id);

    --
    -- Display current contents
    --
    SELECT * FROM bonuses;

    --
    -- Give bonuses to every employee earning
    -- 8000 or less
    --
    -- If the employee has also made sales increase
    -- their current bonus by 1%
    --
    -- If not, provide a 1% bonus anyway
    --
    MERGE INTO bonuses D
       USING (SELECT employee_id, salary, department_id FROM employees
       WHERE department_id = 80) S
       ON (D.employee_id = S.employee_id)
       WHEN MATCHED THEN UPDATE SET D.bonus = D.bonus + S.salary*.01
         DELETE WHERE (S.salary > 8000)
       WHEN NOT MATCHED THEN INSERT (D.employee_id, D.bonus)
         VALUES (S.employee_id, S.salary*0.1)
         WHERE (S.salary <= 8000);

    --
    -- Display results
    --
    SELECT * FROM bonuses;
     
     
    kiran.marla likes this.
  6. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    thanq. so when not matched clause in optional. this confusion creates when we do not understand the features of any keyword or clauses. right?
     
  7. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Yes.. That clause is optional.

    You can find more details on Merge here