Question is on ways to speed weekly import/export process for 12 tables of
3 million rows with 4 indexes on each.
Assumption, since your note implied that you are re-organizing the tables
every week export and import then:
- Do you really need to actually re-org the table every week. I can
understand the need to rebuild the indexes on a frequent basis, but tables
usually do not need this much care. You might just be able to put the
tables on a rotation plan, and indexes could still be done for some of the
tables that are not rebuilt that week.
- If adequate space is available on your system instead of exporting and
re-importing try renaming the original table, creating an empty version of
the original table with original name, insert into original_table select *
from renamed_table, drop the indexes (still hooked to renamed version),
rebuild the indexes, and now drop renamed version. You should provide a
large rollback segment to support this process, and the insert step can be
broken into multiple inserts depending on the data.
- If the space is not available and you have to use export/import then
try dropping the indexes before exporting, provide a large rollback
segment (alter small segments offline so only the big one is available to
job), import the data, then rebuild the indexes from a script. Be sure
you are setting the buffer= parameter on both the export and import to its
maximum size (approx 180K)
Since you have a down window, I would suggest that you alter your
temporary segments extent size to 50M or larger while you run these
processes and I suggest you bounce your system before and after to allow
you to change the sort area size. This really helps. On 7.2 and 7.3 some
init.ora parameters can be changed on the fly but I do not remember which
ones. We still run 7.1.6 in production.
Hope this gives you something to think about.
Mark Powell -- The only advise that counts is the advise that you follow
so follow your own advise
Received on Tue Apr 01 1997 - 00:00:00 CST
