EXPLAIN REFERENCED for MariaDB Xpand

Overview

EXPLAIN REFERENCED was created as a convenience to gather information about a SQL query.

EXPLAIN REFERENCED option sql_statement

Where sql_statement must be explainable DML and the option provided is one of the following:

DATABASES

List of databases referenced by objects in the query and their oids

SCHEMAS

same as DATABASES

TABLES

List of tables referenced by the query, the table oid, and database name

CREATE SQL

DDL statements needed to create objects (tables and views) referenced by the query.

VIEWS

List of views referenced by objects in the query and their oids

Example

The following query queries tables and views. EXPLAIN REFERENCED provides the complete list of DDL needed by the query.

SQL> explain referenced create sql select firstname, lastname, x from vfoo, bar where vfoo.id = bar.id;
+---------------------------------------------------------------------------------------------------------------------------------+
| Create SQL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
+---------------------------------------------------------------------------------------------------------------------------------+
| CREATE DATABASE `clieu` /*!40100 CHARACTER SET utf8 */                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
| CREATE TABLE `clieu`.`foo` (
  `x` int(11) not null,
  `y` int(11),
  `z` int(11),
  `id` int(11),
  PRIMARY KEY (`x`) /*$ DISTRIBUTE=1 */,
  KEY `z_idx` (`z`) /*$ DISTRIBUTE=1 */
) CHARACTER SET utf8 /*$ SLICES=3 */
                                                                                                                                                                                                   |
| CREATE TABLE `clieu`.`bar` (
  `id` int(11),
  `firstname` varchar(25) CHARACTER SET utf8 not null,
  `lastname` varchar(25) CHARACTER SET utf8 not null,
  `username` varchar(16) CHARACTER SET utf8 not null,
  `email` varchar(35) CHARACTER SET utf8,
  `joined` date not null,
  PRIMARY KEY (`joined`) /*$ DISTRIBUTE=1 */
) CHARACTER SET utf8 /*$ SLICES=4 */
 |
| CREATE VIEW `clieu`.`vfoo` (`x`, `y`, `z`, `id`) AS select * from foo                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |
+---------------------------------------------------------------------------------------------------------------------------------+