4 Replies Latest reply: Nov 21, 2012 12:10 AM by Billy~Verreynne RSS

    Which is Faster

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


      Thanks & Regards,
      pallis
        • 1. Re: Which is Faster
          sb92075
          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
            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
              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
                Billy~Verreynne
                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.