The following points describe ways to accelerate the execution of the key generation programs.
Gather table statistics after each key generation program runs and before running key generation programs for the next level. Table 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.
Key generation programs are single threaded and use set-based SQL. Consider running key generation jobs from the same level (see Program Dependencies) in parallel. Monitor system resources (CPU, RAM, IO) to fine tune how many jobs can be run simultaneously
Optimal use of the Mode parameter under Submitting Key Assignment Programs.
Before any key assignments, drop both the “old key” CX_ID index and the “new key” CI_ID index on the CK_<table_name> tables. Make sure you save the index DDLs before dropping the indexes. You can use the script provided in Appendix D to save the index DDLs. The script creates another script called create_ck_index.sql , which can be used to recreate the indexes in a subsequent step.
Run all key assignment tiers, submitting each job with MODE = "I".
Recreate the CX_ID and CI_ID indexes on the CK_<table_name>, using the create_ck_index.sql script that was created previously. This script uses parallelism and no logging options to speed up the index creation. After the indexes are created, this script will change the parallelism to 1 and will enable the logging. 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 © 2007, 2016, Oracle and/or its affiliates. All rights reserved. Documentation build: 2.5.2016 10:21:45 [T1_1454696505000]