A quick and dirty hack to the existing PL/SQL code which typically adds good performance improvement is to fetch from your cursor in bulk collect (say 200-500 records at a time).
Otherwise its Perl/C/etc/etc time. hth connor --- Tim Gorman <[EMAIL PROTECTED]> wrote: > Options #1 (Perl) and #2 (PRO*C) would be fastest > and easiest. The PRO*C > demo programs provide a decent start, for option #2. > Option #3 (OCI) would > be not faster than PRO*C and, due to the increased > complexity of OCI, a more > problematic approach. > > SQL*Plus is the easiest method to implement by far, > but the SQL*Plus program > introduces a surprising amount of "overhead" > processing, making it much > slower than Perl or PRO*C, but much faster than > UTL_FILE. If you are not in > a big time crunch but want decent performance, use > SQL*Plus. If you are in > a time crunch and need the best performance, use > Perl or PRO*C. > > > on 1/10/04 6:09 PM, Guang Mei at [EMAIL PROTECTED] > wrote: > > > Hi: > > > > I have a program (running on oracle 8173 server) > that writes 48 Millions > > lines of data into various text files . The > selected data is from various > > tables and I have the query pretty much optimized. > Now I am trying to find > > the fastest way to dump the selected data into a > text file on the same > > oracle server. The program (written as a pl/sql > package) now works > > something like this: > > > > str varchar2(32767) := ''; > > NL char(1) := chr(10); -- new line > character > > begin > > fpn := utl_file.fopen(directory, filename, 'w', > 32767); > > for x in cur1 loop > > str := str || x.str || NL; -- keep > building the str > > if (length (str) > 31000 ) then > > str := substr (str,1, length(str) -1 ); > > utl_file.put_line(fpn, str); > > str := ''; > > end if; > > end loop; > > > > -- dump the last part: > > str := substr (str,1, length(str) -1 ); > > utl_file.put_line(fpn, str); > > utl_file.fflush(fpn); > > utl_file.fclose(fpn); > > end ; > > > > > > The above code works perfect fine now. But I am > wondering if there is > > another way that could increase the writing > siginificantly faster. This > > porgram does not have to be in pl/sql. I can > think of a couple of > > potential approaches: > > > > 1. Write a perl program, basically using perl's > DBI/DBD to select the data > > from the database, then calling perl's "print" to > write data into a file. > > I have not tested this and don't know if it is > faster that > > utl_file.put_line. > > > > 2. Write a C program, using ProC to talk to DB, > then use C's fopen and > > fwrite(?) to dump data into text file. > > > > 3. Write a C program,using OCI to talk to DB, > then use C's fopen and > > fwrite(?) to dump data into text file. > > > > I don't have direct experience with ProC and OCI, > so I don't know how > > faster (or any) it would be by doing Option 2 or > 3 above. Does anyone > > know if I would see siginificant performance boost > by using C? Any other > > suggestions? > > > > TIA. > > > > Guang > > > > > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.net > -- > Author: Tim Gorman > INET: [EMAIL PROTECTED] > > Fat City Network Services -- 858-538-5051 > http://www.fatcity.com > San Diego, California -- Mailing list and web > hosting services > --------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an > E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of > 'ListGuru') and in > the message BODY, include a line containing: UNSUB > ORACLE-L > (or the name of mailing list you want to be removed > from). You may > also send the HELP command for other information > (like subscribing). ===== Connor McDonald web: http://www.oracledba.co.uk web: http://www.oaktable.net email: [EMAIL PROTECTED] "GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day" ________________________________________________________________________ Yahoo! Messenger - Communicate instantly..."Ping" your friends today! Download Messenger Now http://uk.messenger.yahoo.com/download/index.html -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
