Re: Processing batches, requesting comments with sqlldr and preapred UPDATes.

On Tue, 23 Oct 2007 15:55:09 -0500, Brian Peasland <dba_at_nospam.peasland.net> wrote:

>Brian Tkatch wrote:
>> On Tue, 23 Oct 2007 12:02:47 -0500, Brian Peasland
>> <dba_at_nospam.peasland.net> wrote:
>>
>>> Have you looked at External Tables? This is available in Oracle 9i.
>>> External Tables use the same SQL*Loader engine so you get the same
>>> speed. And you can do a simple INSERT..SELECT to read the External
>>> Table's contents and populate your (internal) table.
>>
>> The files to be loaded are usually in the thousands. If i just read it
>> correctly, the files must be mentioned in the DDL statement
>> itself.That would not be possible, especially since the list files is
>> still growing, and we could not get a change like that into production
>> quickly enough.
>>
>> Or is the filelist variable?
>>
>> B.
>
>The filelist is not variable. However, you can use the ALTER TABLE
>command to point the External Table to a different file:
>
>ALTER TABLE my_external_tab LOCATION (directoryname:'fileName');

Brian. i appreciate the help here.

In production, i cannot execute any DDL statements, due to policy restrictions. However, i could rename the flat file for each load, but i wonder if that would be faster than using sqlldr and an automatically generated control file. For example, last night we processedd over 2000 data files, and that was a little lower than our daily run.

Now, i really don't know what is better. So i appreciate any direction. We are testing and timing the different solutions.

>Or something like that.......
>
>Another solution is to write some shell script to combine your multiple
>text files into one large text file. This works if the columns are all
>the same for each file...

They are, and i suggested that. :) We may be looking into that. I have to find out if that is possible.

Thanx,
B.

>
>HTH,
>Brian
Received on Tue Oct 23 2007 - 16:05:07 CDT