Results 1 to 6 of 6

Thread: Bulk Collect

  1. #1
    Join Date
    Dec 2004
    Posts
    74

    Unanswered: Bulk Collect

    Hey,

    I am currently using this procedure to load a table:

    PROCEDURE xyz
    AS
    TYPE coll IS TABLE OF my_view_v%ROWTYPE INDEX BY PLS_INTEGER;

    v_coll coll;
    BEGIN

    SELECT
    *
    bulk collect INTO
    v_coll
    FROM
    my_view_v;

    forall i
    IN v_coll.first..v_coll.last
    INSERT
    INTO TABLE_XYZ
    VALUES
    v_coll(i);

    COMMIT;
    END;

    At first this worked perfectly, but now that the number of records as increased immensely, the 'SELECT * bulk collect INTO v_coll' is failing with error ORA-0430 - out of memory.

    I know there is a way to process a specific number of rows at a time instead of all at once, but I can't seem to get it to work correctly. Could someone show me how to modify my code to achieve this?

    Thanks

  2. #2
    Join Date
    Feb 2007
    Location
    The Farm
    Posts
    35
    Spence32,

    I was going to be a smart-alec and tell you to read the documentation, but then I spent 10 minutes trying to find the documentation and I knew what I was looking for!!!

    use the LIMIT command.

    Finally found the reference:
    http://download-east.oracle.com/docs...ms21.htm#34222

    Mind you, you will need to put in looping to get all of your rows. Not signicant.

    Bar

  3. #3
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    I am at a lost here why this is not simply a.. insert into table select * from table.

  4. #4
    Join Date
    Feb 2007
    Location
    The Farm
    Posts
    35
    Not sure I am clear on the question, but here is the answer I think you are looking for.

    A straight insert/select invokes tremendous over-head. For each record the PLSQL engine must (context) switch to the SQL engine to select and insert the record then switch back to the PLSQL engine. BULK processing is special programming added to the PLSQL engine, in 8i I believe, that allows the PLSQL engine to build a list "events" (eg inserts) switch once to the SQL engine process the entire list and then switch back. The efficiency gain is quite extraordinary.

    Bar

  5. #5
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Quote Originally Posted by Bartron
    A straight insert/select invokes tremendous over-head.
    NO ! I'm sure you want to say that SELECT, then FETCH and INSERT each record one by one is a LOT of overhead, NOT a straight INSERT...SELECT.

    Quote Originally Posted by Bartron
    For each record the PLSQL engine must (context) switch to the SQL engine to select and insert the record then switch back to the PLSQL engine. BULK processing is special programming added to the PLSQL engine, in 8i I believe, that allows the PLSQL engine to build a list "events" (eg inserts) switch once to the SQL engine process the entire list and then switch back. The efficiency gain is quite extraordinary.
    True in general but here... no.

    BULK processing can be useful. FORALL helps doing only one PLSQL to SQL switch and then process a list of data, that is right... That can help a lot when you are getting data within arrays from a client program for example... BUT here what the OP is trying to do is just, as JMartinez suggested :
    Code:
    INSERT INTO TABLE_XYZ
    SELECT *
    FROM my_view_v;
    That is pure SQL, much more efficient than BULK COLLECTing in PLSQL and then using FORALL. And much more efficient in terms of memory use, which is the OP's problem .

    Spence23, always remember to specify the columns you are working on or the tiniest change on the schema may break a lot of your code , for example :
    Code:
    INSERT INTO TABLE_XYZ(c1)
    SELECT c1
    FROM my_view_v;
    HTH & Regards,

    rbaraer
    Last edited by RBARAER; 02-15-07 at 10:04.
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  6. #6
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Your answer fits more backwards, Bartron.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •