Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2007
    Posts
    6

    Unanswered: SQL*Loader sequence performance question.

    Hello All,

    Here is my situation-

    I am loading a table using sqlldr and I am wanting a unique key for each record. I am pulling the data from multiple input files. If I use sqlldr's SEQUENCE parameter, it loads very quickly (60,000+ records from 5 fixed-width files in under 5 seconds), but it does not give me a unique key. Each input file starts the sequence at one, thus, I have 5 1's, 5 2's, etc.

    Alternatively, if I create an Oracle sequence and use SEQ_NAME.NEXTVAL, I DO get unique keys for each record. However, the processing time goes from under 5 seconds to around 45 seconds.

    The 60,000 records are simply test data. Once this goes into production, I may be getting over 20 million records per day.

    Question: Is there a way to speed up the Oracle sequence? It does exactly what I want and need, but it is slugish compared to the other option.

    Any help is appreciated, please let me know if you would like more information.

    -Brian

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by brianr8011
    Question: Is there a way to speed up the Oracle sequence? It does exactly what I want and need, but it is slugish compared to the other option.
    You can try to increase the CACHE parameter for the sequence, e.g: CREATE SEQUENCE seq_import CACHE 500;

    Check the manual for details.

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Any help is appreciated,
    Can you concatentate the 5 files into a single file prior to loading?
    Can you load a blank field & after the load, UPDATE with a unique (counter) value?
    Can you load using a small PL/SQL procedure using either EXTERNAL TABLE or UTL_FILE?
    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.

Posting Permissions

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