Create Oracle Table and Index Examples with Parallel Operations

To further illustrate the application of parallel operations in SQL statements, consider the implementations of parallel operations for table and index creation shown in the following listings.

Using parallel operations for table creation

Using parallel operations for index creation

 

The CREATE INDEX statement creates the ORDER_KEY index using parallel sort operations. The CREATE TABLE statement creates a new table ORDER_LINE_ITEMS with a degree of parallelism of four by selecting from an existing OLD_ORDER_LINE_ITEMS table using a parallel operation. In the preceding table creation listing, two separate operations within the CREATE TABLE command are taking advantage of parallelism: the query of the OLD_ORDER_LINE_ITEMS table is parallelized, and the insert into ORDER_LINE_ITEMS is parallelized.

NOTE:

Although parallel queries increase the performance of operations that modify data, the redo log entries are written serially and could cause a bottleneck. By using the NOLOGGING option, you can avoid this bottleneck during table and index creation.

Because the writes to the redo log files are serial, redo log writes may effectively eliminate the parallelism you have defined for your statements. Using NOLOGGING forces the bulk operations to avoid logging, but individual INSERT commands are still written to the redo log files. If you use the NOLOGGING option, you must have a way to recover your data other than via the archived redo log files.

TIP

Use NOLOGGING to remove the I/O bottleneck caused by serial writes to the redo logs.

Up to this point, I have ignored the physical location of the data queried in the example SELECT statements. If a full-scanned table’s data is all contained on a single disk, you may succeed only in creating a huge I/O bottleneck on the disk. An underlying principle of the performance gains that you can achieve using parallel operations is that the data is stored on different devices, all capable of being addressed independently of one another.

Not only that, but using the PEO may make your system perform worse. If your system has processing power to spare but has an I/O bottleneck, using PEO will generate more I/O requests faster, creating a larger queue for the I/O system to manage. If you already have an I/O bottleneck, creating more processes against that same bottleneck will not improve your performance. You need to redesign your data distribution across your available I/O devices.

TIP

Make sure your data is properly distributed or the parallel query server processes may add to existing I/O bottleneck problems.

Returning to the CREATE INDEX statement shown earlier in the “Using parallel operations for index creation” listing, consider the following tips:

• Index creation uses temporary tablespace if there is not enough memory available to perform the sort in memory (SORT_AREA_SIZE). Construct the temporary tablespace in such a way that the physical data files are striped across at least as many disks as the degree of parallelism of the CREATE INDEX statement.
• When adding/enabling a primary or unique key for a table, you cannot create the associated index in parallel. Instead, create the index in parallel first and then use ALTER TABLE to add/enable the constraint and specify the USING INDEX clause. For this to work, the index must have the same name and columns as the constraint.