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!

Bulk insert 500 million rows

Discussion in 'SQL PL/SQL' started by jaytav001, May 10, 2009.

  1. jaytav001

    jaytav001 Active Member

    Likes Received:
    Trophy Points:
    hi friends... need help...

    i am want to insert 500 million data from sTable to dTable such as

    insert into dTable
    select * from sTable

    time taken : 20 mins
    Note : there are no index or any constraint on both the tables.
    database : oracle 10g

    is there any fast way to insert bulk data ?
  2. rajavu

    rajavu Forum Guru

    Likes Received:
    Trophy Points:
    @ Bangalore , India
    There are lot of dependencies for the bulk inserting process in oracle like table space , redo log size, Server size.. etc.

    Anyway I prefer going with APPEND hint which basically bypasses the DB cache and directly allocates and writes new blocks to data files. There are PL/SQL methods with FORALL is also open for you.

    If this is One time activity , you can go for CTAS approach ,

    Code (Text):

    Create Table dTable  as Select * from sTable;
  3. Suresh Gali

    Suresh Gali Guest


    I have a similar scenario, however the data is 50 million records and it takes an hour to load the target table. I have used bulk collect, append, nologging and still it takes 1hr.

    Any way to improve performance?

    Thanks in advance,
  4. zargon

    zargon Community Moderator Forum Guru

    Likes Received:
    Trophy Points:
    Aurora, CO

    Using the /*+ APPEND */ does not bypass the DB cache, it bypasses the freelist mechanism, the method by which Oracle finds available free space in populated data blocks via the PCTFREE and PCTUSED settings. You did correctly state that it begins populating new blocks at the end of the existing table data.

    Which may or may not be faster. Another approach is to use a PL/SQL block with a bulk collect and forall construct to load the data. Of course whether any of this improves the situation is a matter of how the database and server are configured.