Results 1 to 13 of 13
  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: UTL_FILE vs GLOBAL TEMP TABLE

    We have a PACKAGE which reads from a file, parses a fixed length string into fields, does some processing, and then inserts those resords into a table.

    When we use UTL_FILE to grab on record at a time, process, then insert, we get an exponentially longer runtime as the source file grows in size.

    However, if we use UTL_FILE to load a GLOBAL TEMPORARY TABLE (which is set up with one long VARCHAR2 field) and use that as the record source, the PACKAGE scales linearly as the file size increases.

    The file resides on the server.

    What could be the interpretation for this? I suppose that it's OK to continue to create a GLOBAL TEMPORARY TABLE for situations like this, but I'd prefer to omit that step if there's something else I can do to just use UTL_FILE.

    Thanks,
    Chuck

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You might consider defining the file as a EXTERNAL TABLE.
    I don't know if it would be faster or if it would scale.
    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
    Dec 2003
    Posts
    1,074
    We considered it, but each row is of variable length, with a newline character after the last byte. We were unable to get an EXTERNAL TABLE DEFINITION to work with it. Or maybe you could interpret that as us abandoning the approach early since the GLOBAL TEMP TABLE was easier and working.

    -Chuck

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Why use utl_file to load the data. Setup an external table with a single string of 4000 characters (assuming this is long enough) and a row termination of newline. This will allow you to access your data very fast. Simply use your parse code to read each record. If each row is the same layout, you could easly write an external table defination, that would be even faster. External files can easly handle variable length rows, as long as the layout is the same.

    You may find it helpful to use the EXTERNAL_TABLE=GENERATE_ONLY parameter in SQL*Loader to get the proper access parameters for a given SQL*Loader control file. When you specify GENERATE_ONLY, all the SQL statements needed to do the load using external tables, as described in the control file, are placed in the SQL*Loader log file. These SQL statements can be edited and customized. The actual load can be done later without the use of SQL*Loader by executing these statements in SQL*Plus.
    Last edited by beilstwh; 06-02-06 at 15:14.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    Dec 2003
    Posts
    1,074
    Awesome! I got the EXTERNAL TABLE set up to read one long CHAR(131) record. I'll get it set up to go head-to-head with the GLOBAL TEMPORARY TABLE and then report back (next week some time).

    -cf

  6. #6
    Join Date
    Dec 2003
    Posts
    1,074
    The external table approach is the fastest.

    I have another question:
    Is it possible to supress the creation of the logfile when accessing the external table? It looks like it's only going to grow in size, and I don't want to have to write something in PERL or something that will truncate it.

    -Chuck

  7. #7
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    You can truncate the table by using utl_file. Open it write, which implies creation, and then close it without putting anything in it. You will have an empty "truncated" external table.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  8. #8
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Bill, I think that Chuck wants to get rid of the LOG file, not source file. I'm not sure can it be done or not ...

    Is it possible (can't try at the moment) to omit LOGFILE clause while creating external table, so that the log file isn't created at all?

  9. #9
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Ahhhhhh, I misunderstood. How about setting the log file location to /dev/null on a unix file system or to nul on windows machines. Thus, they are eaten by the OS, never to be seen again.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  10. #10
    Join Date
    Dec 2003
    Posts
    1,074
    I haven't tried it yet, but if I had RTFM I would've found that NOLOGFILE is a valid option in the EXTERNAL TABLE def'n. I'll tell you if it works.

    -cf

  11. #11
    Join Date
    Dec 2003
    Posts
    1,074
    And it does

    Code:
    CREATE TABLE B220.MED_EXTERNAL_2301_LOADFILE
    (
      FILEINFO  CHAR(400 BYTE)
    )
    ORGANIZATION EXTERNAL
      (  TYPE ORACLE_LOADER
         DEFAULT DIRECTORY B220_DATA_DIR
         ACCESS PARAMETERS 
           ( records delimited by newline
             NOLOGFILE
     fields(
      FILEINFO     char(400))   )
         LOCATION (B220_DATA_DIR:'B220FILE')
      )
    REJECT LIMIT 0;

  12. #12
    Join Date
    Dec 2003
    Posts
    1,074
    One catch still, though, you have to continue to grant WRITE privileges to the user who is SELECTing from the external table, or you get an error. Anyone know why? Is it possible that even though I'm omitting any reference to a BADFILE that the system knows it needs to create one, and test the rights?

    -Chuck

  13. #13
    Join Date
    Dec 2003
    Posts
    1,074
    If you add

    NOLOGFILE NOBADFILE NODISCARDFILE

    then WRITE privileges are not necessary on the DIRECTORY.
    -cf

Posting Permissions

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