The following points describe ways to accelerate the execution of the key generation programs.
Naming convention. The convention "CK_<table_name>" is used to denote the old key / new key tables described under The Big Picture of Key Assignment.
Make the size of your rollback segments large. The exact size is dependent on the number of rows involved in your conversion. Our research has shown that processing 7 million rows generates roughly 3GB of rollback information.
Setup the rollback segment(s) to about 10GB with auto extend to a maximum size of 20GB to determine the high water mark
A next extent value on the order of 100M should be used.
Make sure to turn off all small rollback segments (otherwise Oracle will use them rather than the large rollback segments described above).
After the key assignment programs execute, you can reclaim the space by:
Keep a low value for the "minimum extent" parameter for the rollback.
Shrink the rollback segments and the underlying data files at the end of the large batch jobs.
Compute statistics on the CK_<table_name> tables after every 50% increase in table size. Key generation is performed in tiers or steps because of the inheritance dependency between some tables and their keys. Although key generation for the tier currently being processed is performed by means of set-based SQL, computation of statistics between tiers will allow the database to compute the optimum access path to the keys being inherited from the previous tier's generation run. For Oracle database users, we strongly recommend using the Oracle-provided PL/SQL package to generate statistics rather than the analyze command.
Optimal use of the Mode parameter under Submitting Key Assignment Programs.
Before any key assignments, alter both the "old key" CX_ID index and the "new key" CI_ID index on the CK_<table_name> tables to be unusable.
Run all key assignment tiers, submitting each job with MODE = "I".
Rebuild the CX_ID and CI_ID indexes on the CK_<table_name>. Rebuilding the indexes using both the PARALLEL and NOLOGGING parameters will speed the index creation process in an Oracle database. Statistics should be computed for these indexes.
Run all key assignment tiers that were previously run in MODE = 'I', submitting each job with MODE = 'D'. This will reassign all duplicate keys.
Copyright © 2011, Oracle and/or its affiliates. All rights reserved.