INSERT-SELECT operations
An INSERT-SELECT query selects values from a source and inserts them into a target, as in the following example:
=> INSERT /*+direct*/ INTO destination SELECT * FROM source;
You can optimize an INSERT-SELECT query by matching sort orders or using identical segmentation.
Matching sort orders
To prevent the INSERT operation from sorting the SELECT output, make sure that the sort order for the SELECT query matches the projection sort order of the target table.
For example, on a single-node database:
=> CREATE TABLE source (col1 INT, col2 INT, col3 INT);
=> CREATE PROJECTION source_p (col1, col2, col3)
AS SELECT col1, col2, col3 FROM source
ORDER BY col1, col2, col3
SEGMENTED BY HASH(col3)
ALL NODES;
=> CREATE TABLE destination (col1 INT, col2 INT, col3 INT);
=> CREATE PROJECTION destination_p (col1, col2, col3)
AS SELECT col1, col2, col3 FROM destination
ORDER BY col1, col2, col3
SEGMENTED BY HASH(col3)
ALL NODES;
The following INSERT operation does not require a sort because the query result has the column order of the projection:
=> INSERT /*+direct*/ INTO destination SELECT * FROM source;
In the following example, the INSERT operation does require a sort because the column order does not match the projection order:
=> INSERT /*+direct*/ INTO destination SELECT col1, col3, col2 FROM source;
The following INSERT does not require a sort. The order of the columns does not match, but the explicit ORDER BY clause causes the output to be sorted by col1, col3, and col2:
=> INSERT /*+direct*/ INTO destination SELECT col1, col3, col2 FROM source
GROUP BY col1, col3, col2
ORDER BY col1, col2, col3 ;
Identical segmentation
When selecting from a segmented source table and inserting into a segmented destination table, segment both projections on the same column to avoid resegmenting the data, as in the following example:
=> CREATE TABLE source (col1 INT, col2 INT, col3 INT);
=> CREATE PROJECTION source_p (col1, col2, col3) AS
SELECT col1, col2, col3 FROM source
SEGMENTED BY HASH(col3) ALL NODES;
=> CREATE TABLE destination (col1 INT, col2 INT, col3 INT);
=> CREATE PROJECTION destination_p (col1, col2, col3) AS
SELECT col1, col2, col3 FROM destination
SEGMENTED BY HASH(col3) ALL NODES;
=> INSERT /*+direct*/ INTO destination SELECT * FROM source;