1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Direct Path Insert

Discussion in 'SQL PL/SQL' started by prashant, Sep 11, 2010.

  1. prashant

    prashant Forum Advisor

    Messages:
    85
    Likes Received:
    7
    Trophy Points:
    160
    Location:
    New Delhi, India
    Hi,

    I have 2 identical big tables (1 GB size and around 6 million rows).

    Inserts to both these tables happen together throughout the day except for 1 stage where only one table is inserted into.

    The requirement is to update the second table after this stage with the changed data from the first table.

    I have a few options to go about the same.

    1) Build up a plsql collection with select * from table1 minus select * from table2 and bulk insert into table 2 from this collection.

    2) truncate table 2 each day and insert /*+ APPEND */ into table 2 as select * from table 1.

    In the first option the minus query is too costly and takes around 3 mins to execute.

    Can you suggest any other option apart from the above mentioned options to achieve the requirement?

    Also if option 2 is the best, kindly let me know negative impact if any which could be caused by this.

    Thanks in advance.

    Best Regards,
    Prashant
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    I cannot. Not knowing the complete details of this configuration it's difficult to offer options.
     
  3. erpsindhu@gmail.com

    erpsindhu@gmail.com Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    75
    more details needed