RE: Import Question

I've seen this suggestion regarding indexes=n before, and it's even mentioned in the standard documentation ( http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96652/ch02.htm#1006397), but I don't understand it. How does setting indexes=n and then creating the indexes later improve performance? I see how it would get the data into the tables faster, but then you're most likely going to wait for the indexes to be created afterwards before you actually start working with the data, so what's the difference?  

If indexes=y meant that the indexes were created before the INSERTs, then I would understand - but that is not the case as far as I can tell (I just did some testing/tracing to verify). With indexes=y, the import goes in this order:  

CREATE TABLE t1
INSERT INTO t1

    Repeat n times
CREATE INDEX ON t1  

CREATE TABLE t2
INSERT INTO t2

    Repeat n times
CREATE INDEX ON t2  

But the documentation makes it sound like this is not the case: "Performing index creation, re-creation, or maintenance after Import completes is generally faster than updating the indexes for each row inserted by Import"  

This makes it sound like the indexes are created before the inserts if you leave indexes=y, which I don't believe is true so it seems that this may be another case of misleasing/incorrect documentation.  

Can anyone clear this up for me?  

I see in Jared's recommendation below that the index creation can be done in parallel afterwards and I see how that would improve performance - but is that the only case where it makes sense to do it this way, or is there still some benefit to creating the indexes at the end even if they're still done serially?  

Also, it seems to me that commit=n is the way to go too, but why then does the documentation say "Specifying COMMIT=y prevents rollback segments from growing inordinately large and improves the performance of large imports."? I understand the part about rollback segment growth, but why do they say it improves performance of large imports?  

Thanks,
Brandon    

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Jared Still Sent: Thursday, August 18, 2005 12:28 PM To: fred_fred_1_at_hotmail.com
Cc: oracle-l_at_freelists.org
Subject: Re: Import Question

Kirti Deshpande once published here a rather exhuastive list of import speedups.

Here's a list of what I have used, though I think Kirti had more.

Turn off archiving. This is a big timesaver. Turn it on and make a backup when finished.

If your database is using rollback segments, shrink them all and disable all but 1 of them. This will usually avoid any failure to extend errors. Be sure to shrink and re-enable when finished.

Tweak the import parameters:

commit=n
analyze=n
indexes=n
constraints=n
buffer=67108864 - max size on OS ( windows max I think ) recordlength=65535 - max size of buffer filled before writing to db - 64k is max

Extract the DDL from the export file and create the indexes after the import. Split into several DDL files and run in parallel. Dependent on your IO bandwidth.

HTH Jared

Privileged/Confidential Information may be contained in this message or attachments hereto. Please advise immediately if you or your employer do not consent to Internet email for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of this company shall be understood as neither given nor endorsed by it.

--

http://www.freelists.org/webpage/oracle-l Received on Thu Aug 25 2005 - 14:39:14 CDT