The line
""Performing
index creation, re-creation, or maintenance after Import completes is
generally faster than updating the indexes for each row inserted by
Import""
is a generalisation.
You can have a case where you are using import to append rows to a
table (eg new rows being added to existing
rows or you have truncated the table and are "re-loading" the
data) -- where the table and indexes on it co-exist.
Of course, if you plan to build the indexes seperately, using a script,
you can put in a lot of performance "boosters" :
a)Use the PARALLEL Hint, Use the NOLOGGING Hint
b) Run multiple CREATE INDEX scripts in Parallel
c) Use a larger SORT_AREA_SIZE (or PGA_AGGREGATE_TARGET)
d) Create seperate TEMPORARY Tablespaces specifically for use
during the Index creation (eg on "temporary" disks
or, in earlier days, when using DMT and Manual Allocation, you want
larger EXTENT sizes than those in the "normal"
TEMPORARY Tablespace etc)
As for "Specifying COMMIT=y prevents
rollback segments from growing inordinately large and improves the
performance of large imports.",
it actually helps you improve the performance of a *rollback* if
your import were to fail
(eg instance failure mid-way, space allocation errror etc).
Hemant
At 03:38 AM Friday, Allen, Brandon wrote:
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
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.
- -----Original Message-----
- From: oracle-l-bounce@xxxxxxxxxxxxx [ mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Jared Still
- Sent: Thursday, August 18, 2005 12:28 PM
- To: fred_fred_1@xxxxxxxxxxx
- Cc: oracle-l@xxxxxxxxxxxxx
- 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
Hemant K Chitale
http://web.singnet.com.sg/~hkchital
--
//www.freelists.org/webpage/oracle-l
