Results 1 to 4 of 4

Thread: Insert -faster

  1. #1
    Join Date
    Jul 2003
    Posts
    32

    Unanswered: Insert -faster

    i want to insert records from the SQL file (all insert statements).

    Is there anyway to speed the insert statements..

    Shall i use ...
    ALTER TABLESPACE USERS NOLOGGING
    ALTER TABLE EMP NOLOGGING

  2. #2
    Join Date
    Jan 2004
    Location
    Hyderabad, India
    Posts
    37
    Hi,

    If you create a table with NOLOGGING, then direct path loads and 'create table as select ' statements will not be logged into redo logs.

    But all the subsequent dml statements will be logged into redo logs irrespective of the LOGGING clause.

    If you want your insert queries to run faster, then disable all indexes on the table and then insert the data. after inserting, rebuilding the indexes.
    Regards
    Suneel

  3. #3
    Join Date
    Aug 2003
    Posts
    328
    You might also try to do a copy commit
    SET ARRAYSIZE <Number Of Records in each batch>
    SET COPYCOMMIT <Number of batches should be commited at one time>
    A periodic commit will keep data from bottlenecking.

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    don't forget about disabling constraints referenced to/from the table.

    sqlloader is fast.
    If you can get the data as a flat file I would suggest loading this way.
    - 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
  •