Re: APPLICATION TUNING

BHAVESH PATEL wrote:
>
> Hi
>
> Can any one tell me the answer for the following questions relating to
> application tuning .
> Currently , I have a system which reads data from a flat file and stores it
> in a table after validations. For this we use UTL_FILE built-in package.
> This was working OK for now, it takes 15-20 min, to load 1000-5000 the
> records after validations. But now we are expecting a load of 50000 records
> to be read in and stored in the table after validations.
> I think loading the file thru a sqlloader into a temp table and then
> reading a temp table and validating the record and then inserting it into a
> master table. Which is the best method for the performance? Can anyone
> suggest me what to do or is there any other solution to this or Is there a
> better way to do same thing. I mean is there any other way to load the data
> into a table from a file after validation checks without using UTL_FILEs
> and also can you please tell me which is fastest, UTL_FILE operation which
> reads in one record at a time and validates it and stores it in a table or
> using a temp table and reading from a temp table. Hope to get a clear and
> descriptive answer.
>
> Thanks a lot for your kindness and have a nice weekend and a great day.
> Bhavesh

Assuming a normal load with minimal data manipulation or file format conversion, SQL*Loader will load 50k records in a flash (matter of minutes). Then manipulating 50k rows in ORACLE is a cinch. Don't use UTL_FILE for large data volumes, it's not what it was designed to do. I'm also assuming that row sizes are not very large (< DB block size).

Cheers
HTH

-- 
Nuno Souto
nsouto_at_NOSPAMacay.com.au