Re: Is it normal that dump table is much slower than load into table?

David Shi <dshi_at_magpage.com> wrote in article <6654md$mhi$0_at_204.179.92.69>...
> (please also forward your message to dshi_at_magpage.com, thanks a lot)
>
> I have a flat file of data which is about 160MB, it took about 3
> minutes to do a "direct" sql*load into an Oracle table, but took 30
> minutes to dump the Oracle table back to a flat file using "select *
> from" and direct to a flat file. I can see there are overheads
> associated with "select" but the difference seems too much. Is this a
> normal case?
>
> Oracle version 7.3.3, OS: Solaris 2.5.1 on a SUN 4000.
>
> Also has anyone used Platinum's Fast Unload for Oracle product? It
> claims export data 5-10 times faster than Oracle by bypassing Oracle
> kernel and SQL engine, which sounds very nice for unloading data to
> flat files.
>
> Thanks for your help.
>
> David
>

It may seem trivial, but did you think of suppressing screen output? When selecting and spooling at the same time, SQL*Plus doesn't finish the spool file untill it has completed the screen output. Because screen output is relatively slow, performance can be dramatically increased by either hiding the screen while sppoling is in progress, or by using SET TERMOUT OFF in the script that does the select.

good luck,
Paul. Received on Thu Dec 04 1997 - 00:00:00 CST