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

David Shi wrote:

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.

As a former Platinum employee I used to use FUO quite a bit. FUO's sole purpose in life is to retrieve data as quickly as possible. 10x's faster wasn't unusual.

FUO makes OCI calls to determine which datablocks in the tablespace data file the table resides, then reads those blocks directly off the disk. During the read it locks the table for the duration of the unload so it doesn't have to worry about using rollback segments for consistent gets. You have options to write the resulting file in a format that can be read by Sybase BCP, DB2 utilities, comma-separated and other formats.

Unfortunately Oracle won't authorize Platinum to write a Fast Load utility. This is annoying since Platinum has such utilities for Sybase and DB2. Apparently the data block structure and write locking mechanisms are trade secrets. The best you can do is use SQL*Loader.

SQL*Loader gets its speed from bypassing rollback seqment processing and writing directly to the data blocks. It also disables triggers.

The Platinum TSReorg uses SQL*Loader direct path loads as one of its options, but will fall back to using INSERTs thru OCI calls if it detects and errors. TSR can use FUO if it's in the environment.

If you're doing a lot of these unloads, the cost of the software is significantly lower than upgrading your hardware.

If you have Syncsort, you might want to extract your data to text and sort it later. This way you bypass using indexes and temp segments.

Hope that helps.
--Bob.

rnighting_at_comshare.com Received on Fri Dec 26 1997 - 00:00:00 CST