Re: Help ! Performance question : utl_file or sql*loader?

Thanks Dmitry,

    But I cannot use sql*loader to load multiple tables becuase there is an elaborate logic involved in normalizing the denormalized input data, generating unique ID's and things like that. I dont see how I can put that complex logic in sql*loader. My main concern was the effiency of UTL_FILE reading such a huge number of records from the flat file as opposed to reading an internal table after an sql*loader load into the temporary table.
Prahalad
(ppateel_at_iname.com)

>Hi Prahalad,
>
>SQL*Loader is what you need. You should try to use its features
>for loading into multiple tables and eliminate temporary table stage.
>
>Hth,
>Dmitry Pugachev, Oracle DBA
>
><ppateel_at_my-deja.com> ÓÏÏÂÝÉÌ × ÎÏ×ÏÓÔÑÈ
>ÓÌÅÄÕÀÝÅÅ:8lkvlv$8hn$1_at_nnrp1.deja.com...
>> Hi Oracle experts,
>> I have a question. I have a 400,000 record flat file containing data
>> extract from a system. Each of the records in the flat file contains
>> data for about 30 Oracle tables. Which of the following would be the
>> best approach to load the data into Oracle tables.
>>
>> A. Write a plsql procedure and use UTL_FILE to read each record parse
>> each field, process it(lot of bussiness logic) and insert records into
>> Oracle tables.
>>
>> OR
>>
>> B. Create a temporary table in Oracle with same layout as the field
>> layout of the flat file and use SQL*Loader to load the flat file into
>> the temporary table and then plsql procedure read the temporary table
>> and process it and insert into Oracle tables.
>>
>> Any help is greatly appreciated.
>>
>> Thanks in advance
>> Prahalad
>> (ppateel_at_iname.com)
>>
>>
>> Sent via Deja.com http://www.deja.com/
>> Before you buy.
>
Received on Wed Jul 26 2000 - 00:00:00 CDT