Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2004
    Posts
    22

    Cool Unanswered: Dealing with million rows ... best approach ??

    I have a come up with a scenario here, and i need your expert opinion on that Please go through each approach and also let me know the steps i have mentioned in them are correct.
    I really appreciate all your help and suggestions in this.
    --------------------------------------------------------------------------I am dealing with millions of rows of data in the tables I have. Many constraints and indexes are built on the table.
    And i want to perform DML operations on it....Insert, Update and Delete on the main table using some other table.

    WHAT IS THE BEST APPROACH AND WHY ????

    Main table i would name as A_prod( field_AP1, field_AP2, filed_AP3, ........., field_APn )
    The other table i would name as A_test( field_AT1, field_AT2, filed_AT3, ........., field_ATn )

    I can use 3 different approaches to perform DML on A_prod table.

    1. Using a cursor FOR loop

    Declare a cursor on A_test table.
    FOR rec_A_test IN cur_A_test LOOP
    INSERT INTO A_production VALUES
    ( rec_A_test.field_AT1, rec_A_test.field_AT2, rec_A_test.filed_AT3, ........., rec_A_test .field_ATn )
    END LOOP;

    2. Using a BULK COLLECT and FORALL statement (Bulk Binding)

    Declare a cursor on A_test table.
    Declare a separate PL/SQL table for every column in A_prod table.
    Populate the PL/SQL tables using the cursor.
    Finally populate the A_prod table using FORALL clause
    CURSOR cur IS SELECT * FROM A_test;
    TYPE TabPLT_C1 IS TABLE OF A_prod.field_AP1%TYPE
    PLT_C1 TabPLT_C1;
    TYPE TabPLT_C2 IS TABLE OF A_prod.field_AP2%TYPE
    PLT_C2 TabPLT_C2;
    ......
    ......
    OPEN cur;
    LOOP
    FETCH cur BULK COLLECT INTO PLT_C1, PLT_C2, ....... LIMIT 1000;
    FORALL i IN 1 .. PLT_C1.COUNT
    INSERT INTO A_prod ..... VALUES ( PLT_C1(i),PLT_C2(i) .... );
    EXIT WHEN cur%NOTFOUND;
    END LOOP;
    CLOSE cur;

    3. Using HINTS with these steps
    OPERATION: UPDATE

    a) First create your dummy temporary table
    CREATE TABLE A_temp
    AS SELECT * FROM A_prod
    WHERE rownum<1.
    b) ALTER TABLE A_temp NOLOGGING.
    c) INSERT /*+ append parallel (A_temp,12) */ INTO A_temp (field_AT1, field_AT2, field_AT3)
    SELECT /*+ parallel (a,12) */ a.field_AT1, my_new_value_for_field2, a.field_AT3
    FROM A_prod a
    WHERE <condition>.
    d) when done, either rename the table,
    DROP TABLE A_prod ;
    RENAME A_temp TO A_prod ;
    or swap the partition if original table is partitioned, Obviously we need to rebuild indecies, etc as required.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Why do you want to get idle speculation from this list rather than you gathering hard facts which would quantify each alternative?

    Enable SQL_TRACE with timing & explain plans enabled.
    Run the results thru TKPROF so you'll have irrefutable proof which is better.

    P.S.
    If/when you RENAME a table all indexes on it are flagged as UNUSEABLE.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

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