Faster imports

From: Paul Moore <paul.moore_at_uk.origin-it.com>
Date: Wed, 19 Jan 2000 12:33:03 +0100
Message-ID: <lZ+FOMmp8RdHB7AHFJ00p7=icIbC@4ax.com>

Can anybody suggest any ways of speeding up an import of a user or table on Oracle (7.3.3.4, 8.0.5, or 8.1.5, i'm interested in all of them!)

Specifically, we have a very large database containing tables of sizes up to 4G. Exporting is not a great problem (direct path export, buffer size = lots, etc etc), but imports take ages. As far as I can see, there is no way of running import in direct path mode, or anything similar - even if we are loading data into a (pre-created but empty) table.

What can be done to improve the speed of imports? Dropping indexes and recreating after, and big buffer sizes helped a bit, but that's all I can find, and we're still looking at over 5 hours to import. The annoying thing is that

CREATE TABLE NEW_TAB
UNRECOVERABLE PARALLEL (DEGREE DEFAULT INSTANCES DEFAULT) AS SELECT * FROM BIG_TAB only takes 35 minutes! (But it requires space for two copies of the data :-()

Surely import can't really be this slow?!?!?

Thanks for any help,
Paul Moore Received on Wed Jan 19 2000 - 05:33:03 CST