Chapter 2 HeatWave MySQL

Table of Contents

2.1 Before You Begin

2.2 Loading Data to HeatWave MySQL

2.2.1 Prerequisites

2.2.2 Loading Data Manually

2.2.3 Creating HeatWave Temporary Tables

2.2.4 Loading Data Using Auto Parallel Load

2.2.5 Monitoring Load Progress

2.2.6 Checking Load Status

2.2.7 Data Compression

2.2.8 Change Propagation

2.2.9 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 Dynamic Query Offload

2.3.6 Debugging Queries

2.3.7 Query Runtimes and Estimates

2.3.8 CREATE TABLE ... SELECT Statements

2.3.9 INSERT ... SELECT Statements

2.3.10 Using Views

2.4 Modifying Tables

2.5 Analyzing Tables

2.6 Unloading Data from HeatWave MySQL

2.6.1 Unloading Tables

2.6.2 Unloading Partitions

2.6.3 Unloading Data Using Auto Unload

2.6.4 Unload All Tables

2.7 Table Load and Query Example

2.8 Workload Optimization for OLAP

2.8.1 Encoding String Columns

2.8.2 Defining Data Placement Keys

2.8.3 HeatWave Autopilot Advisor Syntax

2.8.4 Auto Encoding

2.8.5 Auto Data Placement

2.8.6 Auto Query Time Estimation

2.8.7 Unload Advisor

2.8.8 Advisor Command-line Help

2.8.9 Autopilot Report Table

2.8.10 Advisor Report Table

2.9 Workload Optimization for OLTP

2.9.1 Autopilot Indexing

2.10 Best Practices

2.10.1 Preparing Data

2.10.2 Provisioning

2.10.3 Importing Data into the MySQL DB System

2.10.4 Inbound Replication

2.10.5 Loading Data

2.10.6 Auto Encoding and Auto Data Placement

2.10.7 Running Queries

2.10.8 Monitoring

2.10.9 Reloading Data

2.11 Supported Data Types

2.12 Supported SQL Modes

2.13 Supported SELECT Statement Clauses

2.14 Supported Functions and Operators

2.14.1 Aggregate Functions

2.14.2 Arithmetic Operators

2.14.3 Cast Functions and Operators

2.14.4 Comparison Functions and Operators

2.14.5 Control Flow Functions and Operators

2.14.6 Data Masking and De-Identification Functions

2.14.7 Encryption and Compression Functions

2.14.8 JSON Functions

2.14.9 Logical Operators

2.14.10 Mathematical Functions

2.14.11 String Functions and Operators

2.14.12 Temporal Functions

2.14.13 Vector Functions

2.14.14 Window Functions

2.15 String Column Encoding Reference

2.15.1 Variable-length Encoding

2.15.2 Dictionary Encoding

2.15.3 Column Limits

2.16 Troubleshooting

2.17 Metadata Queries

2.17.1 Secondary Engine Definitions

2.17.2 Excluded Columns

2.17.3 String Column Encoding

2.17.4 Data Placement

2.18 Bulk Ingest Data to MySQL Server

2.19 HeatWave MySQL Limitations

2.19.1 Change Propagation Limitations

2.19.2 Data Type Limitations

2.19.3 Functions and Operator Limitations

2.19.4 Index Hint and Optimizer Hint Limitations

2.19.5 Join Limitations

2.19.6 Partition Selection Limitations

2.19.7 Variable Limitations

2.19.8 Bulk Ingest Data to MySQL Server Limitations

2.19.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.4, “Loading Data Using Auto Parallel Load”.

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

For HeatWave for Azure, see Importing Data to HeatWave in the HeatWave for Azure Service Guide.

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.8, “Change Propagation”.

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

As of MySQL 9.2.0, HeatWave Storage Layer persists additional metadata to allow fast recovery after a planned or unplanned restart of Standalone DB system (without High Availability). This enables HeatWave to reload data from the Storage Layer after the DB system restarts. In previous versions of MySQL, HeatWave reloads data from the original source, InnoDB or the Object Storage bucket in case of Lakehouse tables, which takes longer for data to reload.

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