1 2 Previous Next 23 Replies Latest reply: Nov 21, 2012 12:30 PM by 942572 Go to original post RSS
      • 15. Re: Fastest way to  check if a record exists
        Solomon Yakobson
        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
          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
            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
              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
                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
                  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
                    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!!!!
                      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
                        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