Parallel Inserts

Enterprise Edition Home | Express Edition Home | Previous Page | Next Page Parallel Database Query > Database Server Operations That Use PDQ >

Parallel Inserts

The database server performs the following types of inserts in parallel:

SELECT...INTO TEMP inserts using explicit temporary tables.
INSERT INTO...SELECT inserts using implicit temporary tables.

For information on implicit and explicit temporary tables, see the chapter on where data is stored in the IBM Informix: Dynamic Server Administrator's Guide.

Explicit Inserts with SELECT...INTO TEMP

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

SELECT * FROM table1 INTO TEMP temp_table

To perform parallel inserts into a temporary table

Set PDQ priority > 0.
You must meet this requirement for any query that you want the database server to perform in parallel.
Set DBSPACETEMP to a list of two or more dbspaces.
This step is required because of the way that the database server performs the insert. To perform the insert in parallel, the database server first creates a fragmented temporary table. So that the database server knows where to store the fragments of the temporary table, you must specify a list of two or more dbspaces in the DBSPACETEMP configuration parameter or the DBSPACETEMP environment variable. In addition, you must set DBSPACETEMP to indicate storage space for the fragments before you execute the SELECT...INTO statement.

The database server performs the parallel insert by writing in parallel to each of the fragments in a round-robin fashion. Performance improves as you increase the number of fragments.

Implicit Inserts with INSERT INTO...SELECT

The database server can also insert rows in parallel into implicit tables that it creates when it processes SQL statements of the form INSERT INTO...SELECT. 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 target tables meet the following criteria:

The value of PDQ priority is greater than 0.
The target table is fragmented into two or more dbspaces.
The target table has no enabled referential constraints or triggers.
The target table is not a remote table.
In a database with logging, the target table does not contain filtering constraints.
The target table does not contain columns of TEXT or BYTE data type.

The database server does not process parallel inserts that reference an SPL routine. For example, the database server never processes the following statement in parallel:

INSERT INTO table1 EXECUTE PROCEDURE ins_proc