DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: direct path insert

  1. #1
    Join Date
    Aug 2000
    Location
    Jolo, Sulu
    Posts
    639

    direct path insert

    Hi Friends,

    Oracle 9i on win2k

    I have a transaction table with 4 million rows. I want to insert it
    to our Master table of 50 million rows. Is there a fast insert in 9i (nologging, no temp) like the sqlloader direct path? I always experience that spooling this transaction table to text file and
    sqlload it using direct path and rebuild indexes is still faster
    than the normal insert command.

    Thanks a lot

  2. #2
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    INSERT /*+ APPEND */ will do this. It has the NOLOGGING and PARALLEL options and generates no/minimum UNDO for the table - however the index updates will be logged and in UNDO.

  3. #3
    Join Date
    Aug 2000
    Location
    Jolo, Sulu
    Posts
    639
    Thanks a lot,

    Anyway, I have to drop the index first like i used to do in sqlldr.

    Do I need to alter the table to nologging?

    Is this the right command?

    sql> insert /*+ APPEND */ into MASTER select * from TRANS;

  4. #4
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    When you use the APPEND hint for INSERT, data is simply appended to a table. Existing free space in the blocks currently allocated to the table is not used.

    If INSERT is parallelized using the PARALLEL hint or clause, then append mode is used by default. You can use NOAPPEND to override append mode. The APPEND hint applies to both serial and parallel insert.

    The append operation is performed in LOGGING or NOLOGGING mode, depending on whether the [NO] option is set for the table in question. Use the ALTER TABLE... [NO]LOGGING statement to set the appropriate value.

  5. #5
    Join Date
    Aug 2000
    Location
    Jolo, Sulu
    Posts
    639
    Thanks dear,

    So, these are the commands right...

    sql> drop index MASTER_NDX;
    sql> alter table MASTER nologging;
    sql> insert /*+ APPEND */ into MASTER select * from TRANS;

    If this will not gererates undo...I wont need to issue commit,
    nor can i issue rollback. Am i right?

  6. #6
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    you still have to issue commit and you can still rollback.

    Becuase you are inserting above the high water mark when you issue rollback it just forgets what it has done and commit just commits it

  7. #7
    Join Date
    Aug 2000
    Location
    Jolo, Sulu
    Posts
    639
    thanks dear,

    So the difference from an ordinary insert is that the undo generated
    for a 1 time commit 4 million records here using the /hint/ will not be as great i guess nor is the temp?

Posting Permissions

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


Click Here to Expand Forum to Full Width