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
- -----Original Message-----
- From: oracle-l-bounce@freelists.org
[
mailto:oracle-l-bounce@freelists.org]On Behalf Of Jared
Still
- Sent: Thursday, August 18, 2005 12:28 PM
- To: fred_fred_1@hotmail.com
- Cc: oracle-l@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.
Hemant K Chitale
http://web.singnet.com.sg/~hkchital
--
http://www.freelists.org/webpage/oracle-l
Received on Fri Aug 26 2005 - 07:01:22 CDT
