This discussion is archived
1 2 Previous Next 23 Replies Latest reply: Nov 21, 2012 10:30 AM by 942572 Go to original post RSS
  • 15. Re: Fastest way to  check if a record exists
    Solomon Yakobson Guru
    Currently Being Moderated
    Well,
    copy_data_from_baseTable_to_historyTable
    can be done with one insert statement. And
    update_data_to_baseTable
    Else
    insert_data_to_baseTable
    can be done with one merge statement:
    merge
      into baseTable a
      using driverTable b
      on (
          b.id = a.id
         )
      when matched
        then update
                set ...
      when not matched
        then insert
            values(...)
    /
    That's all you need.

    SY.
  • 16. Re: Fastest way to  check if a record exists
    942572 Newbie
    Currently Being Moderated
    Do you mean I can use two merge statements? Since we only need insert the row to histTable when there is any row in baseTable. Then I don't need check the count(*) from baseTable where a.id=myID. The myID is passed in as the parameter. Do you think it will be faster?

    merge into baseTable a
    on ( a.id = myID)
    when matched
    then insert into histTable b
    values(a.id, a......);

    merge into baseTable a
    on (a.id = myID )
    when matched
    then update
    set ...
    when not matched
    then insert
    values(...)
  • 17. Re: Fastest way to  check if a record exists
    Solomon Yakobson Guru
    Currently Being Moderated
    Why would you need second merge?
    insert
      into historyTable
      select  *
        from  baseTable a,
              driverTable b
        where b.id = a.id
    /
    merge
      into baseTable a
      using driverTable b
      on (
          b.id = a.id
         )
      when matched
        then update
                set ...
      when not matched
        then insert
            values(...)
    /
    SY.
  • 18. Re: Fastest way to  check if a record exists
    942572 Newbie
    Currently Being Moderated
    You are right, the first insert works well. Since I have the myID passed in, there is no driving table for the merge. What shall I use for the using clause? For example, I will have the following insert:

    insert
    into historyTable
    select *
    from baseTable a where a.id = myID

    Thanks a lot!
  • 19. Re: Fastest way to  check if a record exists
    Solomon Yakobson Guru
    Currently Being Moderated
    I thought you have 10K of IDs. So you insert them all into some driver table. Now if you join base table and driver table on ID you will get a list of base table rows that you need to update. But before you update them you want to preserve them in history table. So you issue insert I posted. Now all matching rows are saved in history table and we can safely issue merge. Merge will update matching rows and insert new rows. Typical DW task.

    SY.
  • 20. Re: Fastest way to  check if a record exists
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    939569 wrote:
    You are right, the first insert works well. Since I have the myID passed in, there is no driving table for the merge. What shall I use for the using clause?
    You can use dual:
    merge
      into baseTable a
      using (
               SELECT  myID     AS id
             FROM    dual
           ) b
      on (
          b.id = a.id
         )
      when matched
        then update
                set ...
      when not matched
        then insert
            values(...)
    / 
  • 21. Re: Fastest way to  check if a record exists
    Solomon Yakobson Guru
    Currently Being Moderated
    Yes, you could use dual in merge using clause, but 10K one row INSERTs + 10k one row MERGE statements will be much less efficient than 1 INSERT of 10K rows + 1 MERGE of 10K rows.

    SY.
  • 22. Re: Fastest way to  check if a record exists
    Most Wanted!!!! Journeyer
    Currently Being Moderated
    may be like this

    --QUERY 1
    EXPLAIN PLAN FOR
    SELECT COUNT(*)
      FROM emp1
     WHERE sal < 2000
     AND empno BETWEEN 7369 AND 7934
     AND ROWNUM<6;
    SELECT *
      FROM TABLE (DBMS_XPLAN.display ());
    
    
    *PLAN_TABLE_OUTPUT*
    
     
    ------------------------------------------------------------------------------
    | Id  | Operation                     | Name    | Rows  | Bytes | Cost (%CPU)|
    ------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT              |         |     1 |     8 |     3   (0)|
    |   1 |  SORT AGGREGATE               |         |     1 |     8 |            |
    |   2 |   COUNT STOPKEY               |         |       |       |            |
    |   3 |    TABLE ACCESS BY INDEX ROWID| EMP1    |     7 |    56 |     3   (0)|
    |   4 |     INDEX RANGE SCAN          | EMP1_PK |    13 |       |     1   (0)|
    ------------------------------------------------------------------------------
     
     
    --QUERY 2
     
    EXPLAIN PLAN FOR
    SELECT SUM (a)
      FROM (SELECT 1 a
              FROM emp1
             WHERE sal < 2000 AND empno BETWEEN 7369 AND 7934 AND ROWNUM < 6);
    SELECT *
      FROM TABLE (DBMS_XPLAN.display ());
      
    *PLAN_TABLE_OUTPUT*
    
     
    -------------------------------------------------------------------------------
    | Id  | Operation                      | Name    | Rows  | Bytes | Cost (%CPU)|
    -------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT               |         |     1 |     3 |     3   (0)|
    |   1 |  SORT AGGREGATE                |         |     1 |     3 |            |
    |   2 |   VIEW                         |         |     5 |    15 |     3   (0)|
    |   3 |    COUNT STOPKEY               |         |       |       |            |
    |   4 |     TABLE ACCESS BY INDEX ROWID| EMP1    |     6 |    48 |     3   (0)|
    |   5 |      INDEX RANGE SCAN          | EMP1_PK |    13 |       |     1   (0)|
    -------------------------------------------------------------------------------
    regards ,
    friend
  • 23. Re: Fastest way to  check if a record exists
    942572 Newbie
    Currently Being Moderated
    Problem solved! I used the GTT and join instead of the loop, which cuts the execution time to less than one tenth of the original one. Thanks for everyone's help!
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points