Results 1 to 5 of 5

Thread: Insert Slow

  1. #1
    Join Date
    Jul 2003
    Posts
    32

    Unanswered: Insert Slow

    I have text file which contains insert statements and i ran in to database. it's taking long time to insert the rows (text file size 250 MB).
    how do i run faster ? any bulk insert option or some other method avaiable in oracle.

  2. #2
    Join Date
    Oct 2003
    Location
    Switzerland
    Posts
    140
    Do your inserts always concern the same table ? E.g. your txt-file looks like
    INSERT INTO T (COL1, COL2, COL3) VALUES (VAL1, VAL2, VAL3)
    INSERT INTO T (COL1, COL2, COL3) VALUES (VAL1, VAL2, VAL3)
    INSERT INTO T (COL1, COL2, COL3) VALUES (VAL1, VAL2, VAL3)
    ...

    In that case, one way of speeding up is to use CURSOR_SHARING, a trick to avoid parsing every single statement.

    SQL> alter session set cursor_sharing=force;

  3. #3
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    If the file is as "cvandemaele" shows ... you could also set your
    transaction to use a LARGE rollback segment. Do you have
    indexes that you could delete and rebuild after the load ???
    Can you set the table to NOLOGGING ??? Are there any triggers
    on the table that you could disable for the load ??? How about
    foreign keys that you could set to deferable or disable totally ...
    Is the table that you are inserting into set with small next extents
    and having to extend often during the load ???

    Many things to look at ...

    HTH
    Gregg

  4. #4
    Join Date
    Oct 2003
    Location
    INDIA
    Posts
    7
    Hi

    If you can create the text file of insert statements, I guess you can also create a text file with data seperated by commas "," or pipe "|".
    If you can do so, use sql loder. Its very fast and efficient.
    If you need more information on sql loader, plz mail me at vivek@indorama.net
    I would be glad to help you.

    Warm Regards
    Vivek Bajaj

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296
    drop the associated table indexes, and disable associated constraints before the insert. Then enable and recreate indexes afterwards.

    Another option:
    Create a temp_table.
    PHP Code:
    create table temp_table nologging
    as select from REAL_table where 1 2
    Insert everything into that table (you should set echo off, feedback off, etc. in sql*plus).

    Once all data is in the temp table, then:
    PHP Code:
    insert /*+ APPEND */ into REAL_TABLE
    select 
    from TEMP_TABLE
    also, couldn't hurt to add the APPEND hint to every insert in your file.
    That would be an easy find-replace job.

    find: INSERT
    replace with: INSERT /*+ APPEND */
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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