If you are lucky enough to have oracle enterprise edition

There are a couple of simple words that you can type at the end of many SQL queries and DML statements that can imrpove performance dramatically…  parrallel #number.

My example is:

CREATE INDEX &&DATAOWNER..F03B21_CO ON &&DATAOWNER..F03B21
(ZSCO)
LOGGING
TABLESPACE &&DATAOWNER.I
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
PARALLEL 3 COMPUTE STATISTICS;

This will create 3 additional worker threads that will beaver away at generating the index.

If you have large tables in JDE, you do NOT want to generate indexes through the OMW option “generate indexes”.  This will take too long.  You want to generate the table against a datasource that you do not use (Data Dict for example) with logging on.

From your fat boy command line, run something like:

findstr “CREATE “ jde.log > CREATESTATEMENTS.sql

Then massage all of the CREATE INDEX statements to include the parallel option.

Remember that generating indexes from JDE for large tables is bad… It will generate all of them as use all of the defaults for size, therefore you’ll end up with a pile of badly fragmented indexes that took WAY TOO LONG to generate.

Note that you need to have enterprise edition to use this (well, actually I think that you might be able to specify the parameter, but it’ll be recorded in the DB as a infringement).

Operations That Can Be Parallelized

Oracle can parallelize operations that involve processing an entire table or an entire partition. These operations include:

• SQL queries requiring at least one full table scan or queries involving an index range scan spanning multiple partitions.

• Operations such as creating or rebuilding an index or rebuilding one or more partitions of an index.

• Partition operations such as moving or splitting partitions

• CREATE TABLE AS SELECT operations, if the SELECT involves
  a full table or partition scan.

• INSERT INTO . . . SELECT operations, if the SELECT involves a full table or partition scan.

• Update and delete operations on partitioned tables

 

Some of this was taken from http://www.akadia.com/services/ora_parallel_processing.html