This discussion is archived
1 2 Previous Next 15 Replies Latest reply: Jul 2, 2013 9:30 PM by Raunaq RSS

Forall insert Vs Direct insert

user4295847 Newbie
Currently Being Moderated

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 Expert
    Currently Being Moderated

    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 Oracle ACE
    Currently Being Moderated

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

     

    Hemant K Chitale

  • 3. Re: Forall insert Vs Direct insert
    Karthick_Arp Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated


    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 Pro
    Currently Being Moderated

    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 Expert
    Currently Being Moderated

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

  • 7. Re: Forall insert Vs Direct insert
    user4295847 Newbie
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Guru Moderator
    Currently Being Moderated

    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 Explorer
    Currently Being Moderated

    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 Explorer
    Currently Being Moderated

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

     

    PLSQL 101

  • 12. Re: Forall insert Vs Direct insert
    Ashu_Neo Pro
    Currently Being Moderated

    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 Guru Moderator
    Currently Being Moderated

    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 Expert
    Currently Being Moderated

    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

Legend

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