Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2004
    Posts
    4

    Unanswered: Speeding up SQL*Loader

    We're loading alot of data (20-30gigs) from a flat file created from the extract of a Gemstone database into an Oracle table. At this point, we're doing the table load using the conventional path method. I've just taken over this baby and want to switch over to a direct load. However, one of the columns that is loaded is a sequence number that is generated on the way in. The problem is that direct path loads seem to blow up on this with a "SQL string not allowed on direct path".

    Is there any way around this? Also, any hot tips on how to optimize a sqlload process or a webpage devoted to this (couldn't find a good one on my initial web foray) would be helpful.

    The current datafile is taking 36 hours to load and i need to get that down. We're running Oracle 8i.

    Thanx for any suggestions!
    cheers,
    jim.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Thanx for any suggestions!
    Disable any & all triggers & indexes on the table being loaded
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Direct path loads do not support SQL strings in the control file.
    If the sequence is a dictionary define sequence, then create a before insert trigger to assign the next sequence number.
    If the sequence can be assigned during the load, then instead of an SQL string, use the SEQUENCE or RECNUM controlfile option.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  4. #4
    Join Date
    Nov 2003
    Location
    Bangalore,India
    Posts
    51

    Soln for u

    Take out explicit commit;
    set auto commit on at database level
    Enjoy
    It will reduce the time drastically

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296
    be sure the table you are loading into is set to NOLOGGING

    I once created a trigger to load a sequence number into a column
    as my data was loaded. Not sure if it would work on DIRECT load or not.

    trigger:
    PHP Code:
    drop sequence zero_seq2;

    create sequence zero_seq2;

    create or replace trigger tablename_sequence
        before insert on TableName
        
    for each row
            
    declare
                
    v_seq number;
            
    begin
                select zero_seq2
    .nextval into v_seq from dual;
            if 
    INSERTING then
                
    :new.column_name := v_seq;
            
    end if;
    end;

    - 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
  •