Queries with select into clauses create a new table in which to store the query’s result set. Adaptive Server optimizes the base query portion of a select into command in the same way it does a standard query, considering both parallel and serial access methods. A select into statement that is executed in parallel:
Creates the new table using the columns specified in the select into statement.
Creates N partitions in the new table, where N is the degree of parallelism that the optimizer chooses for the insert operation in the query.
Populates the new table with query results, using N worker processes.
Unpartitions the new table, if no specific destination partitioning is required.
Performing a select into statement in parallel requires more steps than an equivalent serial query plan. This is a simple select into done in parallel:
select * into RAT2 from RA2 QUERY PLAN FOR STATEMENT 1 (at line 1). Executed in parallel by coordinating process and 2 worker processes. 4 operator(s) under root The type of query is INSERT. ROOT:EMIT Operator |EXCHANGE Operator (Merged) |Executed in parallel by 2 Producer and 1 Consumer processes. | | |EXCHANGE:EMIT Operator | | | | |INSERT Operator | | | The update mode is direct. | | | | | | |SCAN Operator | | | | FROM TABLE | | | | RA2 | | | | Table Scan. | | | | Forward Scan. | | | | Positioning at start of table. | | | | Executed in parallel with a 2-way partition scan. | | | | | | TO TABLE | | | RAT2 | | | Using I/O Size 2 Kbytes for data pages.
Adaptive Server does not try to increase the degree of the stream coming from the scan of table RA2, and uses it to do a parallel insert into the destination table. The destination table is initially created using round-robin partitioning of degree two. After the insert, the table is unpartitioned.
If the data set to be inserted is not big enough, Adaptive Server may choose to insert this data in serial. The scan of the source table can still be done in parallel. The destination table is then created as an unpartitioned table.
The select into allows destination partitioning to be specified. In such a case, the destination table is created using that partitioning, and Adaptive Server finds the most optimal way to insert data. If the destination table must be partitioned the same way as the source data, and there is enough data to insert, the insert operator executes in parallel.
The next example shows the same partitioning for source and destination table, and demonstrates that Adaptive Server recognizes this scenario and chooses not to repartition the source data.
select * into new_table partition by range(a1, a2) (p1 values <= (500,100), p2 values <= (1000, 2000)) from RA2 QUERY PLAN FOR STATEMENT 1 (at line 1). Executed in parallel by coordinating process and 2 worker processes. 4 operator(s) under root The type of query is INSERT. ROOT:EMIT Operator |EXCHANGE Operator (Merged) |Executed in parallel by 2 Producer and 1 Consumer processes. | | |EXCHANGE:EMIT Operator | | | | |INSERT Operator | | | The update mode is direct. | | | | | | |SCAN Operator | | | | FROM TABLE | | | | RA2 | | | | Table Scan. | | | | Forward Scan. | | | | Positioning at start of table. | | | | Executed in parallel with a 2-way partition scan. | | | | | | TO TABLE | | | RRA2 | | | Using I/O Size 16 Kbytes for data pages.
If the source partitioning does not match that of the destination table, the source data must be repartitioned. This is illustrated in the next example, where the insert is done in parallel using two worker processes after the data is repartitioned using a 2-to-2 exchange operator that converts the data from range partitioning to hash partitioning.
select * into HHA2 partition by hash(a1, a2) (p1, p2) from RA2 QUERY PLAN FOR STATEMENT 1 (at line 1). Executed in parallel by coordinating process and 4 worker processes. 6 operator(s) under root The type of query is INSERT. ROOT:EMIT Operator |EXCHANGE Operator (Merged) |Executed in parallel by 2 Producer and 1 Consumer processes. | | |EXCHANGE:EMIT Operator | | | | |INSERT Operator | | | The update mode is direct. | | | | | | |EXCHANGE OperatorEXCHANGE Operator ( Merged) | | | |Executed in parallel by 2 Producer and 2 Consumer processes. | | | | | | | | |EXCHANGE:EMIT Operator | | | | | | | | | | |SCAN Operator | | | | | | FROM TABLE | | | | | | RA2 | | | | | | Table Scan. | | | | | | Forward Scan. | | | | | | Positioning at start of table. | | | | | | Executed in parallel with a 2-way partition scan. | | | | | | TO TABLE | | | HHA2 | | | Using I/O Size 16 Kbytes for data pages.
