The SELECT
query of an
INSERT ...
SELECT
statement is offloaded to HeatWave for
execution, and the result set is inserted into the specified
table on the MySQL DB System. Offloading the
SELECT
query to HeatWave reduces
INSERT ...
SELECT
execution time in cases where the
SELECT
query is long running
and complex. SELECT
queries
that produce large result sets do not benefit from this
feature due to the large number of DML operations performed on
the MySQL DB System instance.
The SELECT
table must be loaded in HeatWave,
and the INSERT
table must be present on the
MySQL DB System. For example, the following statement selects data
from the orders
table on tHeatWave and
inserts the result set into the orders2
table on the MySQL DB System:
Press CTRL+C to copymysql> INSERT INTO orders2 SELECT * FROM orders;
Usage notes:
The
SELECT
portion of theINSERT ... SELECT
statement is subject to the same HeatWave requirements and limitations as regularSELECT
queries.Functions, operators, and attributes deprecated by MySQL Server are not supported in the
SELECT
query.The
ON DUPLICATE KEY UPDATE
clause is not supported.INSERT INTO
statements are not offloaded. Settingsome_view
SELECTuse_secondary_engine=FORCED
does not cause the statement to fail with an error in this case. The statement is executed on the MySQL DB System regardless of theuse_secondary_engine
setting.See Section 2.3.1, “Query Prerequisites” and Section 2.18.9, “Other Limitations”.