Parallel Data-Manipulation Statements

DELETE, INSERT, and UPDATE statements first fetch the qualifying rows and then perform the delete, insert, or update.

If the data is not in a single local fragment of a table, the database server fetches the qualifying rows in parallel, with one exception. The database server does not process the first part of a DELETE statement in parallel if the targeted table has a referential constraint that can cascade to a child table.

Parallel Inserts into Temporary or External Tables

The database server inserts data into temporary or external tables in parallel in the following cases:

• SELECT...INTO TEMP and SELECT... INTO SCRATCH inserts

  The optimizer can insert rows in parallel into explicit temporary tables that you specify in SQL statements of the form SELECT....INTO TEMP or SELECT...INTO SCRATCH. For example, the optimizer can perform the inserts in parallel into the temporary table, temp_table, as the following sample SQL statement shows:

  SELECT * FROM table1 INTO SCRATCH temp_table 

  To perform the insert in parallel, the database server first creates a fragmented temporary table in the space as specified by the setting of the DBSPACETEMP configuration parameter or the DBSPACETEMP environment variable. The optimizer then writes in parallel to each of the fragments in a round-robin fashion. Performance usually improves as the number of fragments increases.

  For more information on temporary tables, refer to Using Temporary Tables and the IBM Informix: Guide to SQL Syntax.

• INSERT INTO...SELECT inserts

  For example, the database server processes the following INSERT statement in parallel:

  INSERT INTO target_table SELECT * FROM source_table

  The target table can be either a permanent table or a temporary table.

  The database server processes this type of INSERT statement in parallel only when the in the following circumstances:

  • Multiple CPU virtual processors are available.

    On a single-coserver configuration, the NUMCPUVPS configuration parameter must be greater than 1. On a multiple-coserver configuration, the NUMCPUVPS configuration parameter can be set to 1.

  • The target table is fragmented into two or more dbspaces.
  • The target table has no enabled referential constraints.
  • The target table does not contain filtering constraints.
  • The target table does not contain columns of TEXT or BYTE data type
• SELECT... INTO EXTERNAL TABLE statements that unload data

Parallel Index Builds

For index builds, the database server performs both scans and sorts in parallel. The following operations start index builds:

• CREATE INDEX
• Add a unique, primary key
• Add a referential constraint
• ALTER FRAGMENT

Parallel execution can occur during index builds when any of the following conditions exist:

• These operations involve multiple fragments.
• Multiple CPU virtual processors are available.
• Multiple sort threads are available.

If your coservers have multiple CPUs, the optimizer uses one sort thread per CPU virtual processor during index builds.

For more information about index builds, refer to Improving Performance for Index Builds.