How can I quickly update/insert 5 or 6 million rows into a table.

schjo03
schjo03 used Ask the Experts™
on
I have tried the insert exception update method. That took an incredibly long time.
Next I tried a Merge "upsert". That took almost 5 hours as well.
I have a primary key on the target table and the source data is sorted by those key values.
I am breaking the rows into logical blocks of 50,000 and committing.
explain plan shows access by index rowid for both tables
I am out of ideas. Any suggestions welcomed.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Naveen KumarProduction Manager / Application Support Manager

Commented:
can you give your code script ?

Commented:
Try using /*+append*/ hint for direct INSERT. This can reduce the logging. With 5,6 mil rows you could do this very easily. If you can, drop all the primary keys/constrains and recreate them after the load.

or, set the table in NoLogging mode to bypass the redo.

HTH.

~Sve
Devinder Singh VirdiLead Oracle DBA Team

Commented:
Usually Insert is much faster than Merge.
If your table is partitioned table, then you can exchange the partition.
For inserting, you can either use parallelism by using hint or at object level.
You can think of decreasing IO by setting the parameter DB_FILE_MULTIBLOCK_READ_COUNT=0, lets oracle decide its best value during execution.
If you choose parallelism, then you can set PARALLEL_EXECUTION_MESSAGE_SIZE=16K.

>>explain plan shows access by index rowid for both tables
Using index doesnt mean that you will gain performance. Since you are loading 5 to 6 millions records, you need to find how much % of data you are inserting from your table.
Here you can try HASH join with PARALLEL hint.
Also You can think of tablespace with bigger extent size. therefore Oracle should not waste time in allocating extents to segment.

Is is possible to rename the object from where you are trying to insert.

Before coming with solution,  we need to know insert statement, and source DB specification or table is present in same user/different user/ different database over diff network/ if firewall is there etc



Most Valuable Expert 2011
Top Expert 2012
Commented:
What is the plan? Do you have a trace?  What are you waiting on?  The read or the write?

Author

Commented:
sorry i took so long to respond
Here is a the load script I use
it does about 90% update 10% insert

CREATE OR REPLACE PROCEDURE LOAD_SALES
       authid current_user
       AS
 
 CURSOR CMN1 IS
 SELECT MIN_KEY
      , MAX_KEY
      , CT
      , BUCKET
      , PROCESSED
      , ROWID
   FROM BUCKET_TABLE
  ORDER BY BUCKET DESC;
   
 PROCEDURE load_history_month IS  
 
 BEGIN                                

      EXECUTE IMMEDIATE 'DROP TABLE BUCKET_TABLE PURGE';
      EXECUTE IMMEDIATE '
      CREATE TABLE
             CREATE TABLE BUCKET_TABLE TABLESPACE TBS_SMALL AS
      SELECT MIN(KEY_SK) AS MIN_KEY
            ,MAX(KEY_SK) AS MAX_KEY
            ,COUNT(*) CT
            ,BUCKET
        FROM (SELECT KEY_SK, NTILE(75)
        OVER(ORDER BY KEY_SK) BUCKET
        FROM LOCAL_SLS
       WHERE KEY_SK > 0)
       GROUP BY BUCKET';
 
       COMMIT;
                 
       BEGIN

      FOR VAL IN CMN1 LOOP          
                       
          MERGE INTO SALES_HISTORY
               USING (SELECT * FROM LOCAL_SLS WHERE KEY_SK BETWEEN VAL.MIN_KEY AND VAL.MAX_KEY)
                  ON (SKU              = SKU_NK
                 AND LOCATION          = LOCATION_NK
                 AND RETAIL_YEAR_MONTH = DAY_SK)
                WHEN MATCHED THEN
              UPDATE
                 SET ON_HAND_QUANTITY = ON_HAND_QTY
                    ,ON_HAND_COST     = ON_HAND_DOLLARS
                WHEN NOT MATCHED THEN
              INSERT (
                     SKU                
                   , LOCATION          
                   , RETAIL_YEAR_MONTH  
                   , SALES_QUANTITY    
                   , SALES_COST        
                   , SALES_VALUE        
                   , RETURN_QUANTITY    
                   , RETURN_COST        
                   , RETURN_VALUE      
                   , ON_HAND_QUANTITY  
                   , ON_HAND_COST
                     )
                     VALUES
                     (
                     SKU_NK
                   , LOCATION_NK
                   , DAY_SK
                   , 0,0,0,0,0,0
                   , ON_HAND_QTY
                   , ON_HAND_DOLLARS
                     );        
               
      UPDATE BUCKET_TABLE
         SET PROCESSED = 'TRUE'
       WHERE ROWID  = VAL.ROWID;                                    

      COMMIT;                
                   
      END LOOP;
     
      EXCEPTION
           WHEN OTHERS THEN
                DBMS_OUTPUT.PUT_LINE('Unexpected Error During Merge Process '||SQLERRM);
       
      END;    
     
 EXCEPTION
      WHEN OTHERS THEN
           DBMS_OUTPUT.PUT_LINE('Unexpected Error During Bucket Process '||SQLERRM);

 END load_sales_history_month;  

END LOAD_SALES;
 

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial