5 Replies Latest reply: Nov 2, 2012 9:00 AM by User286067 RSS

    fastest way to load csv into oracle BE table

    845109
      I have csv file which is having 10 million records in it . what is the fastest approach to load this data to oracle BE table .

      I am using Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production With the Partitioning, OLAP and Oracle Data Mining options .

      csv format
      first_name,last_name,occupation,address

      above all the fields are of varchar data type.

      I have tried to use external table while inserting its taking too much time .


      Thanks
        • 1. Re: fastest way to load csv into oracle BE table
          BluShadow
          842106 wrote:
          I have tried to use external table while inserting its taking too much time .
          I can load 10 million+ records using external tables in a matter of a couple of minutes.

          How are you loading yours using external tables?

          Demonstrate the problem.

          Using external tables or SQL*Loader will be the fastest ways to load the data, as other methods would involve manually processing the CSV data in code, which will take longer.
          Perhaps you're not using External tables correctly? Are you doing a direct path insert from the external table (using APPEND hint on your insert)? Are you using parallel loading on the external table definition?
          • 2. Re: fastest way to load csv into oracle BE table
            Chanchal Wankhade
            hi,

            You can use sql loader.
            options (skip=1)
            LOAD DATA
            INFILE 'csv file path'
            truncate /append
            INTO table table_name
            FIELDS TERMINATED BY ','
            OPTIONALLY ENCLOSED BY '"'
            trailing nullcols
            (
            column name ,
            column name,
            column name
            )
            {code}
            Create this file as ctl(control file.) on the lcoation where your csv file is.
            
            Execute it by goint to the path where your csv file resides.
            {code}
            cmd
            cd path where your csv file resides
            sqllder userid=username/password@database name control=your_control_file_name
            {code}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
            • 3. Re: fastest way to load csv into oracle BE table
              jeneesh
              Chanchal Wankhade wrote:
              hi,

              You can use sql loader.
              Will it be fatser than external table?

              And you wont be able to load data using PL/SQL if you use sql loader. You will end up creating some kind of "OS dependency..."
              • 4. Re: fastest way to load csv into oracle BE table
                kikolus
                Use external tables
                [url http://docs.oracle.com/cd/B19306_01/server.102/b14215/et_concepts.htm]
                • 5. Re: fastest way to load csv into oracle BE table
                  User286067
                  Any performance measure you will implement, will be impacted by number of indexes, triggers, constraints you have on the table. BTW what the heck is a BE table? BEFORE EDITION?