This discussion is archived
4 Replies Latest reply: Nov 20, 2012 10:10 PM by BillyVerreynne RSS

Which is Faster

pallis Newbie
Currently Being Moderated
sql *loader and utl_file package which is the faster way to load the data.


Thanks & Regards,
pallis
  • 1. Re: Which is Faster
    sb92075 Guru
    Currently Being Moderated
    pallis wrote:
    sql *loader and utl_file package which is the faster way to load the data.


    Thanks & Regards,
    pallis
    post the results from your benchmark tests

    Handle:     pallis
    Status Level:     Newbie (10)
    Registered:     Feb 20, 2011
    Total Posts:     274
    Total Questions:     96 (58 unresolved)

    why so many unanswered questions?
  • 2. Re: Which is Faster
    Manik Expert
    Currently Being Moderated
    SQL* LOADER is operating system utility to load data files into database.
    UTL_FILE is oracle supplied Package.

    It depends on how you use them, if your load is straight forward I would suggest sql*loader (as it is direcly taken care by OS).

    You may also look into external table concepts as well.


    see this in the following document:

    www.orafaq.com/papers/sqlload.doc


    CONCLUSIONS
    The comparative analysis of SQL*Loader and UTL_FILE reveals that these tools are suitable to your environment subject to the conditions of your needs. If the data load is complex (as is the case in relational databases) UTL_FILE seems to be the tool of choice. This tool does require programmatic effort in terms of writing a wrapper package but the subsequent effort in this direction is greatly reduced once the initial tool kit is built for your environment. The UTL_FILE tightly integrates the file input with the programmatic control and the data manipulation inside a single PL/SQL unit. There are disadvantages of speed in loading in case of UTL_FILE but these are totally offset by the programmatic control it offers and the integration it brings in. Thus we find that UTL_FILE tool bridges the gap left by SQL*Loader for complex data loads.



    Cheers,
    Manik.

    Edited by: Manik on Nov 21, 2012 11:18 AM --- ADDED SOURCE FOR INFORMATION
  • 3. Re: Which is Faster
    jeneesh Guru
    Currently Being Moderated
    Since you are using UTL_FILE, your file will be in DB server..

    So, the best option is EXTERNAL TABLE.
  • 4. Re: Which is Faster
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    pallis wrote:
    sql *loader and utl_file package which is the faster way to load the data.
    Pure speed? SQL*Loader running locally (same server), connecting using an IPC (aka bequeath) connection, using the direct and parallel options, and using a large bind buffer.

    UTL_FILE will never be as fast as
    a) it needs PL/SQL (SQL*Loader is native C/machine code)
    b) it uses basic (primitive serial) I/O (does not support overlap/async I/O)

    So it is slower getting data off the disk. It is slower in crunching that data.

    However, this does not make SQL*Loader the best choice each and every time. The requirements dictate the choice of feature and technology selected.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points