Documentation Home
MySQL HeatWave User Guide
Related Documentation Download this Manual
PDF (US Ltr) - 1.7Mb
PDF (A4) - 1.7Mb


MySQL HeatWave User Guide  /  ...  /  Data Placement

2.16.4 Data Placement

  • To identify columns defined as data placement keys in tables on the MySQL DB System, query the COLUMN_COMMENT column of the INFORMATION_SCHEMA.COLUMNS table. For example:

    mysql> SELECT COLUMN_NAME, COLUMN_COMMENT 
              FROM INFORMATION_SCHEMA.COLUMNS
              WHERE TABLE_NAME LIKE 'orders' AND COLUMN_COMMENT LIKE '%DATA_PLACEMENT_KEY%';
    +-----------------+-----------------------------------+
    | COLUMN_NAME     | COLUMN_COMMENT                    |
    +-----------------+-----------------------------------+
    | O_CUSTKEY       | RAPID_COLUMN=DATA_PLACEMENT_KEY=1 |
    +-----------------+-----------------------------------+

    You can also view data placement keys for an individual table using SHOW CREATE TABLE.

  • To identify columns defined as data placement keys in tables that are loaded in HeatWave, query the DATA_PLACEMENT_INDEX column of the performance_schema.rpd_columns table for columns with a DATA_PLACEMENT_INDEX value greater than 0, which indicates that the column is defined as a data placement key. For example:

    mysql> SELECT TABLE_NAME, COLUMN_NAME, DATA_PLACEMENT_INDEX
              FROM performance_schema.rpd_columns r1
              JOIN performance_schema.rpd_column_id r2 ON r1.COLUMN_ID = r2.ID
              WHERE r1.TABLE_ID = (SELECT ID FROM performance_schema.rpd_table_id
              WHERE TABLE_NAME = 'orders') AND r2.TABLE_NAME = 'orders'
              AND r1.DATA_PLACEMENT_INDEX > 0 ORDER BY r1.DATA_PLACEMENT_INDEX;
    +------------+--------------+----------------------+
    | TABLE_NAME | COLUMN_NAME  | DATA_PLACEMENT_INDEX |
    +------------+--------------+----------------------+
    | orders     | O_TOTALPRICE |                    1 |
    | orders     | O_ORDERDATE  |                    2 |
    | orders     | O_COMMENT    |                    3 |
    +------------+--------------+----------------------+

    For information about data placement key index values, see Section 2.7.2, “Defining Data Placement Keys”.

  • To determine if data placement partitions were used by a JOIN or GROUP BY query, you can query the QEP_TEXT column of the performance_schema.rpd_query_stats table to view prepart data. (prepart is short for pre-partitioning.) The prepart data for a GROUP BY operation contains a single value; for example: "prepart":#, where # represents the number of HeatWave nodes. A value greater than 1 indicates that data placement partitions were used. The prepart data for a JOIN operation has two values that indicate the number of HeatWave nodes; one for each JOIN branch; for example: "prepart":[#,#]. A value greater than 1 for a JOIN branch indicates that the JOIN branch used data placement partitions. (A value of "prepart":[1,1] indicates that data placement partitions were not used by either JOIN branch.) prepart data is only generated if a GROUP BY or JOIN operation is executed. To query QEP_TEXT prepart data for the last executed query:

    mysql> SELECT CONCAT( '"prepart":[', (JSON_EXTRACT(QEP_TEXT->>"$**.prepart", '$[0][0]')), 
              "," ,(JSON_EXTRACT(QEP_TEXT->>"$**.prepart", '$[0][1]')) , ']' )
              FROM performance_schema.rpd_query_stats 
              WHERE query_id = (select max(query_id)
              FROM performance_schema.rpd_query_stats);
    +-----------------------------------------------------------------------------+
    | concat( '"prepart":[', (JSON_EXTRACT(QEP_TEXT->>"$**.prepart", '$[0][0]')), |
    |"," ,(JSON_EXTRACT(QEP_TEXT->>"$**.prepart", '$[0][1]')) , ']' )             |
    +-----------------------------------------------------------------------------+
    | "prepart":[2,2]                                                             |
    +-----------------------------------------------------------------------------+