Chapter 2 HeatWave

Table of Contents

2.1 Before You Begin

2.2 Loading Data to HeatWave

2.2.1 Prerequisites

2.2.2 Loading Data Manually

2.2.3 Loading Data Using Auto Parallel Load

2.2.4 Monitoring Load Progress

2.2.5 Checking Load Status

2.2.6 Data Compression

2.2.7 Change Propagation

2.2.8 Reload Tables

2.3 Running Queries

2.3.1 Query Prerequisites

2.3.2 Running Queries

2.3.3 Auto Scheduling

2.3.4 Auto Query Plan Improvement

2.3.5 Debugging Queries

2.3.6 Query Runtimes and Estimates

2.3.7 CREATE TABLE ... SELECT Statements

2.3.8 INSERT ... SELECT Statements

2.3.9 Using Views

2.4 Modifying Tables

2.5 Unloading Data from HeatWave

2.5.1 Unloading Tables

2.5.2 Unloading Partitions

2.5.3 Unloading Data Using Auto Unload

2.5.4 Unload All Tables

2.6 Table Load and Query Example

2.7 Workload Optimization

2.7.1 Encoding String Columns

2.7.2 Defining Data Placement Keys

2.8 Workload Optimization using Advisor

2.8.1 Advisor Syntax

2.8.2 Auto Encoding

2.8.3 Auto Data Placement

2.8.4 Query Insights

2.8.5 Unload Advisor

2.8.6 Advisor Command-line Help

2.8.7 Advisor Report Table

2.9 Best Practices

2.9.1 Preparing Data

2.9.2 Provisioning

2.9.3 Importing Data into the MySQL DB System

2.9.4 Inbound Replication

2.9.5 Loading Data

2.9.6 Auto Encoding and Auto Data Placement

2.9.7 Running Queries

2.9.8 Monitoring

2.9.9 Reloading Data

2.10 Supported Data Types

2.11 Supported SQL Modes

2.12 Supported Functions and Operators

2.12.1 Aggregate Functions

2.12.2 Arithmetic Operators

2.12.3 Cast Functions and Operators

2.12.4 Comparison Functions and Operators

2.12.5 Control Flow Functions and Operators

2.12.6 Data Masking and De-Identification Functions

2.12.7 Encryption and Compression Functions

2.12.8 JSON Functions

2.12.9 Logical Operators

2.12.10 Mathematical Functions

2.12.11 String Functions and Operators

2.12.12 Temporal Functions

2.12.13 Window Functions

2.13 SELECT Statement

2.14 String Column Encoding Reference

2.14.1 Variable-length Encoding

2.14.2 Dictionary Encoding

2.14.3 Column Limits

2.15 Troubleshooting

2.16 Metadata Queries

2.16.1 Secondary Engine Definitions

2.16.2 Excluded Columns

2.16.3 String Column Encoding

2.16.4 Data Placement

2.17 Bulk Ingest Data to MySQL Server

2.18 HeatWave Limitations

2.18.1 Change Propagation Limitations

2.18.2 Data Type Limitations

2.18.3 Functions and Operator Limitations

2.18.4 Index Hint and Optimizer Hint Limitations

2.18.5 Join Limitations

2.18.6 Partition Selection Limitations

2.18.7 Variable Limitations

2.18.8 Bulk Ingest Data to MySQL Server Limitations

2.18.9 Other Limitations

When a HeatWave Cluster is enabled, queries that meet certain prerequisites are automatically offloaded from the MySQL DB System to the HeatWave Cluster for accelerated processing.

Queries are issued from a MySQL client or application that interacts with the HeatWave Cluster by connecting to the MySQL DB System. Results are returned to the MySQL DB System and to the MySQL client or application that issued the query.

Manually loading data into HeatWave involves preparing tables on the MySQL DB System and executing load statements. See Section 2.2.2, “Loading Data Manually”. The Auto Parallel Load utility facilitates the process of loading data by automating required steps and optimizing the number of parallel load threads. See Section 2.2.3, “Loading Data Using Auto Parallel Load”.

For MySQL HeatWave on AWS, you can load data into HeatWave using the MySQL HeatWave Console. See Manage HeatWave Data with Workspaces, in the MySQL HeatWave on AWS Service Guide.

For HeatWave in Oracle Database Service for Azure (ODSA), see Importing Data to MySQL HeatWave.

When HeatWave loads a table, the data is sharded and distributed among HeatWave nodes. After a table is loaded, DML operations on the tables are automatically propagated to the HeatWave nodes. No user action is required to synchronize data. For more information, see Section 2.2.7, “Change Propagation”.

On Oracle Cloud Infrastructure (OCI), data loaded into HeatWave, including propagated changes, are automatically persisted by the HeatWave Storage Layer to OCI Object Storage for a fast recovery in case of a HeatWave node or cluster failure. For MySQL HeatWave on AWS, data is recovered from the MySQL DB System.

After running a number of queries, you can use the HeatWave Advisor to optimize your workload. Advisor analyzes your data and query history to provide string column encoding and data placement recommendations. See Section 2.8, “Workload Optimization using Advisor”.