1 2 Previous Next 15 Replies Latest reply: Jul 2, 2013 11:30 PM by Raunaq RSS

    Forall insert Vs Direct insert

    user4295847

      Hi ,

      Find the below two codes which are doing same logic...Consider product table has 10,000 rows..
      Please advice me which one give the better performance and Why ?


      Code 1
      -----------------
      DECLARE
      TYPE prod_tab IS TABLE OF products%ROWTYPE;
      products_tab   prod_tab := prod_tab();
      BEGIN

      SELECT * BULK COLLECT INTO products_tab FROM products;

      FORALL i in products_tab.first .. products_tab.last
      INSERT INTO products_history VALUES products_tab(i);
      COMMIT;
      END;

       

      Code2:
      -------------

      INSERT /*+ APPEND*/ INTO products_history (product_id,product_name)
      select product_id,product_name from products;
      COMMIT;

        • 1. Re: Forall insert Vs Direct insert
          Paul  Horth

          All things being equal: code 2 because pure SQL is faster than PL/SQL loops.

           

          Having said that, you might not notice the difference with only 10000 rows.

          • 2. Re: Forall insert Vs Direct insert
            Hemant K Chitale

            A Direct Path INSERT is always preferable over a BULK COLLECT Insert.

             

            Hemant K Chitale

            • 3. Re: Forall insert Vs Direct insert
              _Karthick_

              Code 1 does this

               

              1. Select rows from table (products)

              2. Load the rows into a collection  (products_tab) in expensive PGA

              3. Picks the records from the collection (products_tab)

              4. Inserts the data from collection (products_tab) into the table (products_history)

               

              Code 2 does this

               

              1. Select rows from table (products)

              2. Do a direct path insert into the table (products_history)

               

              Now you tell me which one will be faster?


              • 4. Re: Forall insert Vs Direct insert
                user4295847


                Thanks for your reply..

                Consider I am having 100 millions records and code2 is something like below...(added begin and end block)

                 

                Code2:
                -------------
                BEGIN
                INSERT /*+ APPEND*/ INTO products_history (product_id,product_name)
                select product_id,product_name from products;
                COMMIT;
                END

                now pls advice me which one give the better performance and Why ?

                • 5. Re: Forall insert Vs Direct insert
                  Ashu_Neo

                  Hi,

                  As above scenarios, code 2 (using direct path INSERT) will be faster then code 1.

                  As in direct path, the database appends the inserted data after existing data in the table. Data is written directly into datafiles, bypassing the buffer cache. But Free space of table will not be re-used.

                  But code 1 will follow conventional INSERT along with context-switching between SQL/PLSQL engines for execution and will take more time in comparision. But Free space of table will be re-used.

                   

                  Refer documentation for more:- http://docs.oracle.com/cd/E11882_01/server.112/e25494/tables004.htm

                  • 6. Re: Forall insert Vs Direct insert
                    Paul  Horth

                    Sticking a begin/end block round it will make no difference. It will still be faster.

                    • 7. Re: Forall insert Vs Direct insert
                      user4295847

                      Thanks for your reply..

                      In my project ...they have used  BULK COLLECT INSERT  many places..
                      pls advice me which scenario we can use the BULK COLLECT instead of using direct path insert ..

                      provide some exmaples...

                      • 8. Re: Forall insert Vs Direct insert
                        user4295847

                        Thanks for your reply..

                        In my project ...they have used  BULK COLLECT INSERT  many places..
                        pls advice me which scenario we can use the BULK COLLECT instead of using direct path insert ..

                        provide some exmaples...

                        • 9. Re: Forall insert Vs Direct insert
                          BluShadow

                          The only place I'd use BULK COLLECT and FORALL inserts would be if there was some heavy PL/SQL code processing the data that couldn't be done using SQL alone.

                          99.999% of the time, using pure SQL is the correct way.  I've rarely had need to use BULK COLLECT methods, even though I know perfectly well how to use them, and I've written many years worth of code.

                          • 10. Re: Forall insert Vs Direct insert
                            Raunaq

                            Suppose you are writing an INSERT statement inside a LOOP which is executing 10,000 times.

                            There the performance of pure SQL will be impacted.Because , the single SQL statement will be sent to server for processing 10,000 times.

                             

                            In that case , BULK collect is more powerful in terms of performance.

                            • 11. Re: Forall insert Vs Direct insert
                              Raunaq

                              Go through the below link for a detailed picture of BULK COLLECT

                               

                              PLSQL 101

                              • 12. Re: Forall insert Vs Direct insert
                                Ashu_Neo

                                user4295847 wrote:

                                 

                                In my project ...they have used  BULK COLLECT INSERT  many places..

                                Yes; those are real time examples of using bulk collect and you need a complete work through of it.

                                As there might be many reasons in your project to code in plsql as to implement business logic and data processing/manipulation before and after bulk collect! As Blushadow has already notified that it requires in case of heavy data processing or something cannot be possible without plsql. Even all those programs are not only doing just a single job of bulk insertion!

                                 

                                So don't go blindly and replace everywhere where you find it. First analyze, where direct-path insert will be fit in(Like:- loading metadata from data files using SQL Loader) and there will be no impact to other inter-connected sub-systems after change, talk with experience colleagues( who know the full system more; as there are not fool sitting around and don't find use of bulk collect slowing down performance and can be replaced by some other way!

                                 

                                Remember one thing, here just take all right ideas, hints, concepts and understand all things as how all work in better way! Then you be the better person from rest of us to know how your system talks and use the best solution fit in at last!

                                Thanks!

                                • 13. Re: Forall insert Vs Direct insert
                                  BluShadow

                                  Raunaq wrote:

                                   

                                  Suppose you are writing an INSERT statement inside a LOOP which is executing 10,000 times.

                                  There the performance of pure SQL will be impacted.Because , the single SQL statement will be sent to server for processing 10,000 times.

                                   

                                  In that case , BULK collect is more powerful in terms of performance.

                                   

                                  If you're writing an INSERT statement in a loop that executes 10,000 times you've written bad code in the first place.  What data are you inserting? Where has it come from?  If it's from the database you shouldn't be doing it in a loop in the first place, it should be a single INSERT...SELECT... statement.

                                  Yes, BULK COLLECT and FORALL is better than row by row processing, but better still is pure SQL statements, so that doesn't answer the OP's question as to what scenarios it would be good to use BULK COLLECT methods, it just says that bulk collect is better than a poorer method (a method which isn't pure SQL as you suggest)

                                  • 14. Re: Forall insert Vs Direct insert
                                    Paul  Horth

                                    I think you are confused.

                                     

                                    The single insert doesn't get sent 10000 times: it gets sent once.

                                     

                                    The whole point is you don't use a loop at all.

                                    1 2 Previous Next