Oracle Tips  by Burleson Consulting

The Data Warehouse Development Life Cycle

Parallelism And Oracle Data Warehousing

Parallel Index Building

Parallel index builds are often useful to the Oracle database administrator who needs to rebuild indexes that have either spawned too many levels or contain too many deleted leaf rows. Parallel index creation is also useful when importing Oracle data warehouse tables. Because data warehouses are so large, Oracle exports never capture the indexes and only export the row values. If a recovery of a table becomes necessary, the Oracle Remote DBA can use parallel create to speed up the index re-creation. Parallel index creation takes place by allowing the degree of parallelism to be specified in the create index statement. For example:

ALTER INDEX customer_pk
REBUILD PARALLEL 10;

Because this type of index creation always involves reading the old index structure and a large sort operation, Oracle is able to dedicate numerous, independent processes to simultaneously read the base index and collect the keys for the new index structure. Just like parallel query, each sub-query task returns ROWID and key values to the concurrency manager. The concurrency manager collects this information for input in the key sorting phase of the index rebuild. For very large data warehouse tables, parallel index creation can greatly reduce the amount of time required to initially create or rebuild indexes. For more information about when Oracle indexes require rebuilding, see Chapter 8, Oracle Features For The Data Warehouse.

Some Oracle professionals mistakenly believe that it is necessary to have parallel processors (SMP or MPP) in order to use and benefit from parallel processing. Even on the same processor, multiple processes can be used to speed up queries. Oracle parallel query option can be used with any SQL SELECT statement--the only restriction being that the query performs a full-table scan on the target table.

Even if your system uses RAID or LVM, some performance gains are available with parallel query. In addition to using multiple processes to retrieve the table, the query manager will also dedicate numerous processes to simultaneously sort the result sets from a large query. (See Figure 7.10.)

If you like Oracle tuning, you may enjoy the book Oracle Tuning: The Definitive Reference , with over 900 pages of BC's favorite tuning tips & scripts.  You can buy it directly from the publisher and save 30%, and get instant access to the code depot of Oracle tuning scripts.