3 Replies Latest reply: Dec 20, 2012 2:04 AM by kikolus RSS

    Reduce time consumption of a bulk insert statement

    715689
      Hi All,

      I am trying to insert some large amount of data records into a Global Temporary Table (GTT) using a query like below. Below SQL statement is generated as a string and executed as a dynamic sql statement.



      INSERT INTO
      my_table ( col1, col2, col3, col4)
      SELECT c1, c2, c3, c4
      FROM tab1 t1,
      tab2 t2,
      tab3 t3
      WHERE <<dynamically generated where clause>>
      UNION ALL
      SELECT c1, c2, c3, c4
      FROM tab4 t4,
      tab5 t5,
      tab6 t6
      WHERE <<dynamically generated where clause>>
      UNION ALL
      SELECT c1, c2, c3, c4
      FROM tab4 t4,
      tab5 t5,
      tab6 t6
      WHERE <<dynamically generated where clause>>
      UNION ALL
      SELECT c1, c2, c3, c4
      FROM tab4 t4,
      tab5 t5,
      tab6 t6
      WHERE <<dynamically generated where clause>>



      The problem is, it takes some considerable amount of time(25-30 seconds) to write the resulted data set into GTT(my_table ). I have checked the SELECT statement above (without the INSERT), and it gives the result set in few seconds. Therefore i assume it's the INSERT that takes more time. (SELECT statement returns around 75000+ records). The GTT consists of 8 columns and 6 out of those are marked as a primary key.

      Are there any other mechanisms that I can use to efficiently insert large amount of data in to the table? Really appreciate all of your comments and suggestions.

      Best Regards,
      Nipuna
        • 1. Re: Reduce time consumption of a bulk insert statement
          tony.g
          Hi

          This query would probably be better situated in the Oracle database SQL & PL/SQL section, as it's nothing to so with Oracle Forms.

          Tony.
          • 2. Re: Reduce time consumption of a bulk insert statement
            Christian Erlinger
            Bulk processing will slow the process down instead of making it faster.

            http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:73891904732164

            Bulk processing will only minimize the overhead of context switching between the SQL and PL/SQL engine.

            cheers
            • 3. Re: Reduce time consumption of a bulk insert statement
              kikolus
              Hi,
              I'm not sure about your query speed :) Don't have it against me, but sometimes users just run SELECT statement and waits unitl first rows are returned (e.g. using TOAD which fetches 500 first rows as default) and they are convinced that this is response time for whole set. You have to navigate to last record to see real response time. But probably this is not the casse here. When you insert a large set of data consider to move such statement from forms to database (you avoid data roundtrip between forms and DB). If your insert still takes to much time then try to trace session you run your statement and(if possible) try to monitor your database resource ussge. If you're not able to use dbms_trace then you can just lokk at the system v$Session views. Following two views can be helpful to investigate most time consuming waits and can help understand the reason of slowness.
              select * from v$session_wait
              
              select * from v$session_wait_history